Datapump PL/SQL Example for Job Descripton Monitoring

There are 5 pieces in the ku$_STATUS1120 object, this one covers only the job_description.

TYPE ku$_Status1120 IS OBJECT
(
mask NUMBER, -- Status types present
wip ku$_LogEntry1010, -- Work in progress
job_description ku$_JobDesc1020, -- Complete job description
job_status ku$_JobStatus1120,-- Detailed job status
error ku$_LogEntry1010 -- Multi-level context errors
)

    PROCEDURE p_job_description(p_job_name               IN     dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
                                p_job_owner              IN     dba_datapump_jobs.owner_name%TYPE, -- job_owner from dba_datapump_job
                                p_bool_job_description   IN OUT BOOLEAN, -- true when Jobs status is true
                                p_operation              IN OUT VARCHAR2, -- operation
                                p_guid                   IN OUT VARCHAR, -- guid
                                p_job_mode               IN OUT VARCHAR2, -- job_mode
                                p_remote_link            IN OUT VARCHAR2, -- remote_link
                                p_platform               IN OUT VARCHAR2, -- platform
                                p_exp_platform           IN OUT VARCHAR2, -- exp_platform
                                p_global_name            IN OUT VARCHAR2, -- global_name
                                p_exp_global_name        IN OUT VARCHAR2, -- exp_global_name
                                p_instance               IN OUT VARCHAR2, -- instance
                                p_db_version             IN OUT VARCHAR2, -- db_version
                                p_exp_db_version         IN OUT VARCHAR2, -- exp_db_version
                                p_scn                    IN OUT NUMBER, -- scn
                                p_creator_privs          IN OUT VARCHAR2, -- creator_privs
                                p_start_time             IN OUT DATE, -- start_time
                                p_exp_start_time         IN OUT DATE, -- exp_start_time
                                p_term_reason            IN OUT NUMBER, -- term_reason
                                p_max_degree             IN OUT NUMBER, -- max_degree
                                p_log_file               IN OUT VARCHAR2, -- log_file
                                p_sql_file               IN OUT VARCHAR2, -- sql_file
                                p_parameters             IN OUT ku$_paramvalues1010 -- Parameters
                                                                                   )
    IS
        /*

                   Purpose: attaches and returns the status structure
                   Parses the bit

                   MODIFICATION HISTORY
                   Person      Date        Comments
                   ---------   ------      -------------------------------------------
                   dcox        2/20/2013    Initial Build

                   */

        v_line_no INTEGER := 0; -- debug line no
        v_handle NUMBER; -- job handle
        v_status ku$_status; -- job status
        v_sqlcode NUMBER; -- sqlcode
        vc_indefinate_wait_timeout CONSTANT INTEGER := -1; -- specify an indefinate wait
        v_job_state VARCHAR2(40); -- Job state from job_status procedure
    -- vc_timeout constant integer := 5; -- specify a xx seconds(s) timeout to wait for status ( Uncomment and use if necessary)
    BEGIN
        v_line_no := 100; -- debug line no
        -- Attach to a datapump session
        v_handle := DBMS_DATAPUMP.attach(job_name => p_job_name, job_owner => p_job_owner);
        v_line_no := 200; -- debug line no

        -- Monitor status
        DBMS_DATAPUMP.get_status(handle => v_handle,
                                 mask => DBMS_DATAPUMP.ku$_status_job_desc,
                                 timeout => vc_indefinate_wait_timeout,
                                 job_state => v_job_state,
                                 status => v_status);

        -- Decode the bit masks
        -- job status
        IF BITAND(v_status.mask, DBMS_DATAPUMP.ku$_status_job_desc) != 0
        THEN
            p_bool_job_description := TRUE;
        ELSE
            p_bool_job_description := FALSE;
        END IF;

        v_line_no := 400; -- debug line no

        IF p_bool_job_description
        THEN
            v_line_no := 401; -- debug line no
            -- Return data
            p_operation := v_status.job_description.operation; -- operation
            p_guid := v_status.job_description.guid; -- guid
            p_job_mode := v_status.job_description.job_mode; -- job_mode
            p_remote_link := v_status.job_description.remote_link; -- remote_link
            p_platform := v_status.job_description.platform; -- platform
            p_exp_platform := v_status.job_description.exp_platform; -- exp_platform
            p_global_name := v_status.job_description.global_name; -- global_name
            p_exp_global_name := v_status.job_description.exp_global_name; -- exp_global_name
            p_instance := v_status.job_description.instance; -- instance
            p_db_version := v_status.job_description.db_version; -- db_version
            p_exp_db_version := v_status.job_description.exp_db_version; -- exp_db_version
            p_scn := v_status.job_description.scn; -- scn
            p_creator_privs := v_status.job_description.creator_privs; -- creator_privs
            p_start_time := v_status.job_description.start_time; -- start_time
            p_exp_start_time := v_status.job_description.exp_start_time; -- exp_start_time
            p_term_reason := v_status.job_description.term_reason; -- term_reason
            p_max_degree := v_status.job_description.max_degree; -- max_degree
            p_log_file := v_status.job_description.log_file; -- log_file
            p_sql_file := v_status.job_description.sql_file; -- sql_file

            v_line_no := 500; -- debug line no
            -- Get parameters
            p_parameters := v_status.job_description.params; -- Parameter list

        END IF;

        -- Detach from handle
        BEGIN
            DBMS_DATAPUMP.detach(handle => v_handle);
        EXCEPTION
            WHEN OTHERS
            THEN
                NULL;
        END;

        v_line_no := 510; -- debug line no
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));

            BEGIN
                DBMS_DATAPUMP.detach(handle => v_handle);
            EXCEPTION
                WHEN OTHERS
                THEN
                    NULL;
            END;

            RAISE;

    END p_job_description; -- end procedure p_job_description

FUNCTION fn_get_sid
        RETURN VARCHAR2
    IS
        /*


            Purpose: Get's  the SID for the database

            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        10/19/2012    Initial Build

        */
        v_sid VARCHAR2(1000); -- sid for the database
    BEGIN

        SELECT name INTO v_sid FROM v$database;

        RETURN v_sid;
    EXCEPTION
        WHEN OTHERS
        THEN
            RETURN NULL;
    END fn_get_sid; -- Function fn_get_sid

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s