Oracle EXPDP using QUERY parameter with results from SELECT

datapumporacleoracle-11g

Oracle 11.2g XE

I'm trying to write a initial deployment script for my application. There are several tables with data that I want to include in datapump export with a QUERY.

I have a table which includes these queries and table names to be exported.

Table: T_DEFINITION
============================

TABLE_NAME | WHERE_CONDITION
-----------+----------------
TBL1       | WHERE CMP_ID=0
TBL2       | WHERE 1=0
TBL3       | WHERE SEQ_ID=10 OR CMP_ID=0
...

Relevant portions of my expdp PARFILE as follows:

INCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM T_DEFINITION WHERE (WHERE_CONDITION!= '1=0' OR WHERE_CONDITION IS NULL))"
QUERY=DEV.TBL1:"WHERE CMP_ID=0"
QUERY=DEV.TBL2:"WHERE 1=0"
...

As you can see, I must have manually define QUERY parameter for each table that I need. Is there a way to define QUERY parameters that SELECT where conditions from my T_DEFINITION table?

Something like this:

QUERY="SELECT TABLE_NAME||':'||WHERE_CONDITION FROM T_DEFINITION"

Best Answer

No, there is not.

You have already defined manually the filters for each table, why not use that? Just dynamically generate the parameterfile before running expdp:

select 'QUERY=' || table_owner || '.' || table_name || ':"' || where_condition || '"'
from t_definition;

QUERY=DEV.TBL1:"WHERE CMP_ID=0"
QUERY=DEV.TBL2:"WHERE 1=0"
QUERY=DEV.TBL3:"WHERE SEQ_ID=10 OR CMP_ID=0"
...

Or use DBMS_DATAPUMP.

Related Question