DataPump Export (EXPDP) Fails With ORA-6502 For Selective Data From Table

DataPump Export (EXPDP) Fails With ORA-6502 For Selective Data From Table

Running a datapump export to export selective data from a table and receiving an ORA-6502 like below:

$ expdp dfn_history/test tables=dfn_history.history:\"where ticker_serial in\(select ticker_serial from tickers where source_id in\(\'TDWL\'\)\)\" directory=DTPUMP dumpfile=TDWL_History_data_.dmp logfile=TDWL_History_data_.log

Export: Release 11.2.0.3.0 -

ORA-06502: PL/SQL: numeric or value error

Wrong syntax is being used. A subquery cannot be specified under the Tables clause.

Tables clause has following syntax:

TABLES=[schema_name.]table_name[:partition_name] [, ...]

A subquery should be specified under Query clause:

QUERY = [schema.][table_name:] query_clause

To avoid the error, specify the subquery under the Query clause.

Following presents a test case to perform export of selective data from a table:

-- create test user

grant resource, dba to dfn_history identified by test;

-- connect to test user

connect dfn_history/test

-- create dummy table and load data

create table history (

id number,

ticker_serial number

);

create table tickers (

ticker_serial number,

source_id varchar2(20)

);

insert into history values (1,1);

insert into history values (1,1);

insert into history values (1,1);

insert into history values (1,1);

insert into history values (1,1);

insert into tickers values (1,'TDWL');

insert into tickers values (2,'TDWL');

insert into tickers values (3,'TDWL');

insert into tickers values (4,'TDWL');

insert into tickers values (5,'TDWL');

commit;

-- create directory

create or replace directory DTPUMP as '/u01/testcases/t1';

grant read, write on directory DTPUMP to PUBLIC;

After the above, perform the datapump export using the Query clause, e.g.

$ expdp dfn_history/test schemas=dfn_history INCLUDE=TABLE:"in\('HISTORY'\)" query=history:\"where ticker_serial in\(select ticker_serial from tickers where source_id in\(\'TDWL\'\)\)\" directory=DTPUMP dumpfile=TDWL_History_data_.dmp logfile=TDWL_History_data_.log

OR

$ expdp dfn_history/test INCLUDE=TABLE:"in\('HISTORY'\)" query=history:\"where ticker_serial in\(select ticker_serial from tickers where source_id in\(\'TDWL\'\)\)\" directory=DTPUMP dumpfile=TDWL_History_data_.dmp logfile=TDWL_History_data_.log