I'm trying to execute a simple script and it keeps telling me this:

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 2926
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3958
ORA-06512: at line 20

The script is

DECLARE
jobNo NUMBER;
ind NUMBER;
schemas VARCHAR2(30);
percent_done NUMBER;    	-- Percentage of job complete
job_state VARCHAR2(30); 	-- Keeps track of job state
le ku$_LogEntry;        	-- work-in-progress and error messages
js ku$_JobStatus;       	-- Job status from get_status
jd ku$_JobDesc;         	-- Job description from get_status
sts ku$_Status;         	-- Status object returned by get_status

BEGIN

jobNo := DBMS_DATAPUMP.OPEN('EXPORT', 'SCHEMA', NULL,
'DAO2112007-114594','LATEST');
DBMS_OUTPUT.PUT_LINE('Handle: ' || jobNo);

DBMS_DATAPUMP.ADD_FILE(jobNo,'SIEMENSSITE.dmp','EXPSUNIT');
DBMS_OUTPUT.PUT_LINE('Added file');

DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','SIEMENSSITE,
SIEMENSSITE_SCHEMA');
DBMS_OUTPUT.PUT_LINE('Added filter for schema list');

DBMS_DATAPUMP.SET_PARAMETER(jobNo, 'FLASHBACK_SCN', 15383693);
DBMS_OUTPUT.PUT_LINE('Set parameter for SCN');

DBMS_OUTPUT.PUT_LINE('Starting job...');
DBMS_DATAPUMP.START_JOB(jobNo);

percent_done := 0;
job_state := 'UNDEFINED';

while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
DBMS_DATAPUMP.get_status(jobNo,
DBMS_DATAPUMP.ku$_status_job_error +
DBMS_DATAPUMP.ku$_status_job_status +
DBMS_DATAPUMP.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;

-- As the percentage-complete changes in this loop, the new value
displays.
if js.percent_done != percent_done
then
DBMS_OUTPUT.PUT_LINE('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;

-- Displays any work-in-progress or error messages received for the
job.

if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;

if le is not null
then
ind := le.FIRST;
while ind is not null loop
DBMS_OUTPUT.PUT_LINE(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;

-- When the job finishes, display status before detaching from job.
DBMS_OUTPUT.PUT_LINE('Job has completed');
DBMS_OUTPUT.PUT_LINE('Final job state = ' || job_state);
DBMS_DATAPUMP.DETACH(jobNo);
END;
/

Any hints are appreciated. Also how do I find out the job number of an
already submitted job since if I execute the script again it tells that
job already exists. I would like to stop that job using
DBMS_DATAPUMP.Stop

thanks
Sunit

[ Post a follow-up to this message ]

sjoshi wrote:
> Any hints are appreciated. Also how do I find out the job number of an
> already submitted job since if I execute the script again it tells that
> job already exists. I would like to stop that job using
> DBMS_DATAPUMP.Stop

DBA_DATAPUMP_JOBS lists all active jobs.  You can attach to the job and
stop it using dbms_datapump package.

[ Post a follow-up to this message ]

Got the ADD_FILE to work. It seems I need to specify all args as in:

DBMS_DATAPUMP.ADD_FILE(jobNo,'SIEMENSSITE.dmp','EXPSUNIT', NULL,
DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

Now this part fails. I'm trying to use SCHEMA_LIST

DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','SJSITE,
SJSITE_SCHEMA');

thanks
Sunit

MTNorman wrote:
> sjoshi wrote: 
>
> DBA_DATAPUMP_JOBS lists all active jobs.  You can attach to the job and
> stop it using dbms_datapump package.

[ Post a follow-up to this message ]

On 22 Jan 2007 11:13:53 -0800, "sjoshi" <sjoshi@ingr.com> wrote:

>Got the ADD_FILE to work. It seems I need to specify all args as in:
>
>DBMS_DATAPUMP.ADD_FILE(jobNo,'SIEMENSSITE.dmp','EXPSUNIT', NULL,
>DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
>
>Now this part fails. I'm trying to use SCHEMA_LIST
>
>DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','SJSITE,
>SJSITE_SCHEMA');

The filter parameters need to be valid SQL expressions, so something like:

DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST',
'IN (''SJSITE'', ''SJSITE_SCHEMA'')');

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[ Post a follow-up to this message ]

I tried this and it worked. Is this fine ?

DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','''ERLS'',
''ERLS_SCHEMA''', NULL, NULL);

thanks
Sunit

Andy Hassall wrote:
> On 22 Jan 2007 11:13:53 -0800, "sjoshi" <sjoshi@ingr.com> wrote:
> 
>
>  The filter parameters need to be valid SQL expressions, so something like
:
>
> DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST',
>    'IN (''SJSITE'', ''SJSITE_SCHEMA'')');
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[ Post a follow-up to this message ]

On 22 Jan 2007 12:01:15 -0800, "sjoshi" <sjoshi@ingr.com> wrote:

>I tried this and it worked. Is this fine ?
>
>DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','''ERLS'',
>''ERLS_SCHEMA''', NULL, NULL);

If it works, it must be fine ;-) The manual is not particularly clear on wha
t
the _LIST forms accept, so quite possibly the "IN" can be skipped as it's
implicit in the _LIST form.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[ Post a follow-up to this message ]