Question: I want to include only
certain tables on the job during the dbms_datapump
export process and I want to construct the table list
dynamically.
To do this, I made a table called
conditiontables that contains all table I want to
include in the export with
dbms_datapump. How
do I use name_list to export this table name list?
Answer: The following code will use
name_list in dbms_datapump to export the
required table names from a table called
conditiontables:
DECLARE
table_list varchar2(3000);
BEGIN
SELECT
listagg(''''||table_name||'''', ',')
WITHIN
GROUP
(ORDER BY table_name)
INTO
table_list
FROM
conditiontables;
DBMS_DATAPUMP.METADATA_FILTER (
handle => h1,
name =>
'NAME_LIST',
value => table_list,
object_path => 'TABLE');
Here is
another example using name_list where we specify
the names of the CUST and PRODUCT tables. Note the
multiple single-quotation marks around the table names:
dbms_datapump.metadata_filter(
handle => dp_handle,
name =>
'NAME_LIST',
value =>
'''CUST'',''PRODUCT'''
);
