Recently i had faced “ORA-06553: PLS-306:” error while taking an export using expdp for table using “query” parameter.
$ expdp directory=TEST_DPUMP_DIR dumpfile=test_url.dmp logfile=test_url.log tables=anand.test_delete_objects query=\"WHERE processed_flag = 'Y'\" Export: Release 11.2.0.3.0 - Production on Tue May 29 00:24:25 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=TEST_DPUMP_DIR dumpfile=test_url.dmp logfile=test_url.log tables=anand.test_delete_objects query="WHERE processed_flag = Y" Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 112 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-31693: Table data object "ANAND"."TEST_DELETE_OBJECTS" failed to load/unload and is being skipped due to error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_Y' Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /exp/kmportal_dpump_dir/test_url.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 00:25:50
The export completes successfully if we use parfile
$ expdp parfile=test_url.lst Export: Release 11.2.0.3.0 - Production on Tue May 29 00:53:49 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=test_url.lst Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 112 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "ANAND"."TEST_DELETE_OBJECTS" 10.09 KB 38 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /exp/kmportal_dpump_dir/test_url.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 00:54:39 $ more test_url.lst directory=TEST_DPUMP_DIR dumpfile=test_url.dmp logfile=test_url.log tables=anand.test_delete_objects query="WHERE processed_flag = 'Y'"
So,i was little confused until i came across Jonathan Lewis’s comments on the below blog
http://jonathanlewis.wordpress.com/2010/01/26/aliases/
Unfortunately, if you’ve installed Spatial, there are two functions (called ogc_x and ogc_y) which has been given public synonyms X and Y respectively that move Oracle into the function-call validity checks – which is where the pls error comes from.
SYS > select owner,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where SYNONYM_NAME='Y'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME ---------- ------------------------------ ------------------------------ ------------------------- PUBLIC Y MDSYS OGC_Y 1 row selected. SYS > select owner,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where SYNONYM_NAME='X'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME ---------- ------------------------------ ------------------------------ ------------------------- PUBLIC X MDSYS OGC_X 1 row selected.
So to run it succsfully from the command line
$ expdp directory=TEST_DPUMP_DIR dumpfile=test_url_test.dmp logfile=test_url_test.log tables=anand.test_delete_objects query=\"WHERE processed_flag = \'Y\'\" Export: Release 11.2.0.3.0 - Production on Tue May 29 01:11:44 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=TEST_DPUMP_DIR dumpfile=test_url_test.dmp logfile=test_url_test.log tables=anand.test_delete_objects query="WHERE processed_flag = 'Y'" Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 112 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "ANAND"."TEST_DELETE_OBJECTS" 10.09 KB 38 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /exp/kmportal_dpump_dir/test_url_test.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 01:12:24
One thought on “ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_Y' while exporting table”
nice post 🙂