1 2 Previous Next 19 Replies Latest reply: Nov 9, 2011 10:07 AM by Nicolas.Gasparotto RSS

    Data pump: by using dbms_datapump

    user555019
      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
          Have you gone through The Data Pump API chapter with examples?
          • 2. Re: Data pump: by using dbms_datapump
            user555019
            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
              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
                user555019
                No, with command prompt I did not get the errors.
                • 5. Re: Data pump: by using dbms_datapump
                  user555019
                  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
                    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
                      user555019
                      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
                        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
                          user555019
                          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
                            user555019
                            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
                              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
                                Faxurda
                                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
                                  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
                                    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