How do you make DBMS_DATAPUMP error if there's an error?
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
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;