How do you make DBMS_DATAPUMP error if there's an error?

Question

DBMS_DATAPUMP doesn't fail when the columns in the source and destination tables do not match. This means that no exceptions are raised. I'm trying to use the GET_STATUS procedure in order to understand if there are any errors but unfortunately there doesn't seem to be...

My ultimate goal is for DBMS_DATAPUMP to raise an exception if the import fails. Differing columns is an easy example to work with as I know that it should fail.

Here's my current code (I've obscured schema names purposefully). The environment I'm using is identical on both servers save that I've added an extra column to the source table. I also perform a count of the number of rows in the 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.

In attempting to test this the DATAPUMP code has got a little more complicated. Everything in the infinite loop can be removed and this would act the same.

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

As you can see the number of records in the tables is different; the import has failed and no exception has been raised. Various blogs and DBA.SE questions imply that some sort of error catching can be done; but I can't seem to manage it.

How can you catch fatal errors in a DBMS_DATAPUMP import?

Answer 1

I'm working with dbms_datapump package right know. The following procedure is searching one table for schemas that will be exported. BACKUP_INFO_MOD is a procedure with PRAGMA AUTONOMOUS TRANSACTION that's making logs in another table.
Example 6.3 from this document helped me a lot. Here's fragment from my code (with additional commentary):

  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's 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's 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's 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's 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's 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;