DBMS_DATAPUMP IMPORT Error (4 Merged) [message #480561] |
Mon, 25 October 2010 13:50 |
pkumar_2008
Messages: 19 Registered: November 2008 Location: usa
|
Junior Member |
|
|
Hope all are doing well..
I have a requirement to load .dmp files into existing staging tables and there is package to load the ODS tables from staging.So,I thought of using DBMS_Datapump utility to import the data from .DMP files to the Tables and this need be automated.
--Create Directory
CREATE
OR REPLACE DIRECTORY test_dir AS 'C:\Test'
--grant Access to the User
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
--Script to import
DECLARE
l_dp_handle1 NUMBER;
BEGIN
l_dp_handle1 := dbms_datapump.OPEN(operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => NULL,
job_name => 'SAMSE17_IMPORT', version
=> 'COMPATIBLE');
dbms_output.Put_line('calling add_file');
dbms_datapump.Add_file(handle => l_dp_handle1,
filename => 'EXPDAT0122-10-2010011725.DMP', directory => 'DATA_PUMP_DIR');
dbms_output.Put_line('calling METADATA_FILTER');
dbms_datapump.Metadata_filter(handle => l_dp_handle1, NAME => 'NAME_LIST',
VALUE
=> '''EMP_D''');
dbms_output.Put_line('calling SET_PARAMETER - TABLE EXISTS action');
dbms_datapump.Set_parameter(handle => l_dp_handle1, NAME =>
'TABLE_EXISTS_ACTION', VALUE => 'APPEND');
dbms_output.Put_line('starting job');
dbms_datapump.Start_job(l_dp_handle1);
dbms_output.Put_line('started job');
dbms_datapump.Detach(l_dp_handle1);
dbms_output.Put_line('detached job ');
END;
Errors
ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 938
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4590
ORA-06512: at line 4
Please,help me to complete this task.
code formatted by BlackSwan; please do so yourself in the future
[Updated on: Mon, 25 October 2010 13:53] by Moderator Report message to a moderator
|
|
|
Re: DBMS_DATAPUMP IMPORT Error [message #480563 is a reply to message #480561] |
Mon, 25 October 2010 13:54 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
31634, 00000, "job already exists"
// *Cause: Job creation or restart failed because a job having the selected
// name is currently executing. This also generally indicates that
// a Master Table with that job name exists in the user schema. Refer
// to any following error messages for clarification.
// *Action: Select a different job name, or stop the currently executing job
// and re-try the operation (may require a DROP on the Master Table).
Is there a compelling reason to initiate this via PL/SQL;
as opposed to via command line?
[Updated on: Mon, 25 October 2010 14:27] Report message to a moderator
|
|
|
|
|
|
|
|