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




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Oracle dbms_workload_repository awr_global_report_text fails with ORA-06553 PLS-307”

  1. Usn's IT Blog » How to create an Oracle AWR report with SQL and PL/SQL Says:

    […] PS: My lesson learned to type carefully, here. […]

  2. Niall Litchfield Says:

    hah. made the same mistake.

Leave a Reply