About

Martin Klier

usn-it.de

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

How to create an Oracle AWR report with SQL and PL/SQL
Oracle 12c in practice – customer report in Cologne #12cjetzt

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.