CODEWITHSUNDEEP
oracle-databasedatapump
Saroj
Saroj

Reputation: 71

Selecting Tables in oracle metadata filter

I am using the oracle DATA PUMP API to export some database tables using the command

DBMS_DATAPUMP.METADATA_FILTER (handle,'NAME_EXPR','IN ('''||Table_name||''')','TABLE')

As the above code will export the table name provided in table_name. But i will have the table names listed in another table B. Is there a way that i can provide a select query in the Metadata Filter so that all the table name present in Table B will be exported?

Upvotes: 1

Views: 6631

Answers (2)

Robert Norden
Robert Norden

Reputation: 100

As Saroj stated in his answer, you can use LISTAGG to combine the table names.

However you can just use the select statement without LISTAGG as METADATA_FILTER can take a NAME_EXPR in SQL which will be evaluated.

Most tutorials simply use a static IN clause for this as in:

DBMS_DATAPUMP.METADATA_FILTER (handle,'NAME_EXPR','IN (''DEPARTMENTS'')','TABLE')

Usage of double quotes will result in regular quotes and valid sql.

You can use evaluation at run-time with plain sql:

DBMS_DATAPUMP.METADATA_FILTER (handle,'NAME_EXPR','IN (SELECT NAME FROM DEPARTMENTS WHERE DEPARTMENT_ID > 2)','TABLE')

The Oracle DBMS_DATAPUMP documentation states the following to explain the usage of sql expression:

Two versions of each filter are supported: SQL expression and List. The SQL expression version of the filters offer maximum flexibility for identifying objects (for example the use of LIKE to support use of wild cards).

Upvotes: 0

Saroj
Saroj

Reputation: 71

I figured out myself. Need to use the listagg function. The function simply organizes the query result as a list.

Table_List VARCHAR2(2000);

SELECT  listagg (''''||table_column||'''',',') WITHIN GROUP (ORDER BY table_column) INTO Table_List FROM 
(SELECT * FROM Table_B) 

 DBMS_DATAPUMP.METADATA_FILTER (handle,'NAME_EXPR','IN ('||Table_List||')','TABLE')

Table_list will have the data from the table as a list.

Upvotes: 2

Related Questions