Using dbms_datapump for import

Doing the import this way can be done straight over a database link so one doesn't even need to do an export.

create or replace procedure dwh_import
as

ind NUMBER; -- Loop index number
jobhandle NUMBER; -- Data Pump job handle
l_job_name varchar2(100); -- Job Name
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- Keeps track of job state
le ku$_LogEntry; -- work-in-progress and error messages
js ku$_JobStatus; -- Job status from get_status

sts ku$_Status; -- Status object returned by get_status

BEGIN

l_job_name := 'DWHIMPORT'to_char(sysdate,'YYYYMMDDHH24MISS');
jobhandle := DBMS_DATAPUMP.OPEN(operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => 'APOLLO',
job_name => l_job_name,
version => 'COMPATIBLE');

dbms_output.put_line('jobhandle is : 'jobhandle);

DBMS_DATAPUMP.add_file( handle => jobhandle,
filename => 'DWHIMPORT.log',
directory => 'DATAPUMPX',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

dbms_output.put_line('Import Log file is created..');

DBMS_DATAPUMP.SET_PARALLEL(jobhandle, 1);
dbms_output.put_line('parallel set to 1');

DBMS_DATAPUMP.METADATA_REMAP(handle => jobhandle,
name => 'REMAP_SCHEMA',
old_value => 'WD_USER',
value => 'BI_USER');
dbms_output.put_line('metadat_remap');

DBMS_DATAPUMP.SET_PARAMETER(jobhandle,'TABLE_EXISTS_ACTION','REPLACE');
dbms_output.put_line('set_parameter');

-- dbms_datapump.metadata_filter (handle => my_handle, name => 'NAME_EXPR',
-- value => 'LIKE ''DWH_%''', object_type => 'TABLE');

DBMS_DATAPUMP.metadata_filter( handle => jobhandle,
name => 'NAME_EXPR',
value => 'IN (''DWH_CANVASS'',''DWH_CANVASS_DATE'',''DWH_CHANNEL'',
''DWH_CUSTOMER'',''DWH_CYCLE_DATE'', ''DWH_DATE'',
''DWH_DOS_APPOINTMENTS'',''DWH_DOS_SALESREP_TARGET'',
''DWH_DOS_STATE_OF_PLAY'',''DWH_DOS_TEAM'',''DWH_GG_ICANVASS'',
''DWH_GG_WD_CHANNEL'',''DWH_GG_WD_PRODUCTS'',''DWH_GP_WD_CHANNEL'',
''DWH_GP_WD_PRODUCTS'',''DWH_MANDAYS_ACTUALS'',
''DWH_MANDAYS_PLANNED'',''DWH_PA_WD_CHANNEL'',''DWH_PA_WD_PRODUCTS'',
''DWH_PM_WD_CHANNEL'',''DWH_PM_WD_PRODUCTS'',''DWH_PRODUCT'',
''DWH_REP'',''DWH_SALES'',''DWH_SALES_STATUS'',''DWH_TARGET'',
''DWH_UNIT'',''DWH_WD_CANVASS_GROUPS'',''DWH_WD_CHANNEL'',
''DWH_WD_CHANNEL_GRP'',''DWH_WD_DAYOFF'',''DWH_WD_FINANCE_MONTH'',
''DWH_WD_PRODUCT'',''DWH_REFRESH_DATES'',''DWH_MTG_TARGET'',''DWH_DAY_TARGET_ALL'',
''DWH_DAY_TARGET_SALES'',''DWH_DAY_TARGET_CANV'')');
dbms_output.put_line('Name Export');

dbms_datapump.metadata_remap(handle => jobhandle,
name => 'REMAP_TABLESPACE',
old_value => 'DWH_TAB_SMALL',
value => 'BI_USER_TS');

dbms_datapump.metadata_remap(handle => jobhandle,
name => 'REMAP_TABLESPACE',
old_value => 'DWH_TAB_LARGE',
value => 'BI_USER_TS');

dbms_datapump.metadata_remap(handle => jobhandle,
name => 'REMAP_TABLESPACE',
old_value => 'DWH_IND_LARGE',
value => 'USERS');

dbms_datapump.metadata_remap(handle => jobhandle,
name => 'REMAP_TABLESPACE',
old_value => 'DWH_IND_SMALL',
value => 'USERS');

DBMS_DATAPUMP.SET_PARAMETER(jobhandle,'ESTIMATE','STATISTICS');
dbms_output.put_line('Estimate ');

DBMS_DATAPUMP.START_JOB(jobhandle);
dbms_output.put_line('Job Started');

percent_done := 0;
job_state := 'UNDEFINED';

WHILE (job_state != 'COMPLETED') and (job_state != 'STOPPED') LOOP

DBMS_DATAPUMP.get_status(jobhandle,
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 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 (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;


DBMS_OUTPUT.PUT_LINE('Job has completed');
DBMS_OUTPUT.PUT_LINE('Final job state = ' job_state);
DBMS_DATAPUMP.DETACH(jobhandle);

END;