How do you make a DBMS_DATAPUMP error if there is an error?

Question

DBMS_DATAPUMP does not crash if the columns in the source and destination tables do not match. This means that no exceptions are raised. I am trying to use the GET_STATUS procedure to figure out if there are any errors, but unfortunately it seems like ...

My ultimate goal is that DBMS_DATAPUMP throws an exception if the import fails. Different columns are a simple example to work with, because I know that it should fail.

Here is my current code (I purposefully hid schema names). The environment that I use is identical on both servers, except that I added an extra column to the source table. I also do a count of the number of rows in a table.

connect schema/*@db1/db1
-- */

create table tmp_test_datapump as
 select u.*, cast(null as number) as break_it
   from user_tables u;

Table created.


select count(*) from tmp_test_datapump;

  COUNT(*)
----------
      1170

connect schema/*@db2/db2
-- */

set serveroutput on

create table tmp_test_datapump as
 select u.*
   from user_tables u;

Table created.

DATAPUMP . , .

declare
   l_handle number;
   l_status varchar2(255);
   l_job_state varchar2(4000);
   l_ku$status ku$_status1020;
begin
   l_handle := dbms_datapump.open( operation => 'IMPORT'
                                 , job_mode => 'TABLE'
                                 , remote_link => 'SCHEMA.DB.DOMAIN.COM'
                                 , job_name => 'JOB_TEST_DP'
                                 , version => 'COMPATIBLE' );
   dbms_datapump.set_parameter( handle => l_handle
                              , name => 'TABLE_EXISTS_ACTION'
                              , value => 'TRUNCATE');
   dbms_datapump.metadata_filter( handle => l_handle
                                , name => 'NAME_EXPR'
                                , value => 'IN (''TMP_TEST_DATAPUMP'')');
   dbms_datapump.start_job(handle => l_handle);

   while true loop
      dbms_datapump.wait_for_job(handle => l_handle,job_state => l_status);

      if l_status in ('COMPLETED','STOPPED') then
         exit;
      end if;

      dbms_datapump.get_status( handle => l_handle
                              , mask => dbms_datapump.KU$_STATUS_JOB_ERROR
                              , job_state => l_job_state
                              , status => l_ku$status);
      dbms_output.put_line('state: ' || l_job_state);
      if l_ku$status.error is not null and l_ku$status.error.count > 0 then

         for i in l_ku$status.error.first .. l_ku$status.error.last loop
            dbms_output.put_line(l_ku$status.error(i).logtext);
         end loop;
       end if;
   end loop;

end;
/

PL/SQL procedure successfully completed.

select count(*) from tmp_test_datapump;

  COUNT(*)
----------
        47

, ; , . blogs DBA.SE , ; .

DBMS_DATAPUMP?

Answer 1

