CREATE OR REPLACE PROCEDURE SYS.

EXPDP(dump_dir in varchar2,target in
varchar2) AS
idx NUMBER; -- Loop index
JobHandle NUMBER; -- Data Pump job handle
PctComplete NUMBER; -- Percentage of job complete
JobState VARCHAR2(30); -- To keep track of job state
LogEntry ku$_LogEntry; -- For WIP and error messages
JobStatus ku$_JobStatus; -- The job status from get_status
Status ku$_Status; -- The status object returned by get_status
jobname VARCHAR2(130);
dumpfile VARCHAR2(130);
logfile VARCHAR2(130);
dt VARCHAR2(130);
vtmp VARCHAR2(130);
vtarget VARCHAR2(1024);
BEGIN
-----
-- Build a handle for the export job
-----
dt := to_char(sysdate,'_dd_mm_yyyy_hh_mi_ss_');
jobname := 'JOB'||dt;
dbms_output.enable(100000);

JobHandle :=
DBMS_DATAPUMP.OPEN(
operation => 'EXPORT'
,job_mode => 'SCHEMA'
,remote_link => NULL
,job_name => jobname
,version => 'LATEST'
);
dumpfile := target||'_DUMP_'||dt||'.DATA';
logfile := target||'_ID_'||to_char(JobHandle)||'_'||dt||'.LOG';
-----
-- Using the job handle value obtained, specify multiple dump files for
the job
-- and the directory to which the dump files should be written. Note
that the
-- directory object must already exist and the user account running the
job must
-- have WRITE access permissions to the directory
-----
DBMS_DATAPUMP.ADD_FILE(
handle => JobHandle
,filename => dumpfile
,directory => dump_dir
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);

DBMS_DATAPUMP.ADD_FILE(
handle => JobHandle
,filename => logfile
,directory => dump_dir
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

-----
-- Apply a metadata filter to restrict the DataPump Export job to only
return
-- selected tables and their dependent objects from the SH schema
-----
 DBMS_DATAPUMP.METADATA_FILTER(
handle => JobHandle
,NAME => 'SCHEMA_EXPR'
,VALUE => target
);
-----
-- Initiate the DataPump Export job
-----
DBMS_DATAPUMP.START_JOB(JobHandle);

-----
-- If no exception has been returned when the job was initiated, this
loop will
-- keep track of the job and return progress information until the job
is done
-----
PctComplete := 0;
JobState := 'UNDEFINED';

WHILE(JobState != 'COMPLETED') and (JobState != 'STOPPED')

LOOP
DBMS_DATAPUMP.GET_STATUS(
handle => JobHandle
,mask => 15 -- DBMS_DATAPUMP.ku$_status_job_error +
DBMS_DATAPUMP.ku$_status_job_status + DBMS_DATAPUMP.ku$_status_wip
,timeout => NULL
,job_state => JobState
,status => Status
);

JobStatus := Status.job_status;

-- Whenever the PctComplete value has changed, display it

IF JobStatus.percent_done != PctComplete THEN
DBMS_OUTPUT.PUT_LINE('*** Job percent done = ' ||
TO_CHAR(JobStatus.percent_done));
PctComplete := JobStatus.percent_done;
END IF;

-- Whenever a work-in progress message or error message arises,

display it
IF (BITAND(Status.mask,DBMS_DATAPUMP.ku$_status_wip) != 0) THEN
LogEntry := Status.wip;
ELSE
IF
(BITAND(Status.mask,DBMS_DATAPUMP.ku$_status_job_error) != 0) THEN
LogEntry := Status.error;
ELSE
LogEntry := NULL;
END IF;
END IF;

IF LogEntry IS NOT NULL THEN

idx := LogEntry.FIRST;
WHILE idx IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(LogEntry(idx).LogText);
idx := LogEntry.NEXT(idx);
END LOOP;
END IF;
END LOOP;
 -----
-- Successful DataPump Export job completion, so detach from the job
-----
DBMS_OUTPUT.PUT_LINE('Job has completed');
DBMS_OUTPUT.PUT_LINE('Final job state = ' || JobState);
DBMS_DATAPUMP.DETACH(JobHandle);

END;
/
---------------------------------------------------------------------------
------
CREATE OR REPLACE procedure SYS.doexp
as
begin
EXPDP('DATA_PUMP_DIR','IN (''SCOTT'',''HR'',''OE'',''SYSTEM'',''SYS'')');
end;
/
---------------------------------------------------------------------------
-------
BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'PRG_EXPDP'
,program_type => 'PLSQL_BLOCK'
,program_action => 'DOEXP();'
,number_of_arguments => 0
,enabled => FALSE
,comments => NULL
);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'PRG_EXPDP');
END;
/
---------------------------------------------------------------------------
----------
BEGIN
SYS.DBMS_SCHEDULER.CREATE_SCHEDULE
(
schedule_name => 'SCHED_EXPDP'
,start_date => TO_TIMESTAMP_TZ('','yyyy/mm/dd hh24:mi:ss.ff
tzh:tzm')
,repeat_interval => 'freq=weekly;
byday=sat,sun,mon,tue,wed,thu;byhour=20'
,end_date => NULL
,comments => NULL
);
END;
/
---------------------------------------------------------------------------
------------------
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'JEXPDP'
,schedule_name => 'SYS.SCHED_EXPDP'
,program_name => 'SYS.PRG_EXPDP'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
 ( name => 'JEXPDP'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JEXPDP'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_FULL);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JEXPDP'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JEXPDP'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JEXPDP'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JEXPDP'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JEXPDP'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JEXPDP'
,attribute => 'AUTO_DROP'
,value => TRUE);
END;
/