[SOLVED] How to use query clause in EXPDP

How to use query clause in EXPDP

I am exporting data of a table through EXPDP by using the below command

nohup expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=EXP.dmp logfile=EXP.log version=11.2.0.4 TABLES=CRM.CATEGORIES QUERY='"where bank_id='43'"'

Getting the below error

ORA-31693: Table data object "CRM"."CATEGORIES" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-01722: invalid number

If I remove the query parameter from the export command, it works fine. I have tried changing the version parameter to different values but still getting the same error The BANK_ID column in the table is of NVARCHAR2 data type. I can't use EXP as it is blocked on the db. Oracle version is 12.1.0.2.0 for both source and destination db.

Solution

can you try to alter your query filter in this way:

QUERY=(CRM.CATEGORIES:"WHERE bank_id='43'")

please be aware of the :

Edit I Managed to make it work in this way:

expdp \"/ as sysdba\" dumpfile=DATA_PUMP_DIR:test.dmp tableS=CRM.CATEGORIES QUERY='(CRM.CATEGORIES:"WHERE bank_id=43")'   logfile=DATA_PUMP_DIR:test.log