dbms_datapump for Export

Example of dbms_datapump for Export in a procedure that can be automated from dbms_jobsubmit:

CREATE OR REPLACE PROCEDURE "DWH_EXPORT"("P_JOB_NAME" OUT NOCOPY VARCHAR2)
IS

l_dp_handle NUMBER;
l_job_name varchar2(100);
l_file_name varchar2(100);

job_state varchar2(30);
status ku$_Status1010;

begin

l_job_name := 'DWH'to_char(sysdate,'YYYYMMDDHH24MISS');
l_file_name := 'DWH.dmp';

utl_file.fremove('DATAPUMPX',l_file_name);

l_dp_handle := DBMS_DATAPUMP.open( operation => 'EXPORT',
job_mode => 'TABLE',
remote_link => NULL,
job_name => l_job_name,
version => 'LATEST'
);

DBMS_DATAPUMP.add_file( handle => l_dp_handle,
filename => l_file_name,
directory => 'DATAPUMPX',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);

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

DBMS_DATAPUMP.metadata_filter( handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => 'IN (''WD_USER'')'
);

DBMS_DATAPUMP.metadata_filter( handle => l_dp_handle,
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_DATAPUMP.start_job(l_dp_handle);
p_job_name := l_job_name;

job_state := 'UNDEFINED';

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

DBMS_DATAPUMP.GET_STATUS(l_dp_handle,
dbms_datapump.KU$_STATUS_WIP,
-1,
job_state,
status);

END LOOP;

DBMS_DATAPUMP.detach(l_dp_handle);

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('Error:' sqlerrm ' for Job:' l_dp_handle);
RAISE_APPLICATION_ERROR (-20100,'Export Failed - 'sqlerrm);

END;