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

概述当源表和目标表中的列不匹配时,DBMS_DATAPUMP不会失败.这意味着不会引发任何异常.我正在尝试使用GET_STATUS程序,以了解是否有任何错误,但遗憾的是似乎没有…… 我的最终目标是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(*)----------      1170connect schema/*@db2/db2-- */set serveroutput oncreate 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导入中捕获致命错误？

解决方法 我正在使用dbms_datapump包知道.以下过程在一个表中搜索将要导出的模式. BACKUP_INFO_MOD是一个PRAGMA autoNOMOUS TRANSACTION的程序,它在另一个表中创建日志.
this document的例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'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;

总结

以上是内存溢出为你收集整理的oracle – 如果出现错误,如何使DBMS_DATAPUMP错误？全部内容，希望文章能够帮你解决oracle – 如果出现错误,如何使DBMS_DATAPUMP错误？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。