This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Nov 9, 2011 8:07 AM by Nicolas.Gasparotto RSS

Data pump: by using dbms_datapump

558022 Newbie
Currently Being Moderated
I wrote a pl/sql procedure that compiles and there is no error. But when I execute the procedure, I got follwing error
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4710
ORA-06512: at "DO_EXPORT.RCAT_EXPDP", line 59
ORA-06512: at line 1

I went to metalink and there I found that if there is any compnent invalid in dba_registry then this error will occur. I found that spatial is invalid in dba_registry. I run utlrp several times, but still spatial is in-valid. Does any one one work around this so that I can use pl/sql code which i wrote.

thanks
  • 1. Re: Data pump: by using dbms_datapump
    orafad Oracle ACE
    Currently Being Moderated
    Have you gone through The Data Pump API chapter with examples?
  • 2. Re: Data pump: by using dbms_datapump
    558022 Newbie
    Currently Being Moderated
    Yes, I go through that, my procedure compiles without any problem, but I get error when I try to execute that.
  • 3. Re: Data pump: by using dbms_datapump
    orafad Oracle ACE
    Currently Being Moderated
    Do you get any errors if you try a similar export via the command line tool? (expdp)
  • 4. Re: Data pump: by using dbms_datapump
    558022 Newbie
    Currently Being Moderated
    No, with command prompt I did not get the errors.
  • 5. Re: Data pump: by using dbms_datapump
    558022 Newbie
    Currently Being Moderated
    I also got this error when I run this job in a database where all components are valid.

    Source code which I am using is this.
    CREATE OR REPLACE procedure DO_EXPORT.rcat_expdp (
    strJobName in varchar2 , strDumpFileName in varchar2,
    strLogFileName in varchar2, strDirectory in varchar2,
    strSchemaName in varchar2)
    is
    d1 number;
    v_ind number; v_sts ku$_Status; v_le ku$_LogEntry;begin
    begin
    dbms_output.put_line ('in code');
    d1 := dbms_datapump.open (
    operation => 'EXPORT',
    job_mode => 'SCHEMA',
    job_name => strJobName,
    version => 'COMPATIBLE');
    end;
    dbms_output.put_line ('handle created');
    begin
    dbms_datapump.add_file(handle => d1,
    filename => strDumpFileName,
    directory => strDirectory,
    filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
    end;
    dbms_output.put_line ('dmup file');
    begin
    dbms_datapump.add_file(handle => d1,
    filename => strLogFileName,
    directory => strDirectory,
    filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
    end;
    dbms_output.put_line ('log file');
    begin
    dbms_datapump.metadata_filter(handle => d1,
    name => 'SCHEMA_EXPR',
    value => 'IN ( '|| '''strSchemaName''' || ')');
    end;
    dbms_output.put_line ('schema added');
    begin
    dbms_datapump.start_job(handle => d1, skip_current => 0);
    end;
    dbms_output.put_line ('job started');
    begin
    dbms_datapump.detach(handle => d1);
    end;

    dbms_output.put_line('Export job submitted successfully.');

    exception
    when others then
    v_sts:=dbms_datapump.get_status(d1,dbms_datapump.ku$_status_job_error,0);
    v_le := v_sts.error;
    if v_le is not null then
    v_ind := v_le.FIRST;
    while v_ind is not null loop
    dbms_output.put_line(v_le(v_ind).LogText);
    v_ind := v_le.NEXT(v_ind);
    end loop;
    end if;
    begin dbms_datapump.stop_job(handle => d1); end;
    end;
    /
  • 6. Re: Data pump: by using dbms_datapump
    orafad Oracle ACE
    Currently Being Moderated
    Then try the example 5-1 (simple schema export) given in the DP API chapter and work from there.
  • 7. Re: Data pump: by using dbms_datapump
    558022 Newbie
    Currently Being Moderated
    Sure, I will try that one.

    can you help me in finding error in this


    1 declare
    2 h1 number;
    3 BEGIN
    4 h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'EXAMPLE134','LATEST');
    5 dbms_output.put_line('one');
    6 DBMS_DATAPUMP.ADD_FILE(h1,'example.dmp','NIGHTLY_DB_EXPORT');
    7 dbms_output.put_line('two');
    8 DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''SCOTT'')');
    9 dbms_output.put_line('three');
    10 DBMS_DATAPUMP.START_JOB(h1);
    11 dbms_output.put_line('four');
    12 DBMS_DATAPUMP.detach(h1);
    13* END;
    SQL> /
    one
    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 6
  • 8. Re: Data pump: by using dbms_datapump
    orafad Oracle ACE
    Currently Being Moderated
    6
    DBMS_DATAPUMP.ADD_FILE(h1,'example.dmp','NIGHTLY_DB_E
    PORT');
    Is the directory object given in the 3rd position valid? Is it defined, and is it accessible to the user?
  • 9. Re: Data pump: by using dbms_datapump
    558022 Newbie
    Currently Being Moderated
    This code
    declare
    d1 number;
    BEGIN
    d1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'EXAMPLEa9','LATEST');
    DBMS_DATAPUMP.ADD_FILE(d1,'examplea9.dmp','NIGHTLY_DB_EXPORT');
    DBMS_DATAPUMP.METADATA_FILTER(d1,'SCHEMA_EXPR','IN (''SCOTT'')');
    DBMS_DATAPUMP.START_JOB(d1);
    DBMS_DATAPUMP.detach(d1);
    END;

    Worked. Bur when I tired to re-run then got error
    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 911
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 4354
    ORA-06512: at line 4

    Is there any way I can re-use the job name?
    I also want to create log file. for this export? Thanks
  • 10. Re: Data pump: by using dbms_datapump
    558022 Newbie
    Currently Being Moderated
    example 5-1 (simple schema export) given in the DP API works but when put same code in procedure then and after complitaion execute the procedure I got the error

    ERROR at line 1:
    ORA-31626: job does not exist
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 4356
    ORA-06512: at "DO_EXPORT.RCAT_EXPDP", line 12
    ORA-06512: at line 1

    Does it means I cannot call DBMS_DATAPUMP jobs from procedure?
  • 11. Re: Data pump: by using dbms_datapump
    583370 Newbie
    Currently Being Moderated
    example 5-1 (simple schema export) given in the DP
    API works but when put same code in procedure then
    and after complitaion execute the procedure I got the
    error

    ERROR at line 1:
    ORA-31626: job does not exist
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 4356
    ORA-06512: at "DO_EXPORT.RCAT_EXPDP", line 12
    ORA-06512: at line 1

    Does it means I cannot call DBMS_DATAPUMP jobs from
    procedure?
    I have the same error in the same situation (store proc)... any news on that?
  • 12. Re: Data pump: by using dbms_datapump
    591233 Newbie
    Currently Being Moderated
    I got the same error... some one find a solution. I can`t find solution on metalink...
  • 13. Re: Data pump: by using dbms_datapump
    573094 Newbie
    Currently Being Moderated
    Did you see the thread on http://www.orafaq.com/forum/t/62939/0/ ?
    It deals with the same error messages and they have a solution that helps some, but unfortunately not me. I still have those error messages...

    Niels Dybdahl
  • 14. Re: Data pump: by using dbms_datapump
    qmar Newbie
    Currently Being Moderated
    Hi - it's been a while since You had this problem but can You explain me what was the cause of the problem.
    On my database (oracle 10g) i am executing procedure to export selected data from one table and the operation succeeds.
    Then I am trying to do the same operation on other database and I am gettin the error:

    ORA-31623: a job is not attached to this session via the specified handle
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 4710
    ORA-06512: at line 83

    have you resolved what the problem is ?
1 2 Previous Next