title: How do you make DBMS_DATAPUMP error if there's an error?
body: <p>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...</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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 =&gt; 'IMPORT'
                                   , job_mode =&gt; 'TABLE'
                                   , remote_link =&gt; 'SCHEMA.DB.DOMAIN.COM'
                                   , job_name =&gt; 'JOB_TEST_DP'
                                   , version =&gt; 'COMPATIBLE' );
     dbms_datapump.set_parameter( handle =&gt; l_handle
                                , name =&gt; 'TABLE_EXISTS_ACTION'
                                , value =&gt; 'TRUNCATE');
     dbms_datapump.metadata_filter( handle =&gt; l_handle
                                  , name =&gt; 'NAME_EXPR'
                                  , value =&gt; 'IN (''TMP_TEST_DATAPUMP'')');
     dbms_datapump.start_job(handle =&gt; l_handle);
  
     while true loop
        dbms_datapump.wait_for_job(handle =&gt; l_handle,job_state =&gt; l_status);
  
        if l_status in ('COMPLETED','STOPPED') then
           exit;
        end if;
  
        dbms_datapump.get_status( handle =&gt; l_handle
                                , mask =&gt; dbms_datapump.KU$_STATUS_JOB_ERROR
                                , job_state =&gt; l_job_state
                                , status =&gt; 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 &gt; 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
  </code></pre>
  
  <p>As you can see the number of records in the tables is different; the import has failed and no exception has been raised. <a href="http://danirey.wordpress.com/2009/08/13/the-gains-and-pains-of-dbms_datapump-part-i/" rel="nofollow noreferrer">Various</a> <a href="http://dbaharrison.blogspot.co.uk/2013/05/dbmsdatapump-finding-out-what-actual.html" rel="nofollow noreferrer">blogs</a> and <a href="https://dba.stackexchange.com/questions/72575/oracle-dbms-datapump-removes-dumpfile-after-succesful-import">DBA.SE questions</a> imply that some sort of error catching can be done; but I can't seem to manage it. </p>
  
  <p>How can you catch fatal errors in a DBMS_DATAPUMP import?</p>