Error executing DBMS_DATAPUMP

From: sjoshi <sjoshi_at_ingr.com>
Date: 22 Jan 2007 09:09:26 -0800
Message-ID: <1169485766.181651.53320@11g2000cwr.googlegroups.com>

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 Received on Mon Jan 22 2007 - 11:09:26 CST