Oracle dbms_workload_repository awr_global_report_text fails with ORA-06553 PLS-307

My last post was about generating AWR reports from SQL – not a very complicated case. “But” – little word, big meaning. And suddenly we get a strange error message.

In my example, I was especially interested in a Real Application Cluster’s “global” report. The subprogram call for that purpose is dbms_workload_repository.awr_global_report_text(), accepting the DBID, begin snapshot, end snapshot, an option field, and, l_inst_num for the instance number. Regarding the latter, Oracle documentation says: “List of instance numbers to be included in report. If set to NULL, all instances for which begin and end snapshots are available …”

But when I did that, I got an error that felt strange to me:

ORA-06553: PLS-307: too many declarations of ‘AWR_GLOBAL_REPORT_TEXT’ match this call

Bad

What I tried:

SELECT * 
FROM TABLE (dbms_workload_repository.awr_global_report_text(
 l_dbid=>123456789,
 l_inst_num=>null,
 l_bid=>24142,
 l_eid=>24143,
 l_options=>0
 )
);

Good

But l_inst_num is a string, so the correct way to call the procedure would be:

SELECT * 
FROM TABLE (dbms_workload_repository.awr_global_report_text(
 l_dbid=>123456789,
 l_inst_num=>'',
 l_bid=>24142,
 l_eid=>24143,
 l_options=>0
 )
);

Of course my bad, but the error message is not very helpful, either…

Do it better when it’s your turn…
Martin Klier