Data Pump API error help [message #333130] |
Thu, 10 July 2008 10:12 |
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 #409735 is a reply to message #333130] |
Tue, 23 June 2009 14:51 |
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 |
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 |
|
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 |
|
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
|
|
|