When Running SQL Plan Analyzer (SPA) to Compare the Execution Plans, ORA-06553 and PLS-306 Are Displayed
(Doc ID 2364716.1)
Last updated on NOVEMBER 10, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- When running SPA to compare the execution plan between two databases, ORA-06553 and PLS-306 are displayed for a SQL in the SPA report:
...
SQL> BEGIN
2 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
3 task_name => 'spa_task_test_dblink',
4 execution_type => 'COMPARE',
5 execution_params => dbms_advisor.arglist(
6 'execution_name1',
7 'exec_11g_plan_dblink',
8 'execution_name2',
9 'exec_12c_plan_dblink')
10 );
11 END;
12 /PL/SQL procedure successfully completed.
SQL> VAR rep CLOB;
SQL> BEGIN
2 :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
3 task_name => 'spa_task_test_dblink',
4 type => 'HTML',
5 level => 'ERRORS',
6 section => 'ALL',
7 top_sql => 200
8 );
9 END;
10 /PL/SQL procedure successfully completed.
SQL> PRINT :rep
...
SQL Details:
Object ID: 216
Schema Name: TEST
Container Name: Unknown (con_dbid: 3496897090)
SQL ID: a45vgdjyc3u0w
Execution Frequency: 1
SQL Text: SELECT MYPKG.MYFUNC((TO_DATE(:B3 ,:B1 ) + :B2 - 30), :B1 ) FROM DUAL
Errors: 1. Error in execution 'exec_12c_plan_dblink': ORA-06553: PLS-306: wrong number or types of arguments in call to 'MYFUNC'。 - But when running the SQL from SQL*Plus, it can be executed normally without errors:
SQL> var B1 varchar2(10);
SQL> var B2 number;
SQL> var B3 varchar2(10);
SQL>
SQL> exec :B1 := 'YYYYMMDD';
PL/SQL procedure successfully completed.
SQL> exec :B2 := -7;
PL/SQL procedure successfully completed.
SQL> exec :B3 := '20171119';
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT MYPKG.MYFUNC((TO_DATE(:B3 ,:B1 ) + :B2 - 30), :B1 ) FROM DUAL;
MYPKG.MYFUNC((TO_DATE(:B3,:B1)+:B2-30),:B1)
--------------------------------------------------------------------------------
20171013
Changes
None
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |