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