Hi team askTom:

This is not an export/import question, more of a pl/sql and 'quoting' question, and I always have trouble quoting my variables, especially those that get evaluated at run time.

Here I am trying to export 2 schemas, so I wrote a small pl/sql proc for it (I prefer pl/sql because easier for me to use dbms_scheduler). If I use LITERALS the code works correctly, however, I wish to use variables where possible.

Here I declare 2 variables which are schema names which I want to export
DECLARE
l_schema1 VARCHAR2(30) := 'SCHEMA_A';
l_schema2 VARCHAR2(30) := 'SCHEMA_B';

....... some other pl/sql code ...
-- The code snippet shown below works because it is using the literals
-- but I would like to use the vars: l_schema1 and l_schema2

-- works
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_LIST',
value => ' ''SCHEMA_A'', ''SCHEMA_2'' '
);

But if I use variables, instead of the literals shown, I got this error:
ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4825
ORA-06512: at "ORACLE.EXP_JIRA_CONF", line 68
ORA-06512: at line 1

Like I said, I have tried all kinds of stuff, and one of the number of things I tried was this:
value => ''' || l_schema1 || ''' || ',' || ''' || l_schema2 || '''


Is there a way to correctly quote this, such that at run-time it all gets evaluated correctly?

Thanks much,
Khalid

SQL> select dbms_assert.enquote_name('CONNOR') from dual;

DBMS_ASSERT.ENQUOTE_NAME('CONNOR')
-----------------------------------------------------------
"CONNOR"

dbms_datapump.metadata_filter(my_handle, 'SCHEMA_LIST',quote('SCOTT')||','||quote('HR'));