oracle – 如果出现错误,如何使DBMS_DATAPUMP错误？

当源表和目标表中的列不匹配时,DBMS_DATAPUMP不会失败.这意味着不会引发任何异常.我正在尝试使用GET_STATUS程序,以了解是否有任何错误,但遗憾的是似乎没有……

我的最终目标是DBMS_DATAPUMP在导入失败时引发异常.不同的列是一个简单的例子,因为我知道它应该失败.

这是我当前的代码(我故意模糊了模式名称).我正在使用的环境在两台服务器上是相同的,除了我已经在源表中添加了一个额外的列.我还执行表中行数的计数.

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

如您所见,表中的记录数量不同;导入失败,没有例外. Various blogs和DBA.SE questions暗示可以进行某种错误捕获;但我似乎无法管理它.

如何在DBMS_DATAPUMP导入中捕获致命错误？

解决方法

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,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,-1,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',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;

（编辑：海南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!