Data Pump Export Hits SQL Syntax Errors When QUERY Clause Is Greater Than 2000 Bytes (Doc ID 977713.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.

Symptoms

You are trying to perform a Data Pump export (expdp) and you encounter SQL syntax errors such as ORA-00904 or ORA-907 or ORA-920 when using the QUERY parameter where the text is greater than 2000 bytes.

Test Example

1. expdp parameters
 
$ more expdp.par
....
QUERY="WHERE deptno IN (5,7,9,10,11,12,13,14,15,16,,....,600) <--- Over 2000 bytes



2. Error during expdp

$ expdp scott/tiger parfile=expdp.par

Export: Release 10.2.0.4.0 - Production on Sunday, 13 December, 2009 0:26:10
...
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "SCOTT"."DEPT" failed to load/unload and is being skipped due to error:
ORA-00907: missing right parenthesis
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
....



3. DW tracefile has trimmed SQL.

*** 2009-12-13 00:26:29.978
ksedmp: internal or fatal error
ORA-00907: missing right parenthesis
No current SQL statement being executed.
....


Cursor#41(0xb7121754) state=NULL curiob=0xb6f6bb84
curflg=c5 fl2=0 par=(nil) ses=0x56f06fb8
sqltxt(0x5091e7e8)=
CREATE TABLE "ET$008C03EA0001"
( "DEPTNO",
"DNAME",
"LOC"
) ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY DATA_PUMP_DIR ACCESS PARAMETERS (DEBUG =0 DATAPUMP INTERNAL TABLE "SCOTT"."DEPT" JOB ( "SCOTT","SYS_EXPORT_TABLE_01",1) WORKERID 1 PARALLEL 1 VERSION COMPATIBLE ENCRYPTPASSWORDISNULL ) LOCATION ('bogus.dat') ) PARALLEL 1 REJECT LIMIT UNLIMITED
AS SELECT "DEPTNO", "DNAME", "LOC"
FROM RELATIONAL("SCOTT"."DEPT" ) KU$ WHERE deptno IN (5,7,9,10,11,12,13,14,15,16,..,522,523,524,52 
_$#$_ <--- Trimmed here


4. Save WHERE clause from DW tracefile as trimmed.sql  and calculate bytes.
$ wc trimmed.sql
1 7 1999 trimmed.sql <--- 1999 bytes




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms