Home » RDBMS Server » Server Utilities » Data Pump API error help (10g r2)
Data Pump API error help [message #333130] Thu, 10 July 2008 10:12 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
Hey guys, I'm stuck and could really use some help trying to get the data pump api to work. I've copied the code from the example document:
    DECLARE
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
BEGIN

-- Create a (user-named) Data Pump job to do a schema export.

  h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'EXAMPLE1','LATEST');

-- Specify a single dump file for the job (using the handle just returned)
-- and a directory object, which must already be defined and accessible
-- to the user running this procedure.

  DBMS_DATAPUMP.ADD_FILE(h1,'example1.dmp','DMPDIR');

-- A metadata filter is used to specify the schema that will be exported.

  DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''HR'')');

-- Start the job. An exception will be generated if something is not set up
-- properly. 

  DBMS_DATAPUMP.START_JOB(h1);

....
  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END;
  


I can't seem to get the dbms_datapump.addfile to work properly, I keep getting ORA-39001: invalid argument value.

I already have read/write to the datapump directory assigned to the user. The funny thing is that I can run the datapump functions from the command line in the unix server (where the database resides) no problem.

Can somebody please suggest anything? I followed all the instructions for creating and granting permissions to the user but still no luck.

Thanks in advance,
John.
Re: Data Pump API error help [message #333180 is a reply to message #333130] Thu, 10 July 2008 13:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
I get that error when the file already exists. If there is already an exmaple1.dmp delete it. Then exit your current session, start a new session, and try again.
Re: Data Pump API error help [message #409735 is a reply to message #333130] Tue, 23 June 2009 14:51 Go to previous messageGo to next message
MegZadeh
Messages: 1
Registered: June 2009
Location: So Cal
Junior Member
The cause and the action to correct this Error Message are:

Cause
The user specified API parameters were of the wrong type or value range. Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS will further describe the error.

Action
Correct the bad argument and retry the API.

You wrote:
I can't seem to get the dbms_datapump.addfile to work properly


If you are using exactly this, I noticed that you have forgotten the hyphen in add_file the syntax is:
DBMS_DATAPUMP.ADD_FILE(h1,'example1.dmp','DMPDIR');

Correct this and try again.

Good Luck,

Megan
Re: Data Pump API error help [message #429355 is a reply to message #409735] Tue, 03 November 2009 06:20 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Even I am getting the same error .I am not able to rectify that .


Below given is the pl/sql used for this

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'EMP_EXPORT1',
version => 'LATEST');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SCOTT.dmp',
directory => 'TEST_DIR');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SCOTT.log',
directory => 'TEST_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => '= ''CATALOG''');

DBMS_DATAPUMP.start_job(l_dp_handle);

DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
DECLARE
*
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 3162
ORA-06512: at line 14

Please help to solve this
Re: Data Pump API error help [message #429365 is a reply to message #429355] Tue, 03 November 2009 07:05 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Read orafaq forum guide before posting.
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
  2    l_dp_handle       NUMBER;
  3    l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
  4    l_job_state       VARCHAR2(30) := 'UNDEFINED';
  5    l_sts             KU$_STATUS;
  6  BEGIN
  7    l_dp_handle := DBMS_DATAPUMP.open(
  8      operation   => 'EXPORT',
  9      job_mode    => 'SCHEMA',
 10      remote_link => NULL,
 11      job_name    => 'EMP_EXPORT',
 12      version     => 'LATEST');
 13
 14    DBMS_DATAPUMP.add_file(
 15      handle    => l_dp_handle,
 16      filename  => 'SCOTT.dmp',
 17      directory => 'TEST_DIR');
 18
 19    DBMS_DATAPUMP.add_file(
 20      handle    => l_dp_handle,
 21      filename  => 'SCOTT.log',
 22      directory => 'TEST_DIR',
 23      filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 24
 25    DBMS_DATAPUMP.metadata_filter(
 26      handle => l_dp_handle,
 27      name   => 'SCHEMA_EXPR',
 28      value  => '= ''SCOTT''');
 29
 30    DBMS_DATAPUMP.start_job(l_dp_handle);
 31
 32    DBMS_DATAPUMP.detach(l_dp_handle);
 33  END;
 34  /

PL/SQL procedure successfully completed.

[Updated on: Tue, 03 November 2009 07:09]

Report message to a moderator

Re: Data Pump API error help [message #429372 is a reply to message #429355] Tue, 03 November 2009 07:34 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-39001: invalid argument value
 *Cause:  The user specified API parameters were of the wrong type or
          value range.  Subsequent messages supplied by
          DBMS_DATAPUMP.GET_STATUS will further describe the error.
 *Action: Correct the bad argument and retry the API.

Regards
Michel
Previous Topic: Export of indexes fails
Next Topic: Troubleshoot for import data
Goto Forum:
  


Current Time: Mon Dec 23 13:18:33 CST 2024