I have 2 data dump files for import -
--> dev_expdp_part01.dmp --> dev_expdp_part02.dmp
I am using below stored procdure to import both the files one after another. But my stored procedure is only processing the first file - dev_expdp_part01.dmp and ignore the second dump file - dev_expdp_part02.dmp.
Please suggest what I am doing wrong in the below script,
create or replace PROCEDURE SP_DP_REFRESH as
ind NUMBER; -- Loop index
spos NUMBER; -- String starting position
slen NUMBER; -- String length for output
h1 NUMBER; -- Data Pump job handle
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
BEGIN
BEGIN
dbms_output.enable(1000000);
h1 := DBMS_DATAPUMP.open (operation => 'IMPORT', job_mode => 'TABLE',
job_name => 'IMPORT_JOB',
version => '19.0');
END;
BEGIN
DBMS_DATAPUMP.set_parallel (handle => h1, degree => 4);
END;
BEGIN
DBMS_DATAPUMP.add_file (handle => h1,
filename => 'dev_refresh.log',
directory => 'DATA_PUMP_DIR',
filetype => 3
);
END;
BEGIN
DBMS_DATAPUMP.add_file (handle => h1,
filename => 'dev_expdp_part%U.dmp',
directory => 'DATA_PUMP_DIR',
filetype => 1
);
END;
BEGIN
DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA','DBMAP2','DBMAP');
END;
BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
name => 'INCLUDE_METADATA',
VALUE => 0
);
END;
BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
name => 'TABLE_EXISTS_ACTION',
VALUE => 'TRUNCATE'
);
END;
BEGIN
DBMS_DATAPUMP.start_job (handle => h1, skip_current => 0, abort_step => 0);
END;
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
-- If the percentage done changed, display the new value.
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
-- If any work-in-progress (WIP) or error messages were received for the job,
-- display them.
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
-- Indicate that the job finished and detach from it.
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
BEGIN
DBMS_DATAPUMP.detach (handle => h1);
END;
END;