Home » RDBMS Server » Server Utilities » DBMS_DATAPUMP IMPORT Error (4 Merged) (Oralce 10)
DBMS_DATAPUMP IMPORT Error (4 Merged) [message #480561] Mon, 25 October 2010 13:50 Go to next message
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 Go to previous messageGo to next message
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

Re: DataPump Import [message #480573 is a reply to message #480561] Mon, 25 October 2010 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT multipost your question, it is useless duplicates are merged and deleted.

Regards
Michel
Re: DataPump Import [message #480574 is a reply to message #480573] Mon, 25 October 2010 14:49 Go to previous messageGo to next message
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

Sorry Mike.
Re: DBMS_DATAPUMP IMPORT Error [message #480576 is a reply to message #480563] Mon, 25 October 2010 14:58 Go to previous messageGo to next message
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

In order to wrap this import script with the existing pl/sql package.i thought it is better to do in pl/sql compare to command line/Shell script.
Re: DBMS_DATAPUMP IMPORT Error [message #480577 is a reply to message #480576] Mon, 25 October 2010 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
With the procedure as written, it will fail on the second & all subsequent attempts to run it due to name already existing.
change the code to eliminate this error.
Re: DBMS_DATAPUMP IMPORT Error [message #480580 is a reply to message #480577] Mon, 25 October 2010 15:27 Go to previous message
pkumar_2008
Messages: 19
Registered: November 2008
Location: usa
Junior Member

So, what is the best way to automate this process.Please,guide me
Previous Topic: Need way to Wrap my PL/SQL code
Next Topic: Upgrade 8i to 10g
Goto Forum:
  


Current Time: Sun Sep 19 21:02:00 CDT 2021