Oracle EXPDP using QUERY parameter with results from SELECT

Question

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"

• Gandolf989: I have had good luck exporting with data pump from a materialized view. Just refresh the MV before you start the data pump export. You can then use any logic that you want.

Answer 1

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.

• Hazard: I'm already generating the PARFILE on-demand with a procedure. I just want to learn about if there is a better way to accomplish that. Thank you for the answer.