CREATE OR REPLACE PROCEDURE hr.sp_export AS
/*
|| Procedure: HR.SP_EXPORT.PRC
||
|| Creates a nightly DataPump Export of Human Resources (HR) schema
||
*/
    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

BEGIN
    -----
    -- Build a handle for the export job
    -----
    JobHandle := 
        DBMS_DATAPUMP.OPEN(
             operation => 'EXPORT'
            ,job_mode => 'SCHEMA'
            ,remote_link => NULL
            ,job_name => 'HRTABLES'
            ,version => 'LATEST'
        );

    -----
    -- 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 => 'HRTABLES.DMP'
        ,directory => 'EXPORT_DIR'
        ,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE 
        ,filesize => '100M'
    );
  
    DBMS_DATAPUMP.ADD_FILE(
         handle => JobHandle
        ,filename => 'HRTABLES.log'
        ,directory => 'EXPORT_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 => 'IN (''HR'')'
        ,object_type => 'TABLE'
    );

    -----
    -- 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 sp_export;
/