Oracle Datapump PL/SQL Example of a Schema Import

CREATE 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
/

---------------------------

CREATE PROCEDURE p_imp_schema(p_schema_name IN VARCHAR2, -- schema to load
                                                 p_dumpfile_name IN VARCHAR2 -- dumpfile_name to load
                                                                            )
IS
    /*


        Purpose: Import a schema

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

    */
    v_sid VARCHAR2(200) := fn_get_sid; -- sid for this databaes
    v_handle NUMBER; -- job handle
    v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
    v_start_time DATE; -- start time for log file
    v_logfile_name VARCHAR2(200); -- logfile name
    v_dumpfile_name VARCHAR2(200); -- logfile name
    v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
    v_degree INTEGER := 1; -- degree of parallelism
    v_line_no INTEGER := 0; -- debug line no
    v_sqlcode NUMBER; -- sqlcode
    v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
    vc_job_mode CONSTANT VARCHAR2(30) := 'SCHEMA'; -- Job mode
    vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
BEGIN
    v_line_no := 100; -- debug line no

    -- Create the log and dumpfile names
    IF v_compatible = 'COMPATIBLE'
    THEN
        v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
    ELSE
        v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
    END IF;

    v_line_no := 200; -- debug line no

    -- Open the job
    BEGIN
        v_handle :=
            DBMS_DATAPUMP.open(operation => vc_operation,
                               job_mode => vc_job_mode,
                               job_name => vc_operation || '_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI_SS'),
                               version => v_compatible);
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
            RAISE;
    END;

    v_line_no := 300; -- debug line no
    -- Add a logfile
    DBMS_DATAPUMP.add_file(handle => v_handle,
                           filename => v_logfile_name,
                           directory => v_default_dir,
                           filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
    v_line_no := 400; -- debug line no
    -- Add a datafile
    DBMS_DATAPUMP.add_file(handle => v_handle,
                           filename => p_dumpfile_name,
                           directory => v_default_dir,
                           filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
    v_line_no := 500; -- debug line no
    -- Filter for schema
    DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
    v_line_no := 710; -- debug line no
    -- Start the job
    DBMS_DATAPUMP.start_job(handle => v_handle);
    v_line_no := 720; -- debug line no
    DBMS_DATAPUMP.detach(handle => v_handle);
    v_line_no := 800; -- debug line no
EXCEPTION
    WHEN OTHERS
    THEN

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

        DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
        RAISE;
END p_imp_schema; -- Procedurep_imp_schema
/

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