dbms_datapump . , . BACKUP_INFO_MOD - PRAGMA AUTONOMOUS TRANSACTION, .
6.3 . ( ):

  CREATE OR REPLACE PROCEDURE BACKUP_EXECUTE (
    threads in number := 1 
  , dir in varchar2 := 'DATA_PUMP_DIR'
  ) AS 
    schemas varchar2(255);
    filename varchar2(255);
    path varchar2(255);
    errormsg varchar2(4000);

    handle number;
    job_state varchar2(30);
    --variables under this line are important to error handling
    logs ku$_LogEntry;
    lindx pls_integer;
    status ku$_Status;

    exporterr exception; --our exception to handle export errors

    [...]
  BEGIN    
    [...]
        schemas:=schema_list(indx).schema_name;

        --Full dir path for logs
        select directory_path into path from dba_directories where directory_name=dir;
        --If data not found then automatically raise NO_DATA_FOUND

        select to_char(sysdate, 'YYMMDDHH24MI-')||lower(schemas)||'.dmp' into filename from dual;

        backup_info_mod('insert',path||filename,schemas);

        begin --For inner exception handling on short fragment
          handle := dbms_datapump.open('EXPORT','SCHEMA');
          dbms_datapump.add_file(handle, filename, dir); --dump file
          dbms_datapump.add_file(handle, filename||'.log', dir,null,DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); --export log file
          dbms_datapump.metadata_filter(handle, 'SCHEMA_EXPR', 'IN ('''||schemas||''')');
          dbms_datapump.set_parallel(handle,threads);

          backup_info_mod(file_name=>path||filename, curr_status=>'IN PROGRESS');

          dbms_datapump.start_job(handle);

        --If job didn't start due to some errors, then let get some information
        exception
          when others then
            dbms_datapump.get_status(handle,8,0,job_state,status);
        --This will overwrite our job_state and status
        end;

        --Let go handle error if job_state was overwritten
        if job_state is not null then
          raise exporterr;
        else
          job_state:='UNDEFINED';
        end if;

        --Checking in loop if errors occurred. I'm not using wait_for_job
        --because it didn't work out
        while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
        --Like before, let get some information
          dbms_datapump.get_status(handle,8,-1,job_state,status);

        --Looking for errors using mask
          if (bitand(status.mask,dbms_datapump.ku$_status_job_error) != 0) then          
        --If occurred: let stop the export job and raise an error
            dbms_datapump.stop_job(handle);
            dbms_datapump.detach(handle);
            raise exporterr;
          exit;

          end if;
        end loop;

        backup_info_mod(file_name=>path||filename, curr_status=>'COMPLETED');

        dbms_datapump.detach(handle);

      exception
        when NO_DATA_FOUND then
          backup_info_mod('insert',null,schemas,'ERROR','No '||dir||' defined in dba_directories');

        when exporterr then
        --Let get all error messages and write it to errormsg variable
          logs:=status.error;
          lindx:=logs.FIRST;

          while lindx is not null loop
            errormsg:=errormsg||logs(lindx).LogText;
            lindx:=logs.NEXT(lindx);

            if lindx is not null then
              errormsg:=errormsg||' | '; --Just to separate error messages
            end if;
          end loop;

          backup_info_mod(
            file_name=>path||filename,
            curr_status=>'ERROR',
            errormsg=>errormsg);

        /*when other then --TODO
          null;
          */
      end;

  END BACKUP_EXECUTE;

Answer 2

datapump script, impdp, , script, IMP ORA-, true warn .

Answer 3

yammy .

I encountered the same problem when using the dbms_datapump package to import a database dump. Even during import errors occur, the work is considered successful / completed. Using the sample code in the document, I could get an error / log message during import. Then I check for the presence of “ORA-” in the log message and issue a user error when the import task is completed.

The following is sample code:

PROMPT CREATE OR REPLACE PROCEDURE import_schema

CREATE OR REPLACE PROCEDURE import_db_dump (
    dumpFilename        IN VARCHAR2)
IS
    handle NUMBER;              -- Handler of the job
    loopIdx NUMBER;             -- Loop index
    percentDone NUMBER;         -- Percentage of job complete
    jobState VARCHAR2(30);      -- To keep track of job state
    ku_logEntry ku$_LogEntry;   -- For WIP and error messages
    ku_jobStatus ku$_JobStatus; -- The job status from get_status
    ku_jobDescjd ku$_JobDesc;   -- The job description from get_status
    ku_Status ku$_Status;       -- The status object returned by get_status

    errorCount          NUMBER;
    import_error_found  EXCEPTION;

BEGIN
    handle := dbms_datapump.open (
    operation =>    'IMPORT',
    job_mode =>     'SCHEMA');

    dbms_output.put_line('Define table exists action: truncate');
    dbms_datapump.set_parameter (
        handle =>   handle,
        name =>     'TABLE_EXISTS_ACTION',
        value =>    'TRUNCATE');

    dbms_output.put_line('Define dumpfilename: ' || dumpFilename);
    dbms_datapump.add_file (
        handle =>       handle,
        filename =>     dumpFilename,
        filetype =>     dbms_datapump.ku$_file_type_dump_file);

    dbms_output.put_line('Start datapump job');
    dbms_output.put_line('==================');
    dbms_datapump.start_job (handle);

    -- Ref: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_api.htm#SUTIL977

    -- The import job should now be running. In the following loop, the job is
    -- monitored until it completes. In the meantime, progress information is
    -- displayed.

    percentDone := 0;
    jobState := 'UNDEFINED';
    errorCount := 0;
    WHILE (jobState != 'COMPLETED') AND (jobState != 'STOPPED') LOOP
        dbms_datapump.get_status(handle,
               dbms_datapump.ku$_status_job_error +
               dbms_datapump.ku$_status_job_status +
               dbms_datapump.ku$_status_wip,
               -1, jobState, ku_Status);
        ku_jobStatus := ku_Status.job_status;

        -- If the percentage done changed, display the new value.
        IF ku_jobStatus.percent_done != percentDone THEN
          dbms_output.put_line('*** Job percent done = ' ||
                               to_char(ku_jobStatus.percent_done));
          percentDone := ku_jobStatus.percent_done;
        END IF;

        -- If any work-in-progress (WIP) or Error messages were received for the job,
        -- display them.

        IF (bitand(ku_Status.mask, dbms_datapump.ku$_status_wip) != 0) THEN
            ku_logEntry := ku_Status.wip;
        ELSE
            IF (bitand(ku_Status.mask,dbms_datapump.ku$_status_job_error) != 0) THEN
                ku_logEntry := ku_Status.error;
            ELSE
                ku_logEntry := null;
            END IF;
        END IF;

        IF ku_logEntry IS NOT NULL THEN
            loopIdx := ku_logEntry.FIRST;
            WHILE loopIdx IS NOT NULL LOOP
                dbms_output.put_line(ku_logEntry(loopIdx).LogText);
                IF INSTR(ku_logEntry(loopIdx).LogText, 'ORA-') > 0 THEN
                    errorCount := errorCount + 1;
                    dbms_output.put_line('^^^^---ERROR FOUND');
                END IF;
                loopIdx := ku_logEntry.NEXT(loopIdx);
            END LOOP;
        END IF;

    END LOOP;

    -- Indicate that the job finished and gracefully detach from it.
    dbms_output.put_line('Job has completed');
    dbms_output.put_line('Final job state = ' || jobState);
    dbms_datapump.detach(handle);

    IF errorCount > 0 THEN
        RAISE import_error_found;
    END IF;

EXCEPTION
    WHEN import_error_found THEN
        dbms_output.put_line('Error found when import. Number of error: ' || errorCount);
        RAISE;

    WHEN OTHERS THEN
        dbms_output.put_line('[Error Backtrace]');
        dbms_output.put_line(dbms_utility.format_error_backtrace());
        dbms_output.put_line('[Call Stack]');
        dbms_output.put_line(dbms_utility.format_call_stack());

        dbms_datapump.stop_job(handle);
        RAISE;
END;
/

I hope for this help.