Table Driven Data Pump Applications – Lessons learned

After building a real world table driven data pump application (see blog Table Driven Data Pump), thought recording a few lessons learned / notes before forgetting would be helpful.

  • Create job control as part of the overall application.
  • Watch out for Character Set issues.
  • Jobs should be able to re-use generic tasks from other jobs.
  • Jobs should allowing grouping sub-jobs.
  • Progress monitoring queries should be considered in advance.
  • Informational and error logging are very valuable in debugging.
  • Intermingling SQL tasks with Data Pump tasks is awesome.
  • Version control from a remote master for all aspects of the code is a must.
  • Standard user provisioning inclusion is helpful.
  • Consider any encryption issues (current and future) as part of the initial plan.
  • Nodding Donkey Oil – A Fake Company Example of Data Pump Application

    Updated 3/29/13

    OK, hope the examples of the PL/SQL helped, but I think it’s time to make this datapump exploration into something useful. How about coding a set of imports, exports, SQL and putting it all in a table to be executed by PL/SQL and running it all with one procedure?

    This is a long post so you’ll have to drill deep to find the crude oil here.

    Let’s make up a fake company and scenario to be used here. Our company Nodding Donkey Oil has engineers using laptops to record information at various sites. These sites are so remote that they have no connectivity and many times have to do complex queries on a subset of the centralized data in the field. Let’s say we have about 500 of these laptop carrying engineers all using Oracle XE. When they do get back to civilization, they connect to their centralized database via datapump and dump their data into a “cache” database where the data then gets pushed to their central database store. The laptop then goes into the back of the truck until they are called up again. When they do get another call they will rebuild this laptop data for a particular job order at a particular Nodding Donkey pump site.

    By using Oracle XE, we’ve cut out database cost, because it’s free with some limitations. It’s the same database we use the most so we have the knowledge base. It has a max memory limit of 1G, and a max storage capacity of 11G for the data. It doesn’t run the datapump in parallel, but it’s still fast enough for 11G of data. It doesn’t have some of the specialized database functionality like the Enterprise Edition, but for free it works fine for our purpose.

    Our datapump engine will be initiated with a particular job number and the data sync process will occur and be managed all from a set of tables and packages we’ll reveal below.

    Let’s design the table structure: A job table that keeps our sets of master jobs we may want to execute, a task table that has each and every task and the order of the execution. Parameter tables for FULL, SCHEMA, and SQL that are really just further distinctions of each task that may be executed. Thinking of these as parameter tables makes the most sense. Each record in FULL, SCHEMA, and SQL tables have a one to one relationship with one of the TASKS. This could be changed to one Job table and one Task table with all the values together, but this choice makes it easier to understand and easy to modify later. It also makes data entry a bit of a pain since you have to carry the task id down to the SCHEMA, FULL and SQL tables. A data entry app will can make this easier, or you could use the sequence generator only at the FULL, SCHEMA and SQL tables and add the TASK entry as a trigger, making changes to it later. All these options work, may want to pick the best one for you. You could add the two records (TASK and SCHEMA for example) at the same time and could even do this with a trigger and default TASK values from the Lower level table. Or create a app that does this for you.

    DP_LOGGING and REMAP_TABLESPACE are fairly self explanatory. A logging table and a table to remap the tablespaces. For the quick solution you can set a default tablespace in the code to catch all that is not in the remap tablespace table. So for large database on non-XE cases, you might fill in the REMAP_TABLESPACE table, but for XE the default works fine. The remaining table, IMP_USER_ROLE_CR_PREF is used to handle the case where you import via remote link and the local users, roles, etc are not set up. If you don’t have a user it can be added and you move on; however if you have a database link you can do a bit more. Used with this table there is a utility pkg_dp_util.fn_create_permissions_script that will help in creating permissions across databases. The script is created locally and is to be run on the remote database via SQL*Plus and it’s resulting output will populate the values locally. BE SURE TO LOOK THROUGH THIS before you run it. Also if you don’t want users added to your target database on import, be sure to populate this table (IMP_USER_ROLE_CR_PREF) and set the preference to N)o ahead of the import, or comment out that part of the code.

    Let’s talk about some examples all the code is at the end of this blog. Note the packages and tables here may be slightly different than the other blogs on https://oraclesage.wordpress.com on this subject. All this codes works as a system and if you are following along with the examples, it’s left up to you to populate the tables the way you want. There has been a lot of cutting and pasting here, so if something doesn’t work for you or you don’t understand the goal let me know. There are many ways of solving this problem and I’ve done this exercise mostly for my education and future reference. Some things are a little inconsistent and I may improve on this if I can find the time.

    I’ve left off some columns, like the “–stamp” columns, and effectivities the table entries. Note I didn’t add any filter for the effectivities for the code below, but it could be easily added.

    I’m assuming at this point you have the code below installed and all tuned up.

    Let’s build a job adding steps as we go, we’ll start with Job ID 1:

    INSERT INTO oradba.dp_job (DP_JOB_ID,JOB_NAME,JOB_DESCRIPTION) 
    VALUES(1,'Nodding Donkey ','Export, import new schema, import table, import list of tables, execute sql');
    

    In datapump we can start a job and let it complete in the background, and start another. Or we can wait until it completes. So if we build dependent tasks we need to have their predecessors be monitored to completion before running the next step. But if we can run these independently, we get a big time savings by running these in the background as we start another. This assumes we have a little extra performance we can squeeze out of our platform. If you’re on an overloaded server this might not help.

    So we have a basic job that runs a full export, and that is just a safety net, so let’s set Monitor_to_completion to Y. We want our initial full export to complete before we continue.

    Let’s add a user drop, user add, SCHEMA import, Table import, then import a set of tables defined by the table we just brought over. Add some permissions with SQL, and another full export to backup what we just did. And now we have a table driven set of data imported with datapump. We can run this again on another machine by just copying the schema ORADBA listed below to a new machine.

    Here’s an example of the table entries, you have the Job entry above.

    Task Entries – what we are doing and in what order:

    -- Full export - and wait till it is complete
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(2,1,'Full Local Export','Initial  Export','FULL','EXPORT','Y',10);
    -- drop a user (sql)
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(89,1,'Drop User INV','INV','SQL','SQL_EXEC','Y',15);
    -- drop another user
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(79,1,'Drop User DWGS','DWGS','SQL','SQL_EXEC','Y',20);
    -- Add a user
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(80,1,'Add User DWGS',NULL,'SQL','SQL_EXEC','Y',30);
    -- Import a schema
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(66,1,'Imp Inv Schema',NULL,'SCHEMA','IMPORT','Y',40);
    -- drop a table
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(84,1,'Drop table Old',NULL,'SQL','SQL_EXEC','Y',43);
    -- Import a table
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(67,1,'Imp List of Tables Table',NULL,'TABLE','IMPORT','Y',45);
    -- import a set of tables from the list in the preceding table
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(74,1,'Import List of tables','Read the table and import the list','SCHEMA','IMPORT','N',50);
    -- grant a role - not waiting for completion
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(81,1,'Grant Resource',NULL,'SQL','SQL_EXEC','N',60);
    -- perform a final export, but don't wait for it to complete - job is done once submitted
    INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER) 
    VALUES(83,1,'Full Export Local','Final Export','FULL','EXPORT','N',70);
    

    Full Records, not much here, but it can be expanded to include other parameters – full exports and imports

    INSERT INTO oradba.dp_full (DP_TASK_ID,REMOTE_LINK) 
    VALUES(83,NULL);
    INSERT INTO oradba.dp_full (DP_TASK_ID,REMOTE_LINK) 
    VALUES(2,NULL);
    

    Schema Records, no tablespace changes so those columns left off as well:

    -- full schema INV from db link CO
    INSERT INTO oradba.dp_schema (DP_TASK_ID,SCHEMA_NAME,REMOTE_LINK,DEGREE,TABLE_NAME,TABLE_SUBQUERY,EXPR_LIST) 
    VALUES(66,'INV','CO',1,NULL,NULL,NULL);
    -- table from user DWGS named master table
    INSERT INTO oradba.dp_schema (DP_TASK_ID,SCHEMA_NAME,REMOTE_LINK,DEGREE,TABLE_NAME,TABLE_SUBQUERY,EXPR_LIST) 
    VALUES(67,'DWGS','CO',1,'TABLE_MASTER',NULL,NULL);
    -- all the tables listed in the table dwgs.master_table
    INSERT INTO oradba.dp_schema (DP_TASK_ID,SCHEMA_NAME,REMOTE_LINK,DEGREE,TABLE_NAME,TABLE_SUBQUERY,EXPR_LIST) 
    VALUES(74,'DWGS','CO',1,NULL,NULL,'in (SELECT a.table_name FROM DWGS.TABLE_MASTER a  where job_name like ''SITE_A431'' )');
    

    SQL Records:

    -- standard sql with no ending ";" or "/"
    INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN) 
    VALUES(80,'CREATE USER DWGS
    IDENTIFIED BY DWGS
    DEFAULT TABLESPACE USERS 
    TEMPORARY TABLESPACE TEMP','N');
    INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN) 
    VALUES(79,'drop user DWGS cascade','N');
    INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN) 
    VALUES(89,'drop user inv cascade','Y');
    INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN) 
    VALUES(84,'drop table DWGS.TABLE_MASTER','N');
    INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN) 
    VALUES(81,'grant resource to DWGS','N');
    

    Note the column above MONITOR_TO_COMPLETION = ‘N’. If we change this column to ‘Y’, the program will execute to completion and not return control back the executing source until the full export is complete.

    Now if we run the job in an anonymous block, and then check the dump directory specified, we see it is still running. You can also monitor the activity from the dp_logging table.

    ...
     oradba.pkg_dp_main.p_start_job
        (p_job_id=>1,
         p_completion_code=>p_completion_code,
         p_complete_status=>p_complete_status);
    ...
    

    So now you have a project about 75% complete and if anyone is interested in this project, I’ll do some more work on this if not, I’ll move on. At least this can serve as an example of a data driven data pump process that anyone can code.

    Some issues with this code:
    This is more of a Proof of concept, needs more validation and error checking along the way, could combine some of these procedures to simplify the code, some inconsistent coding – would like to see more consistent parameters throughout, lots of code not added, and more. Having listed some of the issues, it does work and could be extended to make a nicer application that could be very useful. Keeping the logic in the tables makes it easy to maintain once the application is complete. Changing the way data is moved would be easy to maintain and very flexible. I’m sure someone else has already done this. Wouldn’t be a long stretch to use this for sub-setting development databases from production and automating the load.

    I encourage you to leave comments if this is interesting, and I’ll add to it if there is any interest.

    As sys grant execute on utl_file and utl_tcp to oradba.

    Here is the rest of the code:
    Sequences :

    CREATE SEQUENCE oradba.seq_dp_full
        INCREMENT BY 1
        START WITH 1000
        MINVALUE 1
        MAXVALUE 9999999999999999999999999999
        NOCYCLE
        ORDER
        NOCACHE
    /
    CREATE SEQUENCE oradba.seq_dp_job
        INCREMENT BY 1
        START WITH 1000
        MINVALUE 1
        MAXVALUE 9999999999999999999999999999
        NOCYCLE
        ORDER
        NOCACHE
    /
    CREATE SEQUENCE oradba.seq_dp_logging
        INCREMENT BY 1
        START WITH 1000
        MINVALUE 1
        MAXVALUE 9999999999999999999999999999
        NOCYCLE
        NOORDER
        NOCACHE
    /
    CREATE SEQUENCE oradba.seq_dp_schema
        INCREMENT BY 1
        START WITH 1000
        MINVALUE 1
        MAXVALUE 9999999999999999999999999999
        NOCYCLE
        ORDER
        NOCACHE
    /
    CREATE SEQUENCE oradba.seq_dp_task
        INCREMENT BY 1
        START WITH 1000
        MINVALUE 1
        MAXVALUE 9999999999999999999999999999
        NOCYCLE
        ORDER
        NOCACHE
    /
    CREATE SEQUENCE oradba.seq_imp_user_role_cr_pref
        INCREMENT BY 1
        START WITH 1000
        MINVALUE 1
        MAXVALUE 9999999999999999999999999999
        NOCYCLE
        ORDER
        NOCACHE
    /
    

    Tables:

    CREATE TABLE oradba.dp_full
    (
        dp_task_id NUMBER(15, 0) NOT NULL,
        remote_link VARCHAR2(30 BYTE),
        insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL,
        CONSTRAINT dp_full_pk PRIMARY KEY(dp_task_id) USING INDEX
    )
    SEGMENT CREATION IMMEDIATE
    NOPARALLEL
    LOGGING
    MONITORING
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_full_bu
        BEFORE UPDATE
        ON oradba.dp_full
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_full_bi
        BEFORE INSERT
        ON oradba.dp_full
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    COMMENT ON TABLE oradba.dp_full IS 'Defines full export
    3/12/13 dcox'
    /
    COMMENT ON COLUMN oradba.dp_full.dp_task_id IS 'FK to Task Table - Unique numerial id for this table as well'
    /
    COMMENT ON COLUMN oradba.dp_full.insert_ts IS 'Insert timestamp'
    /
    COMMENT ON COLUMN oradba.dp_full.insert_user IS 'Insert userstamp'
    /
    COMMENT ON COLUMN oradba.dp_full.remote_link IS 'Remote Link when pulling full export/import'
    /
    COMMENT ON COLUMN oradba.dp_full.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_full.update_user IS 'Update Userstamp'
    /
    CREATE TABLE oradba.dp_job
    (
        dp_job_id NUMBER(15, 0) NOT NULL,
        job_name VARCHAR2(20 BYTE) NOT NULL,
        job_description VARCHAR2(300 BYTE),
        eff_date_start TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        eff_date_stop TIMESTAMP(6) WITH LOCAL TIME ZONE,
        insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL,
        CONSTRAINT dp_job_pk PRIMARY KEY(dp_job_id) USING INDEX
    )
    SEGMENT CREATION IMMEDIATE
    NOPARALLEL
    LOGGING
    MONITORING
    /
    
    ALTER TABLE oradba.dp_job
    ADD CONSTRAINT dp_job_uk1 UNIQUE (job_name)
    USING INDEX
    /
    
    ALTER TABLE oradba.dp_job
    ADD CONSTRAINT db_job_ck1 CHECK (eff_date_start < NVL(eff_date_stop,eff_date_start+1)
    )
    DISABLE NOVALIDATE
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_job_bu
        BEFORE UPDATE
        ON oradba.dp_job
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_job_bi
        BEFORE INSERT
        ON oradba.dp_job
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        SELECT seq_dp_job.NEXTVAL INTO :new.dp_job_id FROM DUAL;
    
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    COMMENT ON TABLE oradba.dp_job IS 'This is the datapump (DP) master job list (one job for many tasks).
    3/12/13 dcox'
    /
    COMMENT ON COLUMN oradba.dp_job.dp_job_id IS 'Job ID'
    /
    COMMENT ON COLUMN oradba.dp_job.eff_date_start IS 'Effective Start Date'
    /
    COMMENT ON COLUMN oradba.dp_job.eff_date_stop IS 'Effective Stop Date'
    /
    COMMENT ON COLUMN oradba.dp_job.insert_ts IS 'Insert Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_job.insert_user IS 'Insert Userstamp'
    /
    COMMENT ON COLUMN oradba.dp_job.job_description IS 'Job Description - detailed description'
    /
    COMMENT ON COLUMN oradba.dp_job.job_name IS 'Job Name - unique name for this job'
    /
    COMMENT ON COLUMN oradba.dp_job.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_job.update_user IS 'Update Userstamp'
    /
    CREATE TABLE oradba.dp_logging
    (
        dp_logging_id NUMBER(15, 0) NOT NULL,
        code_returned NUMBER(30, 0) NOT NULL,
        MESSAGE_TEXT VARCHAR2(4000 BYTE) NOT NULL,
        line_no NUMBER(*, 0) NOT NULL,
        object_schema VARCHAR2(30 BYTE) NOT NULL,
        object_name VARCHAR2(65 BYTE) NOT NULL,
        job_id NUMBER(15, 0),
        task_id NUMBER(15, 0),
        insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL,
        CONSTRAINT db_logging_pk PRIMARY KEY(dp_logging_id) USING INDEX
    )
    SEGMENT CREATION IMMEDIATE
    NOPARALLEL
    LOGGING
    MONITORING
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_logging_bi
        BEFORE INSERT
        ON oradba.dp_logging
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
        SELECT oradba.seq_dp_logging.NEXTVAL INTO :new.dp_logging_id FROM DUAL;
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_logging_bu
        BEFORE UPDATE
        ON oradba.dp_logging
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    COMMENT ON TABLE oradba.dp_logging IS 'Logging table mainly for errors and success, but could also contain informational logging.
    3/12/13 dcox'
    /
    COMMENT ON COLUMN oradba.dp_logging.code_returned IS '0 is success, 1 is informational, all others are errors'
    /
    COMMENT ON COLUMN oradba.dp_logging.dp_logging_id IS 'Unique Numerical Identifier'
    /
    COMMENT ON COLUMN oradba.dp_logging.insert_ts IS 'Insert Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_logging.insert_user IS 'Insert Userstamp'
    /
    COMMENT ON COLUMN oradba.dp_logging.job_id IS 'Job ID'
    /
    COMMENT ON COLUMN oradba.dp_logging.line_no IS 'Line number where error or issue occurred'
    /
    COMMENT ON COLUMN oradba.dp_logging.MESSAGE_TEXT IS 'Message Text'
    /
    COMMENT ON COLUMN oradba.dp_logging.object_name IS 'Object Name where message was logged.  Could be package.procedure or package.function.'
    /
    COMMENT ON COLUMN oradba.dp_logging.object_schema IS 'Object_schema where message was logged'
    /
    COMMENT ON COLUMN oradba.dp_logging.task_id IS 'Task ID'
    /
    COMMENT ON COLUMN oradba.dp_logging.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_logging.update_user IS 'Update Userstamp'
    /
    CREATE TABLE oradba.dp_schema
    (
        dp_task_id NUMBER(15, 0) NOT NULL,
        schema_name VARCHAR2(30 BYTE) NOT NULL,
        insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL,
        remote_link VARCHAR2(30 BYTE),
        degree NUMBER(*, 0) NOT NULL,
        tablespace_from VARCHAR2(30 BYTE),
        tablespace_to VARCHAR2(30 BYTE),
        table_name VARCHAR2(30 BYTE),
        table_subquery VARCHAR2(4000 BYTE),
        expr_list VARCHAR2(4000 BYTE),
        dumpfile_name VARCHAR2(1000 BYTE),
        CONSTRAINT dp_schema_pk PRIMARY KEY(dp_task_id, schema_name) USING INDEX
    )
    SEGMENT CREATION IMMEDIATE
    NOPARALLEL
    LOGGING
    MONITORING
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_schema_bu
        BEFORE UPDATE
        ON oradba.dp_schema
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_schema_bi
        BEFORE INSERT
        ON oradba.dp_schema
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    COMMENT ON TABLE oradba.dp_schema IS 'Task definition for Schema to be moved
    3/12/13 dcox'
    /
    COMMENT ON COLUMN oradba.dp_schema.degree IS 'Degree of Parallism'
    /
    COMMENT ON COLUMN oradba.dp_schema.dp_task_id IS 'fk to Task ID,  also uk for this table'
    /
    COMMENT ON COLUMN oradba.dp_schema.dumpfile_name IS 'Dumpfile_name for local import files'
    /
    COMMENT ON COLUMN oradba.dp_schema.expr_list IS 'Expression - could be a query to other table lists - see blog for example'
    /
    COMMENT ON COLUMN oradba.dp_schema.insert_ts IS 'Insert Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_schema.insert_user IS 'Insert Userstamp'
    /
    COMMENT ON COLUMN oradba.dp_schema.remote_link IS 'Remote Database Link'
    /
    COMMENT ON COLUMN oradba.dp_schema.schema_name IS 'Schema to be imp/exp target'
    /
    COMMENT ON COLUMN oradba.dp_schema.table_name IS 'Table name - blank when it is for a schema'
    /
    COMMENT ON COLUMN oradba.dp_schema.table_subquery IS 'Table subquery (where clause)'
    /
    COMMENT ON COLUMN oradba.dp_schema.tablespace_from IS 'Import only - Tablespace from'
    /
    COMMENT ON COLUMN oradba.dp_schema.tablespace_to IS 'Import only - Tablespace to'
    /
    COMMENT ON COLUMN oradba.dp_schema.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_schema.update_user IS 'Update Userstamp'
    /
    CREATE TABLE oradba.dp_sql
    (
        dp_task_id NUMBER(15, 0) NOT NULL,
        sql_stmt VARCHAR2(4000 BYTE),
        insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL,
        ignore_error_yn VARCHAR2(1 BYTE) NOT NULL,
        CONSTRAINT dp_sql_pk PRIMARY KEY(dp_task_id) USING INDEX
    )
    SEGMENT CREATION IMMEDIATE
    NOPARALLEL
    LOGGING
    MONITORING
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_sql_bu
        BEFORE UPDATE
        ON oradba.dp_sql
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_sql_bi
        BEFORE INSERT
        ON oradba.dp_sql
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    COMMENT ON TABLE oradba.dp_sql IS 'Defines sql statements without return values
    3/12/13 dcox'
    /
    COMMENT ON COLUMN oradba.dp_sql.dp_task_id IS 'Unique numerical Identifier'
    /
    COMMENT ON COLUMN oradba.dp_sql.ignore_error_yn IS 'Ignore any execution errors for this sql'
    /
    COMMENT ON COLUMN oradba.dp_sql.insert_ts IS 'Insert timestamp'
    /
    COMMENT ON COLUMN oradba.dp_sql.insert_user IS 'Insert userstamp'
    /
    COMMENT ON COLUMN oradba.dp_sql.sql_stmt IS 'SQL Statement'
    /
    COMMENT ON COLUMN oradba.dp_sql.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_sql.update_user IS 'Update Userstamp'
    /
    CREATE TABLE oradba.dp_task
    (
        dp_task_id NUMBER(15, 0) NOT NULL,
        dp_job_id NUMBER(15, 0) NOT NULL,
        task_name VARCHAR2(30 BYTE) NOT NULL,
        task_description VARCHAR2(300 BYTE),
        task_type VARCHAR2(30 BYTE) NOT NULL,
        eff_date_start TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        eff_date_stop TIMESTAMP(6) WITH LOCAL TIME ZONE,
        task_operation VARCHAR2(30 BYTE) NOT NULL,
        insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL,
        monitor_to_completion VARCHAR2(1 BYTE) NOT NULL,
        execution_order NUMBER(*, 0) NOT NULL,
        CONSTRAINT dp_task_pk PRIMARY KEY(dp_task_id) USING INDEX
    )
    SEGMENT CREATION IMMEDIATE
    NOPARALLEL
    LOGGING
    MONITORING
    /
    
    ALTER TABLE oradba.dp_task
    ADD CONSTRAINT dp_task_uk1 UNIQUE (dp_job_id, task_name)
    USING INDEX
    /
    
    ALTER TABLE oradba.dp_task
    ADD CONSTRAINT dp_task_ck3 CHECK (task_type IN ('FULL','SCHEMA','TABLE','LIST','QUERY','SQL')
    )
    /
    
    ALTER TABLE oradba.dp_task
    ADD CONSTRAINT dp_task_ck2 CHECK (task_operation IN ('IMPORT','EXPORT','SQL_FILE','SQL_EXEC')
    )
    /
    
    ALTER TABLE oradba.dp_task
    ADD CONSTRAINT dp_task_ck1 CHECK (eff_date_start < NVL(eff_date_stop,eff_date_start+1)
    )
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_task_bu
        BEFORE UPDATE
        ON oradba.dp_task
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.dp_task_bi
        BEFORE INSERT
        ON oradba.dp_task
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        IF :new.dp_task_id IS NULL
        THEN
            SELECT seq_dp_task.NEXTVAL INTO :new.dp_task_id FROM DUAL;
        END IF;
    
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    COMMENT ON TABLE oradba.dp_task IS 'Lists of tasks and the order in which they should be executed.
    3/12/13 dcox'
    /
    COMMENT ON COLUMN oradba.dp_task.dp_job_id IS 'FK to Job table'
    /
    COMMENT ON COLUMN oradba.dp_task.dp_task_id IS 'Unique numerical identifier'
    /
    COMMENT ON COLUMN oradba.dp_task.eff_date_start IS 'Effective Start Date'
    /
    COMMENT ON COLUMN oradba.dp_task.eff_date_stop IS 'Effective Stop Date'
    /
    COMMENT ON COLUMN oradba.dp_task.execution_order IS 'Lower numbers are first, then second by task_id'
    /
    COMMENT ON COLUMN oradba.dp_task.insert_ts IS 'Insert Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_task.insert_user IS 'Insert Userstamp'
    /
    COMMENT ON COLUMN oradba.dp_task.monitor_to_completion IS 'Y)es or N)o to monitor to completion.'
    /
    COMMENT ON COLUMN oradba.dp_task.task_description IS 'Task Description - Long detailed descripton'
    /
    COMMENT ON COLUMN oradba.dp_task.task_name IS 'Task Name'
    /
    COMMENT ON COLUMN oradba.dp_task.task_operation IS 'TAsk Operation - IMPORT, EXPORT, SQL_FILE, SQL_EXEC'
    /
    COMMENT ON COLUMN oradba.dp_task.task_type IS 'Task Type FULL, SCHEMA, TABLE, LIST OF TABLES, SQL EXEC'
    /
    COMMENT ON COLUMN oradba.dp_task.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.dp_task.update_user IS 'Update Userstamp'
    /
    CREATE TABLE oradba.imp_user_role_cr_pref
    (
        imp_user_role_cr_pref_id NUMBER(15, 0) NOT NULL,
        permission_name VARCHAR2(30 BYTE) NOT NULL,
        user_or_role VARCHAR2(4 BYTE) NOT NULL,
        create_preference_yn VARCHAR2(1 BYTE) NOT NULL,
        insert_ts TIMESTAMP(0) WITH TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL
    )
    SEGMENT CREATION IMMEDIATE
    NOPARALLEL
    LOGGING
    MONITORING
    /
    
    ALTER TABLE oradba.imp_user_role_cr_pref
    ADD CONSTRAINT imp_user_role_or_pref_uk1 UNIQUE (permission_name)
    USING INDEX
    /
    
    ALTER TABLE oradba.imp_user_role_cr_pref
    ADD CONSTRAINT imp_user_role_cr_pref_ck2 CHECK (
    create_preference_yn IN ('Y','N')
    )
    /
    
    ALTER TABLE oradba.imp_user_role_cr_pref
    ADD CONSTRAINT imp_user_role_cr_pref_ck1 CHECK (user_or_role IN ('USER','ROLE')
    )
    /
    
    CREATE OR REPLACE TRIGGER oradba.imp_user_role_cr_pref_bi
        BEFORE INSERT
        ON oradba.imp_user_role_cr_pref
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        SELECT seq_imp_user_role_cr_pref.NEXTVAL INTO :new.imp_user_role_cr_pref_id FROM DUAL;
    
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.imp_user_role_cr_pref_bu
        BEFORE UPDATE
        ON oradba.imp_user_role_cr_pref
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
    
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    COMMENT ON TABLE oradba.imp_user_role_cr_pref IS
        'This table determines preferences on whether a user/role has been created (logged here if added) or is desired to be created (create_preference_yn set to Y/N).
    
    Only create when there is a Y preference here.
    3/12/13 dcox
    
    '
    /
    COMMENT ON COLUMN oradba.imp_user_role_cr_pref.create_preference_yn IS 'Y)es create user (default), N)o don''t create this user'
    /
    COMMENT ON COLUMN oradba.imp_user_role_cr_pref.imp_user_role_cr_pref_id IS 'Unique numerical identifier'
    /
    COMMENT ON COLUMN oradba.imp_user_role_cr_pref.insert_ts IS 'Insert Timestamp'
    /
    COMMENT ON COLUMN oradba.imp_user_role_cr_pref.insert_user IS 'Insert Userstamp'
    /
    COMMENT ON COLUMN oradba.imp_user_role_cr_pref.permission_name IS 'User or Role name'
    /
    COMMENT ON COLUMN oradba.imp_user_role_cr_pref.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.imp_user_role_cr_pref.update_user IS 'Update Userstamp'
    /
    COMMENT ON COLUMN oradba.imp_user_role_cr_pref.user_or_role IS 'User or Role'
    /
    CREATE TABLE oradba.remap_tablespace
    (
        local_tablespace VARCHAR2(30 BYTE) NOT NULL,
        remote_tablespace VARCHAR2(30 BYTE) NOT NULL,
        insert_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL
    )
    SEGMENT CREATION IMMEDIATE
    NOPARALLEL
    LOGGING
    MONITORING
    /
    
    ALTER TABLE oradba.remap_tablespace
    ADD CONSTRAINT rempa_tablespace_uk1 UNIQUE (local_tablespace, remote_tablespace)
    USING INDEX
    /
    
    CREATE OR REPLACE TRIGGER oradba.remap_tablespace_bi
        BEFORE INSERT
        ON oradba.remap_tablespace
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.remap_tablespace_bu
        BEFORE UPDATE
        ON oradba.remap_tablespace
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    COMMENT ON TABLE oradba.remap_tablespace IS 'Cross Reference to remap remote tablespaces to local tablespaces.
    3/12/13 dcox'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.insert_ts IS 'Insert Timestamp'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.insert_user IS 'Insert Userstamp'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.local_tablespace IS 'Local_Tablespace'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.remote_tablespace IS 'Remote Tablespace'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.update_user IS 'Update Userstamp'
    /
    ALTER TABLE oradba.dp_full
    ADD CONSTRAINT dp_full_fk1 FOREIGN KEY (dp_task_id)
    REFERENCES oradba.dp_task (dp_task_id)
    /
    ALTER TABLE oradba.dp_schema
    ADD CONSTRAINT dp_schema_fk1 FOREIGN KEY (dp_task_id)
    REFERENCES oradba.dp_task (dp_task_id)
    /
    ALTER TABLE oradba.dp_sql
    ADD CONSTRAINT dp_sql_fk1 FOREIGN KEY (dp_task_id)
    REFERENCES oradba.dp_task (dp_task_id)
    /
    ALTER TABLE oradba.dp_task
    ADD CONSTRAINT dp_task_fk1 FOREIGN KEY (dp_job_id)
    REFERENCES oradba.dp_job (dp_job_id)
    /
    -- End of DDL script for Foreign Key(s)
    
    -- Added 3/18/13 dcox
    ALTER TABLE ORADBA.DP_JOB 
     MODIFY (
      EFF_DATE_START DEFAULT sysdate
     )
    /
    
    ALTER TABLE ORADBA.DP_SQL 
     MODIFY (
      IGNORE_ERROR_YN DEFAULT 'N'
    
     )
    /
    
    ALTER TABLE ORADBA.IMP_USER_ROLE_CR_PREF 
     MODIFY (
      CREATE_PREFERENCE_YN DEFAULT 'N'
     )
    /
    
    ALTER TABLE ORADBA.DP_TASK 
     MODIFY (
      EFF_DATE_START DEFAULT sysdate,
      MONITOR_TO_COMPLETION DEFAULT 'Y',
      EXECUTION_ORDER DEFAULT 1000
     )
    /
    
    ALTER TABLE ORADBA.DP_SCHEMA 
     MODIFY (
      DEGREE DEFAULT 1
     )
    /
    
    
    

    PL/SQL:

    CREATE OR REPLACE PACKAGE oradba.pkg_dp_exp
    IS
        /*
    
            Purpose: DP Automation Export Functions and Procedures go here
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/28/2013    Initial Build
    
        */
    
        PROCEDURE p_export_full(p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- JOB ID
                                                                            p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                                                                                       p_job_handle IN OUT NUMBER -- Job handle for job created
                                                                                                                                 );
    
        PROCEDURE p_export_schema(p_schema_name   IN     VARCHAR2, -- schema name
                                  p_task_id       IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                  p_remote_link   IN     VARCHAR2 DEFAULT NULL, -- remote database link
                                  p_degree        IN     INTEGER DEFAULT 1, -- degree of parallelism
                                  p_job_name      IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                  p_job_handle    IN OUT NUMBER -- Job handle for job created
                                                               );
    
        PROCEDURE p_export_table(p_schema_name     IN     VARCHAR2, -- schema name
                                 p_table_name      IN     VARCHAR2, -- table name
                                 p_task_id         IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                 p_remote_link     IN     VARCHAR2 DEFAULT NULL, -- remote database link
                                 p_degree          IN     INTEGER DEFAULT 1, -- degree of parallelism
                                 p_dumpfile_name   IN OUT VARCHAR2, -- dumpfile name
                                 p_job_name        IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                 p_job_handle      IN OUT NUMBER -- Job handle for job created
                                                                );
    END; -- Package spec
    /
    
    CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_exp
    IS
        /*
    
            Purpose: DP Automation Export Functions and Procedures go here
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/28/2013    Initial Build
    
        */
    
        -------------------------------------
        PROCEDURE p_export_full(p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                                                            p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                                                                                       p_job_handle IN OUT NUMBER -- Job handle for job created
                                                                                                                                 )
        IS
            /*
    
    
                Purpose: Perform a full database export - starts it - no monitoring
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/6/2013    Initial Build
                dcox        3/1/2013    Adding to blog version and extending
    
            Note: grant execute on sys.dbms_lock to <owner of this package>;
    
            */
            v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
            v_handle NUMBER; -- job handle
            v_current_time DATE := SYSDATE;
            v_current_scn v$database.current_scn%TYPE; -- current scn
            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_start_time DATE; -- start time
            v_end_time DATE; -- end time
            v_elapsed_days NUMBER; -- elapsed days and fraction of days
            v_elapsed_hours NUMBER; -- total hours (used for intermediate calc with minutes also)
            v_elapsed_minutes NUMBER; -- minus (minus hours)
            v_line_no INTEGER := 0; -- debug line no
            v_job_state VARCHAR2(32767); -- Job state of export
            v_job_status ku$_status1010; -- See sys type ku$_Status1010
            vc_sleep_time INTEGER := 10; -- seconds to sleep between testing for status
            v_sqlcode NUMBER; -- sqlcode
            v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
            v_return NUMBER; -- function return code
            vc_local_fn VARCHAR2(30) := 'p_export_full'; -- local function or procedure name
        BEGIN
            v_line_no := 100; -- debug line no
    
            -- create log and dump file names
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.dmp';
            ELSE
                v_logfile_name :=
                    'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
                v_dumpfile_name :=
                    'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.dmp';
            END IF;
    
            v_line_no := 150; -- debug line no
    
            -- Open the job
            BEGIN
                v_line_no := 160; -- debug line no
                p_job_name := 'EXP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
                v_line_no := 170; -- debug line no
                DBMS_OUTPUT.put_line(SUBSTR('Value of p_job_name=' || TO_CHAR(p_job_name), 1, 255));
                v_handle :=
                    DBMS_DATAPUMP.open(operation => 'EXPORT',
                                       job_mode => 'FULL',
                                       job_name => p_job_name,
                                       version => v_compatible,
                                       compression => DBMS_DATAPUMP.ku$_compress_metadata);
                p_job_handle := v_handle; -- return value for handle
                -- Set the degree
                DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
            EXCEPTION
                WHEN OTHERS
                THEN
                    DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open db_task_id:' || RTRIM(p_task_id, 10), 1, 255));
                    RAISE;
            END;
    
            v_line_no := 200; -- debug line no
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
            -- Set the degree
            DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
            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 => 'dp' || '_%U_' || v_dumpfile_name,
                                   directory => v_default_dir,
                                   filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
            v_line_no := 500; -- debug line no
    
            -- consistent = Y equivalent
            SELECT current_scn INTO v_current_scn FROM v$database;
    
            DBMS_DATAPUMP.set_parameter(handle => v_handle, name => 'FLASHBACK_SCN', VALUE => v_current_scn);
    
            v_line_no := 600; -- debug line no
            -- Get the start time
            v_start_time := SYSDATE;
            -- Add a start time to the log file
            DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
    
            v_line_no := 700; -- debug line no
            -- Start the job
            DBMS_DATAPUMP.start_job(handle => v_handle);
            DBMS_DATAPUMP.detach(handle => v_handle);
            v_line_no := 800; -- debug line no
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
                                                  p_message_text => SQLERRM(v_sqlcode),
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_task_id => p_task_id);
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
                DBMS_DATAPUMP.detach(handle => v_handle);
    
                -- stop job if it fails
                BEGIN
                    DBMS_DATAPUMP.stop_job(handle => v_handle,
                                           immediate => 0,
                                           keep_master => NULL,
                                           delay => 0);
                EXCEPTION
                    WHEN OTHERS
                    THEN
                        NULL;
                END;
    
                RAISE;
        END p_export_full; -- procedure p_export_full
    
        ---------------------
    
        PROCEDURE p_export_schema(p_schema_name   IN     VARCHAR2, -- schema name
                                  p_task_id       IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                  p_remote_link   IN     VARCHAR2 DEFAULT NULL, -- remote database link
                                  p_degree        IN     INTEGER DEFAULT 1, -- degree of parallelism
                                  p_job_name      IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                  p_job_handle    IN OUT NUMBER -- Job handle for job created
                                                               )
        IS
            /*
    
    
                Purpose: Export schema
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/15/2013   Initial Build
                dcox        3/1/2013    Modified from blog example
    
            */
            v_sid VARCHAR2(200) := oradba.pkg_dp_util.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; -- degree of parallism
            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(200) := 'SCHEMA'; -- Job mode
            v_filter_name VARCHAR2(30); -- filter name
            v_filter_value NUMBER; -- filter value
            vc_local_fn CONSTANT VARCHAR2(30) := 'p_export_schema'; -- local function or procedure
            v_return NUMBER; -- return error code
        BEGIN
            v_line_no := 100; -- debug line no
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Started' || ' ' || $$plsql_unit || '.' || vc_local_fn,
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
    
            -- Create the log and dumpfile names
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.dmp';
            ELSE
                v_logfile_name :=
                    'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
                v_dumpfile_name :=
                    'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.dmp';
            END IF;
    
            -- Open the job
            BEGIN
                p_job_name := 'EXP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
                v_handle :=
                    DBMS_DATAPUMP.open(operation => 'EXPORT',
                                       job_mode => vc_job_mode,
                                       remote_link => p_remote_link,
                                       job_name => p_job_name,
                                       version => v_compatible);
                p_job_handle := v_handle;
                -- Set the degree
                DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
            EXCEPTION
                WHEN OTHERS
                THEN
                    DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
                    RAISE;
            END;
    
            v_line_no := 200; -- debug line no
    
            -- Set the degree
            IF p_degree IS NULL OR p_degree < 0 OR p_degree > 32
            THEN
                v_degree := 1;
            ELSE
                v_degree := p_degree;
            END IF;
    
            DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
    
            -- 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 => 'dp' || '_%U_' || v_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 := 600; -- debug line no
    
            -- Get the start time
            v_start_time := SYSDATE;
    
            v_line_no := 700; -- debug line no
    
            -- Add a start time to the log file
            DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
    
            v_line_no := 710; -- debug line no
            -- Start the job
            DBMS_DATAPUMP.start_job(handle => v_handle);
            DBMS_DATAPUMP.detach(handle => v_handle);
            v_line_no := 800; -- debug line no
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Completed' || ' ' || $$plsql_unit || '.' || vc_local_fn,
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
                                                  p_message_text => SQLERRM(v_sqlcode),
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_task_id => p_task_id);
                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_export_schema; -- Procedure p_export_schema
    
        --------------------------------------
    
        PROCEDURE p_export_table(p_schema_name     IN     VARCHAR2, -- schema name
                                 p_table_name      IN     VARCHAR2, -- table name
                                 p_task_id         IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                 p_remote_link     IN     VARCHAR2 DEFAULT NULL, -- remote database link
                                 p_degree          IN     INTEGER DEFAULT 1, -- degree of parallelism
                                 p_dumpfile_name   IN OUT VARCHAR2, -- dumpfile name
                                 p_job_name        IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                 p_job_handle      IN OUT NUMBER -- Job handle for job created
                                                                )
        IS
            /*
    
    
                Purpose: Export a table
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        3/4/2012    Initial Build
    
            */
            v_sid VARCHAR2(200) := oradba.pkg_dp_util.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) := 'TABLE'; -- Job mode
            vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
            v_return NUMBER; -- function return code
            vc_local_fn CONSTANT VARCHAR2(30) := 'p_export_table'; -- local function or procedure name
        BEGIN
            v_line_no := 100; -- debug line no
    
            -- Create the log and dumpfile names
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.dmp';
            ELSE
                v_logfile_name :=
                    'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
                v_dumpfile_name :=
                    'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.dmp';
            END IF;
    
            -- Populate dumpfilename with composed file_name - unless name is provided then set in variable
            IF p_dumpfile_name IS NULL
            THEN
                p_dumpfile_name := v_dumpfile_name;
            ELSE
                v_dumpfile_name := p_dumpfile_name;
            END IF;
    
            v_line_no := 200; -- debug line no
    
            -- Open the job
            BEGIN
                p_job_name := 'EXP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
                v_handle :=
                    DBMS_DATAPUMP.open(operation => 'EXPORT',
                                       job_mode => vc_job_mode,
                                       remote_link => p_remote_link,
                                       job_name => p_job_name,
                                       version => v_compatible);
                p_job_handle := v_handle;
                -- Set the degree
                DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
            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 := 600; -- debug line no
            -- Filter for table
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_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
                v_sqlcode := SQLCODE;
                -- Log error
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
                                                  p_message_text => SQLERRM(v_sqlcode),
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_task_id => p_task_id);
                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_export_table; -- Procedure p_export_table
    END pkg_dp_exp; -- package pkg_dp_exp
    /
    
    CREATE OR REPLACE PACKAGE oradba.pkg_dp_imp
    IS
        /*
    
            Purpose: Import procedures and functions
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        3/4/2013    Initial Build
    
        */
    
        PROCEDURE p_imp_schema(p_task_id         IN     NUMBER, -- task id to be processed
                               p_schema_name     IN     VARCHAR2, -- schema to load
                               p_dumpfile_name   IN     VARCHAR2, -- dumpfile_name to load
                               p_job_name        IN OUT VARCHAR2, -- job name for this import
                               p_job_handle      IN OUT NUMBER -- Job handle for job created
                                                              );
    
        PROCEDURE p_imp_schema_vialink(p_task_id       IN     NUMBER, -- task id to be processed
                                       p_schema_name   IN     VARCHAR2, -- schema to load
                                       p_remote_link   IN     VARCHAR2, -- DB Link to source database
                                       p_job_name      IN OUT VARCHAR2, -- job name for this import
                                       p_job_handle    IN OUT NUMBER -- Job handle for job created
                                                                    );
        PROCEDURE p_imp_table_vialink(p_schema_name   IN     VARCHAR2, -- schema for table
                                      p_table_name    IN     VARCHAR2, --  table
                                      p_task_id       IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                      p_remote_link   IN     VARCHAR2 DEFAULT NULL, -- remote database link
                                      p_degree        IN     INTEGER DEFAULT 1, -- degree of parallelism
                                      p_job_name      IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                      p_job_handle    IN OUT NUMBER -- Job handle for job created
                                                                   );
    
        PROCEDURE p_imp_table(p_schema_name     IN     VARCHAR2, -- schema for talbe
                              p_table_name      IN     VARCHAR2, -- table to load
                              p_dumpfile_name   IN     VARCHAR2, -- dumpfile_name to load
                              p_task_id         IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                              p_degree          IN     INTEGER DEFAULT 1, -- degree of parallelism
                              p_job_name        IN OUT VARCHAR2, -- Job name assigned to this datapump job
                              p_job_handle      IN OUT NUMBER -- Job handle for job created
                                                             );
    
        PROCEDURE p_imp_tab_list_vialink(p_schema_name   IN     VARCHAR2, -- schema for table
                                         p_task_id       IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                         p_expr_list     IN     VARCHAR2, -- expression list
                                         p_remote_link   IN     VARCHAR2 DEFAULT NULL, -- remote database link
                                         p_degree        IN     INTEGER DEFAULT 1, -- degree of parallelism
                                         p_job_name      IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                         p_job_handle    IN OUT NUMBER -- Job handle for job created
                                                                      );
    END pkg_dp_imp; -- Package spec pkg_dp_imp
    /
    
    CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_imp
    IS
        /*
    
            Purpose: Import procedures and functions
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        3/4/2013    Initial Build
    
        */
    
        PROCEDURE p_imp_schema(p_task_id         IN     NUMBER, -- task id to be processed
                               p_schema_name     IN     VARCHAR2, -- schema to load
                               p_dumpfile_name   IN     VARCHAR2, -- dumpfile_name to load
                               p_job_name        IN OUT VARCHAR2, -- job name for this import
                               p_job_handle      IN OUT NUMBER -- Job handle for job created
                                                              )
        IS
            /*
    
    
                Purpose: Import a schema
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/20/2013   Initial Build
                dcox        3/4/2013    Modified for DP example
    
            */
            v_sid VARCHAR2(200) := oradba.pkg_dp_util.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
            /*not used here, can turn on later if needed needs more code below
            v_degree INTEGER := 1; -- degree of parallelism */
            v_line_no INTEGER := 0; -- debug line no
            v_sqlcode NUMBER; -- sqlcode
            v_degree INTEGER := 1;
            v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
            vc_job_mode CONSTANT VARCHAR2(30) := 'SCHEMA'; -- Job mode
            vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
            v_return NUMBER; -- function return variable
            vc_local_fn CONSTANT VARCHAR2(30) := 'p_import_schema';
        BEGIN
            v_line_no := 100; -- debug line no
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Schema Task Import Started.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
    
            -- 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') || '_T' || p_task_id || '.log';
            ELSE
                v_logfile_name :=
                       vc_operation
                    || '_'
                    || v_sid
                    || '_'
                    || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI')
                    || '_'
                    || v_compatible
                    || '_T'
                    || p_task_id
                    || '.log';
            END IF;
    
            v_line_no := 200; -- debug line no
    
            -- Open the job
            BEGIN
                p_job_name := 'IMP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
                v_handle :=
                    DBMS_DATAPUMP.open(operation => vc_operation,
                                       job_mode => vc_job_mode,
                                       job_name => p_job_name,
                                       version => v_compatible);
                p_job_handle := v_handle; -- Set the return value
                -- Set the degree
                DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
            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
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Schema Task Import Completed.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
        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
    
        ---------------------
    
        PROCEDURE p_imp_schema_vialink(p_task_id       IN     NUMBER, -- task id to be processed
                                       p_schema_name   IN     VARCHAR2, -- schema to load
                                       p_remote_link   IN     VARCHAR2, -- DB Link to source database
                                       p_job_name      IN OUT VARCHAR2, -- job name for this import
                                       p_job_handle    IN OUT NUMBER -- Job handle for job created
                                                                    )
        IS
            /*
    
    
                Purpose: Import a schema
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/20/2013   Initial Build
                dcox        3/4/2013    Modified to be use in dp example
    
            */
            v_sid VARCHAR2(200) := oradba.pkg_dp_util.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
            v_message VARCHAR2(4000); -- error message to be raised
            v_return NUMBER; -- Return error or success code
            vc_local_fn CONSTANT VARCHAR2(30) := 'p_imp_schema_vialink';
        BEGIN
            v_line_no := 100; -- debug line no
            DBMS_OUTPUT.put_line(SUBSTR(TO_CHAR('Schema Task with DB Link Started.  '), 1, 255));
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Schema Task Import with DB Link Started.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
            DBMS_OUTPUT.put_line(SUBSTR('1 v_return=' || TO_CHAR(v_return) || ' for ' || vc_local_fn, 1, 255));
            -- verify all accouts are created before bringing over schema
            v_return := oradba.pkg_dp_util.fn_get_rmt_user_n_role(p_remote_link => p_remote_link, p_schema_name => p_schema_name);
    
            -- Check for errors
            IF v_return < 0
            THEN
                v_message := 'Function oradba.pkg_dp_util.fn_get_rmt_user_n_role failed to get/create the missing users and roles';
                raise_application_error(-20001, v_message);
            END IF;
    
            -- 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') || '_T' || p_task_id || '.log';
            ELSE
                v_logfile_name :=
                       vc_operation
                    || '_'
                    || v_sid
                    || '_'
                    || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI')
                    || '_'
                    || v_compatible
                    || '_T'
                    || p_task_id
                    || '.log';
            END IF;
    
            v_line_no := 200; -- debug line no
    
            -- Open the job
            BEGIN
                p_job_name := 'IMP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
                v_handle :=
                    DBMS_DATAPUMP.open(operation => vc_operation,
                                       job_mode => vc_job_mode,
                                       remote_link => p_remote_link,
                                       job_name => p_job_name,
                                       version => v_compatible);
                p_job_handle := v_handle;
            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 := 500; -- debug line no
            -- Filter for schema
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
            v_line_no := 600; -- debug line no
    
            -- Remap tablespace if needed - using function
            v_return := oradba.pkg_dp_util.fn_tbs_remaps_for_link(p_remote_link => p_remote_link, p_handle => v_handle, p_schema_name => p_schema_name);
    
            -- check for errors
            IF v_return < 0
            THEN
                v_message :=
                       'Tablespace Remaps (oradba.pkg_dp_util.fn_tbs_remaps_for_link) failed for remote link: '
                    || p_remote_link
                    || ' Job Name:'
                    || p_job_name;
                raise_application_error(-20001, v_message);
            END IF;
    
            -- Get the start time
            v_start_time := SYSDATE;
            -- Add a start time to the log file
            DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
    
            v_line_no := 710; -- debug line no
            -- Start the job
            DBMS_DATAPUMP.start_job(handle => v_handle);
            v_line_no := 720; -- debug line no
    
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Schema Task Import with DB Link Completed.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
            DBMS_OUTPUT.put_line(SUBSTR('2 v_return=' || TO_CHAR(v_return) || ' for ' || vc_local_fn, 1, 255));
            v_line_no := 900; -- debug line no
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
                                                  p_message_text => SQLERRM(v_sqlcode),
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_task_id => p_task_id);
                -- detach just in case this was not reached in the code
                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_vialink; -- procedure p_imp_schema_vialink
    
        ---------------------
    
        PROCEDURE p_imp_table_vialink(p_schema_name   IN     VARCHAR2, -- schema for table
                                      p_table_name    IN     VARCHAR2, --  table
                                      p_task_id       IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                      p_remote_link   IN     VARCHAR2 DEFAULT NULL, -- remote database link
                                      p_degree        IN     INTEGER DEFAULT 1, -- degree of parallelism
                                      p_job_name      IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                      p_job_handle    IN OUT NUMBER -- Job handle for job created
                                                                   )
        IS
            /*
    
    
                     Purpose: Import a table - This version automatically remaps the tablespaces
                     based on either a default value or a mapping in a table (see fn_tbs_remaps_for_link for details)
    
                     MODIFICATION HISTORY
                     Person      Date        Comments
                     ---------   ------      -------------------------------------------
                     dcox        2/20/2012    Initial Build
    
                 */
            v_sid VARCHAR2(200) := oradba.pkg_dp_util.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) := 'TABLE'; -- Job mode
            vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
            v_message VARCHAR2(4000); -- error message to be raised
            v_return NUMBER; -- function return error code
            vc_local_fn CONSTANT VARCHAR2(30) := 'p_imp_table_vialink'; -- local function or procedure name
        BEGIN
            v_line_no := 100; -- debug line no
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Schema Task Import Table w/link Started.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
    
            -- Check parameters - p_schema_name
            IF p_schema_name IS NULL
            THEN
                v_message := 'p_schema_name is null, this is needed to make this work.';
                raise_application_error(-20001, v_message);
            END IF;
    
            -- Check parameters - p_table_name
            IF p_table_name IS NULL
            THEN
                v_message := 'p_table_name is null, this is needed to make this work.';
                raise_application_error(-20001, v_message);
            END IF;
    
            -- Check parameters - p_remote_link
            IF p_remote_link IS NULL
            THEN
                v_message := 'p_remote_link is null, this is needed to make this work.';
                raise_application_error(-20001, v_message);
            END IF;
    
            -- verify all accouts are created before bringing over schema/table
            v_return :=
                oradba.pkg_dp_util.fn_get_rmt_user_n_role(p_remote_link => p_remote_link, p_schema_name => p_schema_name, p_table_name => p_table_name);
    
            -- Check for errors
            IF v_return < 0
            THEN
                v_message := 'Function oradba.pkg_dp_util.fn_get_rmt_user_n_role failed to get/create the missing users and roles';
                raise_application_error(-20001, v_message);
            END IF;
    
            -- Create the log and dumpfile names
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
    
            ELSE
                v_logfile_name :=
                    'impdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
    
            END IF;
    
            v_line_no := 200; -- debug line no
    
            -- Open the job
            p_job_name := 'impdp_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
            DBMS_OUTPUT.put_line(SUBSTR('Value of vc_operation=' || TO_CHAR(vc_operation), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of vc_job_mode=' || TO_CHAR(vc_job_mode), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of p_remote_link=' || TO_CHAR(p_remote_link), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of p_job_name=' || TO_CHAR(p_job_name), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_compatible=' || TO_CHAR(v_compatible), 1, 255));
    
            v_line_no := 210; -- debug line no
            BEGIN
                v_handle :=
                    DBMS_DATAPUMP.open(operation => vc_operation,
                                       job_mode => vc_job_mode,
                                       remote_link => p_remote_link,
                                       job_name => p_job_name,
                                       version => v_compatible);
                v_line_no := 220; -- debug line no
                -- for output variables
                p_job_handle := v_handle;
            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 := 500; -- debug line no
            -- Filter for schema
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
            v_line_no := 600; -- debug line no
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
            v_line_no := 650; -- debug line no
            -- Remap tablespace if needed - using function
            v_return := oradba.pkg_dp_util.fn_tbs_remaps_for_link(p_remote_link => p_remote_link, p_handle => v_handle, p_schema_name => p_schema_name);
    
            -- check for errors
            IF v_return < 0
            THEN
                v_message := 'Tablespace Remaps failed for remote link: ' || p_remote_link || ' Job Name:' || p_job_name;
                raise_application_error(-20001, v_message);
            END IF;
    
            v_line_no := 710; -- debug line no
            -- Start the job
            DBMS_DATAPUMP.start_job(handle => v_handle);
            v_line_no := 720; -- debug line no
    
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Schema Task Import Table w/link Completed.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                DBMS_OUTPUT.put_line(SUBSTR('Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
                                                  p_message_text => SQLERRM(v_sqlcode),
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_task_id => p_task_id);
                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_table_vialink; -- procedure p_imp_table_vialink
    
        --------------------------------------
    
        PROCEDURE p_imp_table(p_schema_name     IN     VARCHAR2, -- schema for talbe
                              p_table_name      IN     VARCHAR2, -- table to load
                              p_dumpfile_name   IN     VARCHAR2, -- dumpfile_name to load
                              p_task_id         IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                              p_degree          IN     INTEGER DEFAULT 1, -- degree of parallelism
                              p_job_name        IN OUT VARCHAR2, -- Job name assigned to this datapump job
                              p_job_handle      IN OUT NUMBER -- Job handle for job created
                                                             )
        IS
            /*
    
                Purpose: Import a table
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/20/2012    Initial Build
    
            */
            v_sid VARCHAR2(200) := oradba.pkg_dp_util.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) := 'TABLE'; -- Job mode
            vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
            v_return NUMBER; -- return error code
            vc_local_fn CONSTANT VARCHAR(30) := 'p_imp_table'; -- local function or procedure name
        BEGIN
            v_line_no := 100; -- debug line no
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Schema Task Import Table Started.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
    
            -- Create the log and dumpfile names
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
            ELSE
                v_logfile_name :=
                    'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
            END IF;
    
            v_line_no := 200; -- debug line no
            -- Open the job
            p_job_name := 'impdp_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
            -- Open the job
            BEGIN
                v_handle :=
                    DBMS_DATAPUMP.open(operation => vc_operation,
                                       job_mode => vc_job_mode,
                                       job_name => p_job_name,
                                       version => v_compatible);
    
                -- for output variables
                p_job_handle := v_handle;
            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 := 600; -- debug line no
            -- Filter for table
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_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
    
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Schema Task Import Table Completed.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
                                                  p_message_text => SQLERRM(v_sqlcode),
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_task_id => p_task_id);
                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_table; -- Procedure p_imp_table
    
        ---------------------
    
        PROCEDURE p_imp_tab_list_vialink(p_schema_name   IN     VARCHAR2, -- schema for table
                                         p_task_id       IN     oradba.dp_full.dp_task_id%TYPE, -- Task ID
                                         p_expr_list     IN     VARCHAR2, -- expression list
                                         p_remote_link   IN     VARCHAR2 DEFAULT NULL, -- remote database link
                                         p_degree        IN     INTEGER DEFAULT 1, -- degree of parallelism
                                         p_job_name      IN OUT VARCHAR2, -- Job name assigned to this datapump job
                                         p_job_handle    IN OUT NUMBER -- Job handle for job created
                                                                      )
        IS
            /*
    
    
                     Purpose: Import a list of tables from a "table list" - This version automatically remaps the tablespaces
                     based on either a default value or a mapping in a table (see fn_tbs_remaps_for_link for details)
                     A table List will include a list of tables to be imported and will be created as an expression here
    
                     Unlike some of the other procedures in this package, this one assumes that the target users are created.
    
    
                     MODIFICATION HISTORY
                     Person      Date        Comments
                     ---------   ------      -------------------------------------------
                     dcox        2/20/2012    Initial Build
                     dcox        3/6/2012    Copied from other examples
    
                 */
            v_sid VARCHAR2(200) := oradba.pkg_dp_util.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) := 'TABLE'; -- Job mode
            vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
            v_message VARCHAR2(4000); -- error message to be raised
            v_return NUMBER; -- function return error code
            vc_local_fn CONSTANT VARCHAR2(30) := 'p_imp_tab_list_vialink'; -- local function or procedure name
        BEGIN
            v_line_no := 100; -- debug line no
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Task Import Table w/EXPR_LIST Started.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
    
            -- Check parameters - p_schema_name
            IF p_schema_name IS NULL
            THEN
                v_message := 'p_schema_name is null, this is needed to make this work.';
                raise_application_error(-20001, v_message);
            END IF;
    
            -- Check parameters - p_remote_link
            IF p_remote_link IS NULL
            THEN
                v_message := 'p_remote_link is null, this is needed to make this work.';
                raise_application_error(-20001, v_message);
            END IF;
    
            -- Create the log and dumpfile names
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'impdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
    
            ELSE
                v_logfile_name :=
                    'impdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
    
            END IF;
    
            v_line_no := 200; -- debug line no
            -- verify all accouts are created before bringing over schema
            v_return := oradba.pkg_dp_util.fn_get_rmt_user_n_role(p_remote_link => p_remote_link, p_schema_name => p_schema_name);
    
            -- Check for errors
            IF v_return < 0
            THEN
                v_message := 'Function oradba.pkg_dp_util.fn_get_rmt_user_n_role failed to get/create the missing users and roles';
                raise_application_error(-20001, v_message);
            END IF;
    
            -- Open the job
            p_job_name := 'impdp_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
            DBMS_OUTPUT.put_line(SUBSTR('Value of vc_operation=' || TO_CHAR(vc_operation), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of vc_job_mode=' || TO_CHAR(vc_job_mode), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of p_remote_link=' || TO_CHAR(p_remote_link), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of p_job_name=' || TO_CHAR(p_job_name), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_compatible=' || TO_CHAR(v_compatible), 1, 255));
    
            v_line_no := 210; -- debug line no
            BEGIN
                v_handle :=
                    DBMS_DATAPUMP.open(operation => vc_operation,
                                       job_mode => vc_job_mode,
                                       remote_link => p_remote_link,
                                       job_name => p_job_name,
                                       version => v_compatible);
                v_line_no := 220; -- debug line no
                -- for output variables
                p_job_handle := v_handle;
            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 := 500; -- debug line no
            -- Filter for schema
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
            v_line_no := 600; -- debug line no
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_EXPR', VALUE => p_expr_list);
            v_line_no := 650; -- debug line no
            -- Remap tablespace if needed - using function
            v_return := oradba.pkg_dp_util.fn_tbs_remaps_for_link(p_remote_link => p_remote_link, p_handle => v_handle, p_schema_name => p_schema_name);
    
            -- check for errors
            IF v_return < 0
            THEN
                v_message := 'Tablespace Remaps failed for remote link: ' || p_remote_link || ' Job Name:' || p_job_name;
                raise_application_error(-20001, v_message);
            END IF;
    
            v_line_no := 710; -- debug line no
            -- Start the job
            DBMS_DATAPUMP.start_job(handle => v_handle);
            v_line_no := 720; -- debug line no
    
            v_return :=
                oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                              p_message_text => 'Task Import Table w/EXPR_LIST Completed.  ',
                                              p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                              p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                              p_line_no => v_line_no,
                                              p_task_id => p_task_id);
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                DBMS_OUTPUT.put_line(SUBSTR('Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
                                                  p_message_text => SQLERRM(v_sqlcode),
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_task_id => p_task_id);
                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_tab_list_vialink; -- procedure p_imp_tab_list_vialink
    END pkg_dp_imp; -- package body pkg_dp_imp
    /
    
    CREATE OR REPLACE PACKAGE oradba.pkg_dp_main
    IS
        /*
    
            Purpose:
            pkg_dp_main:    Control of DP automation jobs and tasks go here
            pkg_dp_imp:     Import funs, procs, etc.
            pkg_dp_exp:     Export funs, procs, etc.
            pkg_dp_util:    Utility funs, procs, etc. / Helper funs, procs, etc.
            pkg_dp_monitor: Monitoring Tools funs, procs, etc.
            pkg_dp_sql:     Sql Execution funs, procs, etc.
    
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/28/2013    Initial Build
    
        */
    
        PROCEDURE p_start_job(p_job_id IN oradba.dp_job.dp_job_id%TYPE, -- Job to be started
                                                                       p_completion_code IN OUT INTEGER, -- numerical code to be primary evaluation return variable
                                                                                                        p_complete_status IN OUT VARCHAR2 -- Description of status being returned numerically (don't use this for evaluation of success)
                                                                                                                                         );
    END; -- Package spec
    /
    
    CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_main
    IS
        /*
    
            Purpose:
            pkg_dp_main:    Control of DP automation jobs and tasks go here
            pkg_dp_imp:     Import funs, procs, etc.
            pkg_dp_exp:     Export funs, procs, etc.
            pkg_dp_util:    Utility funs, procs, etc. / Helper funs, procs, etc.
            pkg_dp_monitor: Monitoring Tools funs, procs, etc.
            pkg_dp_sql:     Sql Execution funs, procs, etc.
    
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/28/2013    Initial Build
    
        */
    
        PROCEDURE p_start_job(p_job_id IN oradba.dp_job.dp_job_id%TYPE, -- Job to be started
                                                                       p_completion_code IN OUT INTEGER, -- numerical code to be primary evaluation return variable
                                                                                                        p_complete_status IN OUT VARCHAR2 -- Description of status being returned numerically (don't use this for evaluation of success)
                                                                                                                                         )
        IS
            /*
    
    
                Purpose: This procedure starts jobs, gets the tasks to be executed, initiates tasks
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/28/2013    Initial Build
    
            */
            -- get the job info
            CURSOR c_get_job(cv_dp_job_id IN oradba.dp_job.dp_job_id%TYPE)
            IS
                SELECT *
                  FROM oradba.dp_job
                 WHERE dp_job_id = cv_dp_job_id;
    
            vrec_job c_get_job%ROWTYPE; -- job record
    
            -- get the task info
            CURSOR c_get_tasks(cv_dp_job_id IN oradba.dp_task.dp_job_id%TYPE)
            IS
                  SELECT *
                    FROM oradba.dp_task
                   WHERE dp_job_id = cv_dp_job_id
                ORDER BY execution_order;
    
            -- get the full info
            CURSOR c_get_full(cv_dp_task_id IN oradba.dp_full.dp_task_id%TYPE)
            IS
                SELECT *
                  FROM oradba.dp_full
                 WHERE dp_task_id = cv_dp_task_id;
    
            vrec_full c_get_full%ROWTYPE; -- record type for full
    
            -- get the full info
            CURSOR c_get_schema(cv_dp_task_id IN oradba.dp_full.dp_task_id%TYPE)
            IS
                SELECT *
                  FROM oradba.dp_schema
                 WHERE dp_task_id = cv_dp_task_id;
    
            vrec_schema c_get_schema%ROWTYPE;
    
            v_monitor_interval CONSTANT INTEGER := 1; -- default monitor interval in seconds
            v_job_name VARCHAR2(30); -- job name for process created
            v_job_handle NUMBER; -- job handle for process created
            v_current_schema VARCHAR2(30); -- current schema being executed
            v_percent_complete INTEGER; -- current percent complete
            v_line_no INTEGER := 0; -- debug line no
            v_sqlcode NUMBER; -- sqlcode return value
            v_message VARCHAR2(32767); -- error message
            vc_local_fn CONSTANT VARCHAR2(30) := 'p_start_job'; -- local procedure or function name
            v_return NUMBER; -- return value for functions
            v_task_id oradba.dp_task.dp_task_id%TYPE; -- type id
            v_job_state VARCHAR2(32767); -- job state of datapump job
        BEGIN
            v_line_no := 10100; -- debug line no
    
            -- Get job info
            OPEN c_get_job(p_job_id);
            FETCH c_get_job INTO vrec_job;
            CLOSE c_get_job;
    
            -- check for successful find
            IF vrec_job.dp_job_id IS NULL
            THEN
                v_message := 'Could not find job id: ' || vrec_job.dp_job_id;
                raise_application_error(-20001, v_message);
            END IF;
    
            v_return :=
                oradba.pkg_dp_util.fn_logging(
                    p_code => oradba.pkg_dp_util.logging_informational,
                    p_message_text => 'Job ' || vrec_schema.dp_task_id || ' ' || NVL(vrec_job.job_description, vrec_job.job_name) || ' started.',
                    p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                    p_object_name => $$plsql_unit || '.' || vc_local_fn,
                    p_line_no => v_line_no,
                    p_job_id => vrec_job.dp_job_id);
    
            v_line_no := 10200; -- debug line no
    
            -- Get Task Info
            FOR ntasks IN c_get_tasks(p_job_id)
            LOOP
                v_line_no := 10300; -- debug line no
                v_task_id := ntasks.dp_task_id; -- task id for error collection only
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_task_id=' || TO_CHAR(v_task_id), 1, 255));
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
                                                  p_message_text => 'Task ' || NVL(ntasks.task_description, ntasks.task_name) || ' started.  ',
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_job_id => vrec_job.dp_job_id,
                                                  p_task_id => ntasks.dp_task_id);
                v_line_no := 10350; -- debug line no
    
                -- Execute tasks with correct package
    
                -- Check for Export or Import
                IF ntasks.task_operation = 'EXPORT'
                THEN
                    v_line_no := 10400; -- debug line no
    
                    IF ntasks.task_type = 'FULL'
                    THEN
    
                        v_line_no := 10410; -- debug line no
                        OPEN c_get_full(ntasks.dp_task_id);
                        FETCH c_get_full INTO vrec_full;
                        CLOSE c_get_full;
    
                        oradba.pkg_dp_exp.p_export_full(ntasks.dp_task_id, v_job_name, v_job_handle);
                    ELSIF ntasks.task_type = 'SCHEMA'
                    THEN
                        v_line_no := 10420; -- debug line no
                        OPEN c_get_schema(ntasks.dp_task_id);
                        FETCH c_get_schema INTO vrec_schema;
                        CLOSE c_get_schema;
    
                        v_line_no := 10430; -- debug line no
    
                        -- Run the schema export
                        oradba.pkg_dp_exp.p_export_schema(vrec_schema.schema_name, -- p_schema_name
                                                          ntasks.dp_task_id, -- p_task_id
                                                          vrec_schema.remote_link, -- p_remote_link
                                                          vrec_schema.degree, -- degree parallel
                                                          v_job_name, -- job name returned
                                                          v_job_handle); -- job handle returned
    
                    END IF; -- end of check for type of export  - schema - full
    
                -- Check for import
                ELSIF ntasks.task_operation = 'IMPORT'
                THEN
                    v_line_no := 10440; -- debug line no
    
                    -- Check for schema
                    IF ntasks.task_type IN ('SCHEMA', 'TABLE')
                    THEN
                        v_line_no := 10450; -- debug line no
                        OPEN c_get_schema(ntasks.dp_task_id);
                        FETCH c_get_schema INTO vrec_schema;
                        CLOSE c_get_schema;
    
                        -- check for schema only
                        IF vrec_schema.table_name IS NULL AND vrec_schema.table_subquery IS NULL AND vrec_schema.expr_list IS NULL
                        THEN
    
                            -- is over DB Link
                            IF vrec_schema.remote_link IS NOT NULL
                            THEN
                                v_line_no := 10470; -- debug line no
                                oradba.pkg_dp_imp.p_imp_schema_vialink(p_task_id => vrec_schema.dp_task_id, -- task id
                                                                       p_schema_name => vrec_schema.schema_name, -- schema name
                                                                       p_remote_link => vrec_schema.remote_link, -- remote database link
                                                                       p_job_name => v_job_name, -- job name for this import
                                                                       p_job_handle => v_job_handle); -- job handle
                            -- Else use version with dumpfile name
                            -- not yet coded - dumpfile version goes here xxxxxxxxxxxxxxxx
                            END IF; -- end check for remote link or local file
    
                        ELSIF vrec_schema.table_name IS NOT NULL
                        THEN
    
                            -- is over DB Link
                            IF vrec_schema.remote_link IS NOT NULL
                            THEN
                                v_line_no := 10480; -- debug line no
    
                                oradba.pkg_dp_imp.p_imp_table_vialink(p_schema_name => vrec_schema.schema_name, -- schema name
                                                                      p_table_name => vrec_schema.table_name, -- table name
                                                                      p_task_id => vrec_schema.dp_task_id, -- task id
                                                                      p_remote_link => vrec_schema.remote_link, -- remote database link
                                                                      p_degree => vrec_schema.degree, -- degree parallel
                                                                      p_job_name => v_job_name, -- job name for this import
                                                                      p_job_handle => v_job_handle); -- job handle
                            -- Else use version with dumpfile name
                            -- not yet coded - dumpfile version goes here xxxxxxxxxxxxxxxx
                            END IF; -- end check for remote link or local file
    
                        ELSIF vrec_schema.expr_list IS NOT NULL
                        THEN
    
                            -- is over DB Link
                            IF vrec_schema.remote_link IS NOT NULL
                            THEN
                                v_line_no := 10490; -- debug line no
                                oradba.pkg_dp_imp.p_imp_tab_list_vialink(p_schema_name => vrec_schema.schema_name, -- schema name
                                                                         p_task_id => vrec_schema.dp_task_id, -- task id
                                                                         p_expr_list => vrec_schema.expr_list, -- expression list
                                                                         p_remote_link => vrec_schema.remote_link, -- remote database link
                                                                         p_job_name => v_job_name, -- job name for this import
                                                                         p_job_handle => v_job_handle); -- job handle
                            -- Else use version with dumpfile name
                            -- not yet coded - dumpfile version goes here xxxxxxxxxxxxxxxx
                            END IF; -- end check for remote link or local file
    
                        END IF; -- check for subquery and table_expression
    
                    END IF; -- end check for schema import, table import, table list
    
                    v_line_no := 10500; -- debug line no
                ELSIF ntasks.task_operation = 'SQL_EXEC'
                THEN
                    v_line_no := 10600; -- debug line no
                    -- execute sql script
                    v_return := oradba.pkg_dp_sql.fn_execute_sql(p_task_id => v_task_id, -- task id
                                                                                        p_message => v_message -- message
                                                                                                              );
    
                    IF v_return < 0
                    THEN
                        raise_application_error(-20001, v_return || ' ' || v_message);
                    END IF;
    
                ELSE
                    v_line_no := 10700; -- debug line no
                    -- task Operation type not coded
                    v_message := 'This task operation type "' || ntasks.task_operation || '"is not coded.';
                    raise_application_error(-20001, v_message);
    
                END IF; -- End check for export or import
    
                v_line_no := 10800; -- debug line no
    
                -- do we monitor?
                IF ntasks.monitor_to_completion = 'Y' AND ntasks.task_operation != 'SQL_EXEC'
                THEN
                    v_line_no := 10900; -- debug line no
                    BEGIN
    
                        DBMS_DATAPUMP.wait_for_job(v_job_handle, --handle
                                                                v_job_state --job_state -
                                                                           );
                    EXCEPTION
                        WHEN OTHERS
                        THEN
                            v_sqlcode := SQLCODE;
                            v_return :=
                                oradba.pkg_dp_util.fn_logging(
                                    p_code => v_sqlcode,
                                    p_message_text =>    'Job:'
                                                      || vrec_job.dp_job_id
                                                      || ' failed with job state:'
                                                      || v_job_state
                                                      || ' on task ID:'
                                                      || vrec_schema.dp_task_id,
                                    p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                    p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                    p_line_no => v_line_no,
                                    p_job_id => vrec_job.dp_job_id,
                                    p_task_id => v_task_id);
                            RAISE;
                    END;
    
                    /*  Alternate code
                    LOOP
                        v_line_no := 10800; -- debug line no
                        -- monitor task
                        v_percent_complete := pkg_dp_monitors.fn_get_pct_complete(p_job_name => v_job_name, p_job_owner => v_current_schema);
                        DBMS_OUTPUT.put_line(SUBSTR('Value of v_percent_complete=' || TO_CHAR(v_percent_complete), 1, 255));
    
                        IF v_percent_complete < 0 OR v_percent_complete >= 100
                        THEN
                            EXIT;
                        END IF;
    
                        DBMS_LOCK.sleep(seconds => v_monitor_interval);
    
                    END LOOP;*/
    
                    v_line_no := 10900; -- debug line no
                -- Log completion
                END IF;
    
                v_line_no := 11000; -- debug line no
                v_return :=
                    oradba.pkg_dp_util.fn_logging(
                        p_code => oradba.pkg_dp_util.logging_informational,
                        p_message_text => 'Task ' || ntasks.dp_task_id || ' ' || NVL(ntasks.task_description, ntasks.task_name) || ' Completed.  ',
                        p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                        p_object_name => $$plsql_unit || '.' || vc_local_fn,
                        p_line_no => v_line_no,
                        p_job_id => vrec_job.dp_job_id,
                        p_task_id => ntasks.dp_task_id);
            END LOOP; -- end of tasks loop
    
            -- return status
            v_line_no := 11100; -- debug line no
    
            v_return :=
                oradba.pkg_dp_util.fn_logging(
                    p_code => oradba.pkg_dp_util.logging_informational,
                    p_message_text =>    'Job '
                                      || NVL(vrec_job.job_description, vrec_job.job_name)
                                      || ' completed.  Note if this was submitted asynchronously it may still be running - see monitoring.',
                    p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                    p_object_name => $$plsql_unit || '.' || vc_local_fn,
                    p_line_no => v_line_no,
                    p_job_id => vrec_job.dp_job_id);
    
            -- Completion status
            p_completion_code := 0;
            p_complete_status := 'Success';
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                p_completion_code := v_sqlcode; -- return code
                p_complete_status := SQLERRM(v_sqlcode);
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => p_completion_code,
                                                  p_message_text => p_complete_status,
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_job_id => vrec_job.dp_job_id,
                                                  p_task_id => v_task_id);
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
                DBMS_OUTPUT.put_line(SUBSTR('main - Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
                RAISE;
        END p_start_job; -- Procedure p_start_job
    END;
    /
    
    CREATE OR REPLACE PACKAGE oradba.pkg_dp_monitors
    IS
        /*
    
            Purpose: Monitoring Examples
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/20/2012    Initial Build
    
        */
    
        ------------------------------------
    
        PROCEDURE p_job_status(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_status      IN OUT BOOLEAN, -- true when Jobs status is true
                               p_operation            IN OUT VARCHAR2, -- operation
                               p_job_mode             IN OUT VARCHAR2, -- job_mode
                               p_bytes_processed      IN OUT NUMBER, -- bytes_processed
                               p_total_bytes          IN OUT NUMBER, -- total_bytes
                               p_percent_done         IN OUT NUMBER, -- percent_done
                               p_degree               IN OUT NUMBER, -- degree
                               p_error_count          IN OUT NUMBER, -- error_count
                               p_state                IN OUT VARCHAR2, -- state
                               p_phase                IN OUT NUMBER, -- phase
                               p_restart_count        IN OUT NUMBER, -- restart_count
                               p_worker_status_list   IN OUT ku$_workerstatuslist1120, -- worker status list
                               p_file_list            IN OUT ku$_dumpfileset1010 -- file list
                                                                                );
    
        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
                                                                                       );
    
        PROCEDURE p_wip_or_error(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_action_desired      IN     NUMBER, -- pass the constant bit  ( KU$_STATUS_WIP or KU$_STATUS_JOB_ERROR) currently 1 and 8
                                 p_bool_wip_or_error   IN OUT BOOLEAN, -- true when Jobs status is true
                                 p_log_entries         IN OUT ku$_logentry -- Error or WIP results for log entries
                                                                          );
    
        FUNCTION fn_get_pct_complete(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
                                                                                                                                   )
            RETURN INTEGER;
    END; -- Package spec
    /
    
    CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_monitors
    IS
        /*
    
            Purpose: Monitoring Examples
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/20/2012    Initial Build
    
        */
    
        PROCEDURE p_job_status(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_status      IN OUT BOOLEAN, -- true when job status has data
                               p_operation            IN OUT VARCHAR2, -- operation
                               p_job_mode             IN OUT VARCHAR2, -- job_mode
                               p_bytes_processed      IN OUT NUMBER, -- bytes_processed
                               p_total_bytes          IN OUT NUMBER, -- total_bytes
                               p_percent_done         IN OUT NUMBER, -- percent_done
                               p_degree               IN OUT NUMBER, -- degree
                               p_error_count          IN OUT NUMBER, -- error_count
                               p_state                IN OUT VARCHAR2, -- state
                               p_phase                IN OUT NUMBER, -- phase
                               p_restart_count        IN OUT NUMBER, -- restart_count
                               p_worker_status_list   IN OUT ku$_workerstatuslist1120, -- worker status list
                               p_file_list            IN OUT ku$_dumpfileset1010 -- file list
                                                                                )
        IS
            /*
    
                       Purpose: attaches and returns the job 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_status,
                                     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_status) != 0
            THEN
                p_bool_job_status := TRUE;
            ELSE
                p_bool_job_status := FALSE;
            END IF;
    
            v_line_no := 300; -- debug line no
    
            -- Detach from handle
            BEGIN
                DBMS_DATAPUMP.detach(handle => v_handle);
            EXCEPTION
                WHEN OTHERS
                THEN
                    NULL;
            END;
    
            IF p_bool_job_status
            THEN
                -- Return data
                p_operation := v_status.job_status.operation; -- operation
                p_job_mode := v_status.job_status.job_mode; -- job_mode
                p_bytes_processed := v_status.job_status.bytes_processed; -- bytes_processed
                p_total_bytes := v_status.job_status.total_bytes; -- total_bytes
                p_percent_done := v_status.job_status.percent_done; -- percent_done
                p_degree := v_status.job_status.degree; -- degree
                p_error_count := v_status.job_status.error_count; -- error_count
                p_state := v_status.job_status.state; -- state
                p_phase := v_status.job_status.phase; -- phase
                p_restart_count := v_status.job_status.restart_count; -- restart_count
    
                -- Get Worker Status List
                p_worker_status_list := v_status.job_status.worker_status_list; -- worker status list
    
                -- Get dump Files List
                p_file_list := v_status.job_status.files; -- files list
    
            END IF;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                --DBMS_OUTPUT.put_line(SUBSTR('Value of v_sqlcode=' || TO_CHAR(v_sqlcode), 1, 255));
                --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_status; -- end procedure p_job_status
    
        --------------------------
    
        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
    
        ------------------------------------
    
        PROCEDURE p_wip_or_error(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_action_desired      IN     NUMBER, -- pass the constant bit  ( KU$_STATUS_WIP or KU$_STATUS_JOB_ERROR) currently 1 and 8
                                 p_bool_wip_or_error   IN OUT BOOLEAN, -- true when Jobs status is true
                                 p_log_entries         IN OUT ku$_logentry -- Error or WIP results for log entries
                                                                          )
        IS
            /*
    
                       Purpose: attaches and returns the status structure
                       Parses the bit
                       Uses the same structure to display info
    
                       MODIFICATION HISTORY
                       Person      Date        Comments
                       ---------   ------      -------------------------------------------
                       dcox        2/8/2013    Initial Build
    
                       */
    
            v_line_no INTEGER := 0; -- debug line no
            v_handle NUMBER; -- job handle
            v_index NUMBER; -- array index
            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
            v_action VARCHAR2(10); -- WIP or ERROR
            v_data ku$_logentry; -- data to be returned
        -- 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 => p_action_desired,
                                     timeout => vc_indefinate_wait_timeout,
                                     job_state => v_job_state,
                                     status => v_status);
    
            -- Decode the bit mask
            IF BITAND(p_action_desired, DBMS_DATAPUMP.ku$_status_wip) != 0
            THEN
                v_action := 'WIP';
    
                IF BITAND(v_status.mask, DBMS_DATAPUMP.ku$_status_wip) != 0
                THEN
                    p_bool_wip_or_error := TRUE;
                ELSE
                    p_bool_wip_or_error := FALSE;
                END IF;
    
                v_data := v_status.wip;
            ELSE
                v_action := 'ERROR';
    
                IF BITAND(v_status.mask, DBMS_DATAPUMP.ku$_status_job_error) != 0
                THEN
                    p_bool_wip_or_error := TRUE;
                ELSE
                    p_bool_wip_or_error := FALSE;
                END IF;
    
                v_data := v_status.error;
            END IF;
    
            -- read if data is not null
            IF p_bool_wip_or_error AND v_data IS NOT NULL
            THEN
    
                -- set log parameter for output
                p_log_entries := v_data;
    
            /*  -----  Optional - to display via DBMS_OUTPUT ------
    
            -- Return data
            v_index := v_data.FIRST;
    
            WHILE v_index IS NOT NULL
            LOOP
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_index=' || TO_CHAR(v_index), 1, 255));
                DBMS_OUTPUT.put_line(v_data(v_index).loglinenumber);
                DBMS_OUTPUT.put_line(v_data(v_index).errornumber);
                DBMS_OUTPUT.put_line(v_data(v_index).logtext);
                DBMS_OUTPUT.put_line('--------------------');
                v_index := v_data.NEXT(v_index);
            END LOOP;*/
    
            END IF;
    
            -- Detach from handle
            BEGIN
                DBMS_DATAPUMP.detach(handle => v_handle);
            EXCEPTION
                WHEN OTHERS
                THEN
                    NULL;
            END;
        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_wip_or_error; -- end proceedure p_job_status
    
        ---------------------------
        FUNCTION fn_get_pct_complete(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
                                                                                                                                   )
            RETURN INTEGER
        IS
            /*
    
                Purpose: Returns the percent complete or -1 if there is an error or no data
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        3/1/2013    Initial Build
    
            */
    
            -- dummy values except v_percent_done
    
            v_bool_job_status BOOLEAN; -- true when job status has data
            v_operation VARCHAR2(200); -- operation
            v_job_mode VARCHAR2(200); -- job_mode
            v_bytes_processed NUMBER; -- bytes_processed
            v_total_bytes NUMBER; -- total_bytes
            v_percent_done NUMBER; -- percent_done
            v_degree NUMBER; -- degree
            v_error_count NUMBER; -- error_count
            v_state VARCHAR2(200); -- state
            v_phase NUMBER; -- phase
            v_restart_count NUMBER; -- restart_count
            v_worker_status_list ku$_workerstatuslist1120; -- worker status list
            v_file_list ku$_dumpfileset1010; -- file list
        BEGIN
            p_job_status(p_job_name,
                         p_job_owner,
                         v_bool_job_status,
                         v_operation,
                         v_job_mode,
                         v_bytes_processed,
                         v_total_bytes,
                         v_percent_done,
                         v_degree,
                         v_error_count,
                         v_state,
                         v_phase,
                         v_restart_count,
                         v_worker_status_list,
                         v_file_list);
    
            IF v_percent_done NOT BETWEEN 0 AND 100
            THEN
                raise_application_error(-20001, 'Error');
            END IF;
    
            RETURN v_percent_done;
        EXCEPTION
            WHEN OTHERS
            THEN
                RETURN -1;
        END;
    END;
    /
    
    CREATE OR REPLACE PACKAGE oradba.pkg_dp_sql
    IS
        /*
    
    
            Purpose: This package has functions and procedures to execute a set of sql statements
            that don't return any values.  For example, inserts, updates, DDL (create user, drop user, add permission).
    
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        3/9/2013    Initial Build
    
        */
    
        FUNCTION fn_execute_sql(p_task_id IN oradba.dp_sql.dp_task_id%TYPE, -- task to be executed
                                                                           p_message IN OUT VARCHAR2 -- return message
                                                                                                    )
            RETURN NUMBER;
    END pkg_dp_sql; -- pkg spec pkg_dp_sql
    /
    
    CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_sql
    IS
        /*
    
    
            Purpose: This package has functions and procedures to execute a set of sql statements
            that don't return any values.  For example, inserts, updates, DDL (create user, drop user, add permission).
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        3/9/2013    Initial Build
    
        */
    
        FUNCTION fn_execute_sql(p_task_id IN oradba.dp_sql.dp_task_id%TYPE, -- task to be executed
                                                                           p_message IN OUT VARCHAR2 -- return message
                                                                                                    )
            RETURN NUMBER
        IS
            /*
    
    
                Purpose: This function executes the sql called for the task ID submitted
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        3/11/2013    Initial Build
    
            */
            -- get the sql to execute
            CURSOR c_get_sql(cv_task_id IN oradba.dp_sql.dp_task_id%TYPE)
            IS
                SELECT *
                  FROM oradba.dp_sql s
                 WHERE s.dp_task_id = cv_task_id;
    
            v_sqlcode NUMBER; -- sql error code
            vrec_sql c_get_sql%ROWTYPE; -- record variable
            v_return NUMBER; -- function return code
            vc_local_fn CONSTANT VARCHAR2(30) := 'fn_execute_sql'; -- local function or procedure name
            v_line_no INTEGER := 0; -- debug line no
        BEGIN
            v_line_no := 100; -- debug line no
            -- get the sql to execute
            OPEN c_get_sql(p_task_id);
            FETCH c_get_sql INTO vrec_sql;
            CLOSE c_get_sql;
    
            BEGIN
                --execute sql command
                EXECUTE IMMEDIATE vrec_sql.sql_stmt;
    
                -- commit any changes
                COMMIT;
            EXCEPTION
                WHEN OTHERS
                THEN
    
                    IF vrec_sql.ignore_error_yn = 'Y'
                    THEN
                        NULL;
                    ELSE
                        RAISE;
                    END IF;
    
            END;
            p_message := 'Success';
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                p_message := SQLERRM(v_sqlcode);
                ROLLBACK;
    
                -- Log error
                v_return :=
                    oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
                                                  p_message_text => SQLERRM(v_sqlcode),
                                                  p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
                                                  p_object_name => $$plsql_unit || '.' || vc_local_fn,
                                                  p_line_no => v_line_no,
                                                  p_task_id => p_task_id);
                RETURN v_sqlcode;
        END;
    END pkg_dp_sql; -- pkg body pkg_dp_sql
    /
    
    CREATE OR REPLACE PACKAGE oradba.pkg_dp_util
    IS
        /*
    
    
            Purpose: Data Pump Automation Utilities go in this package
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/28/2013    Initial Build
    
        */
        -- default tablespace
        vcg_default_tablespace CONSTANT oradba.remap_tablespace.local_tablespace%TYPE := 'USERS'; -- package global default for the default tablespace
    
        -- Logging codes for Success and information
        logging_success CONSTANT INTEGER := 0;
        logging_informational CONSTANT INTEGER := 1;
    
        FUNCTION fn_get_sid
            RETURN VARCHAR2;
    
        FUNCTION fn_stop_job(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
                                                                                                                           )
            RETURN NUMBER;
    
        FUNCTION fn_check_add_user(p_permission_object IN VARCHAR2, -- user or role to check and possibly add
                                                                   p_remote_link IN VARCHAR2)
            RETURN NUMBER;
    
        FUNCTION fn_get_rmt_user_n_role(p_remote_link IN VARCHAR2, -- remote database link
                                                                  p_schema_name IN VARCHAR2, -- schema name to be imported
                                                                                            p_table_name IN VARCHAR2 DEFAULT NULL -- table name to be imported
                                                                                                                                 )
            RETURN NUMBER;
    
        FUNCTION fn_remap_tablespaces(p_remote_tablespace IN VARCHAR2, -- The "FROM" tablespace at the remote database
                                                                      p_bool_always_remap IN BOOLEAN DEFAULT FALSE, -- always remap and don't use the local name if it exists, but still use default if not found on remap
                                                                                                                   p_local_tablespace IN OUT VARCHAR2 -- The "TO" tablespace here on the local database
                                                                                                                                                     )
            RETURN NUMBER;
    
        FUNCTION fn_tbs_remaps_for_link(p_remote_link   IN VARCHAR2, -- remote link - database data to be imported via link
                                        p_handle        IN NUMBER, -- handle opened for import
                                        p_schema_name   IN VARCHAR2, -- schema to be imported
                                        p_table_name    IN VARCHAR2 DEFAULT NULL -- table or object to be imported
                                                                                )
            RETURN NUMBER;
    
        FUNCTION fn_logging(p_code             IN oradba.dp_logging.code_returned%TYPE, -- 0 - Success, 1 - General information , < 0 error codes
                            p_message_text     IN oradba.dp_logging.MESSAGE_TEXT%TYPE, -- message text
                            p_object_schema    IN oradba.dp_logging.object_schema%TYPE, -- Object schema
                            p_object_name      IN oradba.dp_logging.object_name%TYPE, -- Object name
                            p_line_no          IN INTEGER, -- line number where error occurred
                            p_job_id           IN oradba.dp_logging.job_id%TYPE DEFAULT NULL, -- Job ID
                            p_task_id          IN oradba.dp_logging.task_id%TYPE DEFAULT NULL, -- Task ID
                            p_skip_commit_yn   IN VARCHAR2 DEFAULT 'N' -- If for some reason the commit needs to be skipped here in this function then this should be 'Y'
                                                                      )
            RETURN NUMBER;
    
        FUNCTION fn_clean_stopped_job(p_job_owner IN VARCHAR2, -- job owner
                                                              p_job_name IN VARCHAR2 -- job name
                                                                                    )
            RETURN NUMBER;
        FUNCTION fn_create_userperm_script(p_username IN VARCHAR2, --  create script for specific user
                                                                  p_remote_link IN VARCHAR2 -- remote link to remote database
                                                                                           )
            RETURN NUMBER;
    
        FUNCTION fn_create_permissions_script(p_remote_link IN VARCHAR2) -- remote link to remote database
            RETURN NUMBER;
    END pkg_dp_util; -- Package spec pkg_dp_util
    /
    
    CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_util
    IS
        /*
    
    
            Purpose: Data Pump Automation Utilities go in this package
    
            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/28/2013    Initial Build
    
        */
    
        --------------------------------------------
        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
    
        ----------------------------
    
        FUNCTION fn_stop_job(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
                                                                                                                           )
            RETURN NUMBER
        IS
            /*
    
    
                Purpose: This function stops a job with job_name and job_owner
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        3/1/2013    Initial Build
    
            */
            v_line_no INTEGER := 0; -- debug line no
            v_handle NUMBER; -- handle for datapump job
        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);
            -- Add a entry  to the log file
            DBMS_DATAPUMP.log_entry(handle => v_handle,
                                    MESSAGE => 'Manuallying Stopping Job at ' || TO_CHAR(SYSDATE, 'DD-Mon-RR HH24:MI:SS'),
                                    log_file_only => 0);
            DBMS_DATAPUMP.stop_job(handle => v_handle,
                                   immediate => 1,
                                   keep_master => 0,
                                   delay => 30);
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                RETURN SQLCODE;
        END;
    
        ----------------------------
    
        FUNCTION fn_get_rmt_user_n_role(p_remote_link IN VARCHAR2, -- remote database link
                                                                  p_schema_name IN VARCHAR2, -- schema name to be imported
                                                                                            p_table_name IN VARCHAR2 DEFAULT NULL -- table name to be imported
                                                                                                                                 )
            RETURN NUMBER
        IS
            /*
    
    
                Purpose: Get users or roles granted on remote database objects to be imported
    
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/21/2013    Initial Build
    
            */
            v_sqlcode NUMBER; -- sqlcode error
            v_sql VARCHAR2(32767); -- sql code to be executed
            v_return NUMBER; -- return code
            v_grantee VARCHAR2(30); -- user or role
            v_message VARCHAR2(32767); -- custom message to be reported for sql error
    
            TYPE t_cur_grantees IS REF CURSOR; --Create type as ref cursor
    
            vrefcur_grantees t_cur_grantees; -- create reference cursor variable
        BEGIN
    
            -- Get missing users and roles
    
            v_sql :=
                   'SELECT DISTINCT grantee
              FROM dba_tab_privs@'
                || p_remote_link
                || ' WHERE owner = '''
                || p_schema_name
                || ''' AND grantee NOT IN (''PUBLIC'') and table_name like nvl('''
                || p_table_name
                || ''',''%'')  union SELECT '''
                || p_schema_name
                || '''  from dual union select distinct granted_role from dba_role_privs@'
                || p_remote_link
                || ' where grantee = '''
                || p_schema_name
                || '''';
    
            -- Open up cursor to remote server
            OPEN vrefcur_grantees FOR v_sql;
    
            -- Loop throught grantees
            LOOP
    
                -- fetch grantee's from remote server
                v_grantee := NULL; -- re-initialize
    
                FETCH vrefcur_grantees INTO v_grantee;
    
                -- Close cursor to remote server and exit loop when no data found
                IF v_grantee IS NULL
                THEN
    
                    CLOSE vrefcur_grantees;
    
                    EXIT; -- exit loop
                END IF; -- end check for grantee
    
                -- resolve missing user
                v_return := fn_check_add_user(p_permission_object => v_grantee, p_remote_link => p_remote_link);
    
                -- Check for error
                IF v_return < 0
                THEN
                    v_message := 'Effort to add ' || v_grantee || ' failed.';
                    raise_application_error(-20001, v_message);
                END IF; -- end check for error
    
                COMMIT;
            END LOOP; -- end loop through grantees
    
            RETURN 0; -- Success
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                DBMS_OUTPUT.put_line(SUBSTR('fn_get_rmt_user_n_role sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
                RETURN v_sqlcode;
        END fn_get_rmt_user_n_role; -- function fn_get_rmt_user_n_role
    
        -----------------------
        FUNCTION fn_check_add_user(p_permission_object IN VARCHAR2, -- user or role to check and possibly add
                                                                   p_remote_link IN VARCHAR2)
            RETURN NUMBER
        IS
            /*
    
                Purpose: Makes sure user/role either exists or are added to ensure
                import succeeds the way intended. Some permissions may not get applied if the user or role
                does not exist.
    
                1) Is user or Role
                2) Does user or role exist in database
                3) Does user or role exist in lookup table - if exists check permission to add
                4) Add missing user or role when allowed - by permissions in lookup table
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/25/2013    Initial Build
    
            */
    
            v_sqlcode NUMBER; -- sqlcode
            -- write select statement
            v_select VARCHAR2(32767)
                :=    ' select username, ''USER'' from dba_users@'
                   || p_remote_link
                   || '
                    where username = '''
                   || p_permission_object
                   || ''' union select role, ''ROLE'' from dba_roles@'
                   || p_remote_link
                   || '
                    where role = '''
                   || p_permission_object
                   || '''';
    
            TYPE t_cur_permission_obj IS REF CURSOR; --Create type as ref cursor
    
            vrefcur_get_type_perm t_cur_permission_obj; -- create reference cursor variable
            v_permission_name VARCHAR2(30); -- Value for permission object return from select
            v_permission_type VARCHAR2(30); -- "Role" or "User" return from select
    
            -- Check for existance of a username
            CURSOR c_get_user_ct(cv_username IN dba_users.username%TYPE)
            IS
                SELECT COUNT(*)
                  FROM dba_users
                 WHERE username = cv_username;
    
            -- Check for existance of a role
            CURSOR c_get_role_ct(cv_role IN dba_roles.role%TYPE)
            IS
                SELECT COUNT(*)
                  FROM dba_roles
                 WHERE role = cv_role;
    
            v_object_exists INTEGER := 0; -- if it is not 0 then the object exists
    
            -- cursor - Preferences for user or role creation
            CURSOR c_local_perm_obj(cv_permission_name IN imp_user_role_cr_pref.permission_name%TYPE)
            IS
                SELECT *
                  FROM oradba.imp_user_role_cr_pref p
                 WHERE p.permission_name = cv_permission_name;
    
            vrec_local_perm_obj c_local_perm_obj%ROWTYPE; -- Record Variable for local permission cursor
            v_message VARCHAR2(32767); -- application error message
            v_password VARCHAR2(200); -- random password generated
            v_sql VARCHAR2(32767); -- sql to be executed
        BEGIN
    
            --dbms_output.put_line(substr('Value of v_select='||to_char(v_select),1,200));
            --dbms_output.put_line(substr('Value of v_select='||to_char(v_select),201,200));
    
            --- 1) Is User or Role
            OPEN vrefcur_get_type_perm FOR v_select;
    
            FETCH vrefcur_get_type_perm
            INTO v_permission_name, v_permission_type;
    
            CLOSE vrefcur_get_type_perm;
    
            --dbms_output.put_line(substr('Value of v_permission_name='||to_char(v_permission_name),1,255));
            --dbms_output.put_line(substr('Value of v_permission_type='||to_char(v_permission_type),1,255));
            --- 2) Does user or role exist in database
            IF v_permission_type = 'USER'
            THEN
    
                -- get count for the username
                OPEN c_get_user_ct(v_permission_name);
    
                FETCH c_get_user_ct INTO v_object_exists;
    
                CLOSE c_get_user_ct;
    
            ELSE
    
                -- Get count for the role
                OPEN c_get_role_ct(v_permission_name);
    
                FETCH c_get_role_ct INTO v_object_exists;
    
                CLOSE c_get_role_ct;
    
            END IF; --end type of permission  - check for existance
    
            --- 3) Does user or role exist in lookup table - if exists then don't add, add when count = 0
            -- is this object in the current object preference table
            OPEN c_local_perm_obj(v_permission_name);
    
            FETCH c_local_perm_obj INTO vrec_local_perm_obj;
    
            CLOSE c_local_perm_obj;
    
            -- Looking for same name, but different type
            IF vrec_local_perm_obj.permission_name IS NOT NULL AND vrec_local_perm_obj.user_or_role != v_permission_type
            THEN
                v_message :=
                       'Permission object: '
                    || vrec_local_perm_obj.permission_name
                    || ' is a '
                    || vrec_local_perm_obj.user_or_role
                    || ' locally, but is a '
                    || v_permission_type
                    || ' remotely.';
                raise_application_error(-20001, v_message);
            END IF; -- end check for same name different roles
    
            -- add to local table if not there
            IF vrec_local_perm_obj.permission_name IS NULL
            THEN
    
                -- look  at existing DB and add with pref Y if not existing, and N if it exists
                IF v_object_exists = 0
                THEN
                    -- Add to current priv preference table
                    v_sql :=
                           'INSERT INTO oradba.imp_user_role_cr_pref (PERMISSION_NAME,USER_OR_ROLE)
                              VALUES('''
                        || v_permission_name
                        || ''','''
                        || v_permission_type
                        || ''')';
                ELSE
                    -- Add to current priv preference table - with preference = 'N' since it is already existing
                    v_sql :=
                           'INSERT INTO oradba.imp_user_role_cr_pref(permission_name, user_or_role, create_preference_yn)
                        VALUES ('''
                        || v_permission_name
                        || ''','''
                        || v_permission_type
                        || ''',''N'')';
                END IF;
    
                EXECUTE IMMEDIATE v_sql;
    
                COMMIT;
    
                -- Reread the permissions from the preference table (just added)
                OPEN c_local_perm_obj(v_permission_name);
    
                FETCH c_local_perm_obj INTO vrec_local_perm_obj;
    
                CLOSE c_local_perm_obj;
    
            END IF; -- end add to local table for permission names not already there
    
            -- if does not exist in database then add
            IF v_object_exists = 0
            THEN
    
                --- 4) Add missing user or role when allowed - by permissions in lookup table
                -- if allowed to create then create
                IF vrec_local_perm_obj.create_preference_yn = 'Y'
                THEN
    
                    -- create permission in database
                    IF v_permission_type = 'USER'
                    THEN
    
                        -- Create user
                        v_password := DBMS_RANDOM.string('p', 30); -- Random password
                        v_password := REPLACE(v_password, '"', ''); -- Bounding by " seems to help with special characters, but then no " in passwd - remove them
    
                        -- write the statement
                        v_sql := 'create user  ' || v_permission_name || ' identified by "' || v_password || '"';
    
                        -- execute stmt and create user
                        EXECUTE IMMEDIATE v_sql;
    
                        -- add permissions on default TBS (local default db)
                        v_sql := 'ALTER USER ' || v_permission_name || ' QUOTA UNLIMITED ON ' || vcg_default_tablespace;
                        EXECUTE IMMEDIATE v_sql;
    
                    -- Create role
                    ELSIF v_permission_type = 'ROLE'
                    THEN
                        -- add role
                        -- write the statement
                        v_sql := 'create role  ' || v_permission_name;
    
                        -- execute stmt and create role
                        EXECUTE IMMEDIATE v_sql;
    
                    ELSE
                        NULL;
                    END IF; -- end of creation for user and role
    
                END IF; -- if allowed to create then create
    
            END IF; -- end for check if objects exists in current db (step 3)
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                DBMS_OUTPUT.put_line(SUBSTR('vn_check_add_user Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
                RETURN v_sqlcode;
        END fn_check_add_user;
    
        ------------------------
        FUNCTION fn_remap_tablespaces(p_remote_tablespace IN VARCHAR2, -- The "FROM" tablespace at the remote database
                                                                      p_bool_always_remap IN BOOLEAN DEFAULT FALSE, -- always remap and don't use the local name if it exists, but still use default if not found on remap
                                                                                                                   p_local_tablespace IN OUT VARCHAR2 -- The "TO" tablespace here on the local database
                                                                                                                                                     )
            RETURN NUMBER
        IS
            /*
    
    
                Purpose: Does a lookup to determine a cross reference for tablespace changes
                         Uses the default tablespace name when no match is found.
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/21/2013    Initial Build
    
            */
            -- Lookup remap value
            CURSOR c_get_tablespace_remaps(cv_remote_tablespace IN oradba.remap_tablespace.remote_tablespace%TYPE)
            IS
                SELECT a.local_tablespace to_tbs
                  FROM oradba.remap_tablespace a
                 WHERE remote_tablespace = cv_remote_tablespace;
    
            -- Check for local match
            CURSOR c_get_existing_tablespaces(cv_remote_tablespace IN oradba.remap_tablespace.remote_tablespace%TYPE)
            IS
                SELECT tablespace_name
                  FROM dba_tablespaces
                 WHERE tablespace_name = cv_remote_tablespace;
    
            v_line_no INTEGER := 0; -- debug line no
            v_sqlcode NUMBER; -- sqlcode error
        BEGIN
            v_line_no := 100; -- debug line no
    
            -- look up local tablespace - skip if this is true and use remap values instead
            IF NVL(p_bool_always_remap, FALSE) = FALSE
            THEN
                v_line_no := 200; -- debug line no
    
                OPEN c_get_existing_tablespaces(p_remote_tablespace);
    
                FETCH c_get_existing_tablespaces INTO p_local_tablespace;
    
                CLOSE c_get_existing_tablespaces;
    
            END IF;
    
            -- When not found then look for a remap value
            IF p_local_tablespace IS NULL
            THEN
    
                -- Look up a match for the local tablespace
                OPEN c_get_tablespace_remaps(p_remote_tablespace);
    
                FETCH c_get_tablespace_remaps INTO p_local_tablespace;
    
                CLOSE c_get_tablespace_remaps;
    
            END IF;
    
            -- set to default if still null
            IF p_local_tablespace IS NULL
            THEN
                p_local_tablespace := vcg_default_tablespace;
            END IF;
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
                RETURN v_sqlcode;
        END fn_remap_tablespaces; -- Function fn_remap_tablespaces
        -------------------------
        FUNCTION fn_tbs_remaps_for_link(p_remote_link   IN VARCHAR2, -- remote link - database data to be imported via link
                                        p_handle        IN NUMBER, -- handle opened for import
                                        p_schema_name   IN VARCHAR2, -- schema to be imported
                                        p_table_name    IN VARCHAR2 DEFAULT NULL -- table or object to be imported
                                                                                )
            RETURN NUMBER
        IS
            /*
    
                Purpose: This function will add the remap lines for tablespaces associated
                         with a given schema to be loaded over a database link
    
                         For example, if the remote tablespace is called DATA, and the only
                         tablespace locally is USERS.  Users can be defined as the default tablespace.
    
                         OR
    
                         Multiple tablespaces can be mapped in the local table on how the tablespaces should be remapped
                         (table name: REMAP_TABLESPACE)
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/21/2013    Initial Build
    
            */
    
            TYPE t_cur_tablespaces IS REF CURSOR; --Create type as ref cursor
    
            vrefcur_tablespace t_cur_tablespaces; -- create reference cursor variable
            v_remote_tablespace oradba.remap_tablespace.remote_tablespace%TYPE; -- remote tablespace
            v_local_tablespace oradba.remap_tablespace.local_tablespace%TYPE; -- local tablespace
            v_select VARCHAR2(32767);
            v_return NUMBER; -- return value from function
            v_message VARCHAR2(32767); -- error message
            v_sqlcode NUMBER; -- sqlcode error
        BEGIN
    
            -- write select statement
            v_select := 'select distinct tablespace_name
                    from dba_segments@' || p_remote_link || '
                    where owner = ''' || p_schema_name || '''';
    
            -- Loop through tablespaces and write remap statements
            IF p_table_name IS NOT NULL
            THEN
                v_select := v_select || ' and segment_name = (''' || p_table_name || ''',''%'')';
            END IF;
    
            -- open ref cursor
            OPEN vrefcur_tablespace FOR v_select;
    
            LOOP
    
                FETCH vrefcur_tablespace INTO v_remote_tablespace;
    
                EXIT WHEN vrefcur_tablespace%NOTFOUND;
    
                -- Get remap value
                v_return :=
                    fn_remap_tablespaces(p_remote_tablespace => v_remote_tablespace, p_bool_always_remap => FALSE, p_local_tablespace => v_local_tablespace);
    
                IF v_return < 0
                THEN
                    v_message := 'Failure in fn_remap_tablespaces, cannot remap remote tablespace:' || v_remote_tablespace;
                    raise_application_error(-20001, v_message);
                END IF;
    
                DBMS_DATAPUMP.metadata_remap(p_handle,
                                             'REMAP_TABLESPACE',
                                             v_remote_tablespace,
                                             v_local_tablespace);
    
            END LOOP;
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
    
                RETURN v_sqlcode;
        END fn_tbs_remaps_for_link; --function fn_tbs_remaps_for_link
    
        -------------------------------
        FUNCTION fn_logging(p_code             IN oradba.dp_logging.code_returned%TYPE, -- 0 - Success, 1 - General information , < 0 error codes
                            p_message_text     IN oradba.dp_logging.MESSAGE_TEXT%TYPE, -- message text
                            p_object_schema    IN oradba.dp_logging.object_schema%TYPE, -- Object schema
                            p_object_name      IN oradba.dp_logging.object_name%TYPE, -- Object name
                            p_line_no          IN INTEGER, -- line number where error occurred
                            p_job_id           IN oradba.dp_logging.job_id%TYPE DEFAULT NULL, -- Job ID
                            p_task_id          IN oradba.dp_logging.task_id%TYPE DEFAULT NULL, -- Task ID
                            p_skip_commit_yn   IN VARCHAR2 DEFAULT 'N' -- If for some reason the commit needs to be skipped here in this function then this should be 'Y'
                                                                      )
            RETURN NUMBER
        IS
            /*
    
    
                Purpose: Log an error, success or informational message
    
                Note
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        3/4/2013    Initial Build
    
            */
    
            v_sql VARCHAR2(4000) := 'INSERT INTO oradba.dp_logging(code_returned,
                                  MESSAGE_TEXT,
                                  object_schema,
                                  object_name,
                                  line_no,
                                  job_id,
                                  task_id)
         VALUES (:code_returned,
                 :MESSAGE_TEXT,
                 :object_schema,
                 :object_name,
                 :line_no,
                 :job_id,
                 :task_id)'; -- sql text to write to log table
            v_sqlcode NUMBER; -- error code
        BEGIN
            EXECUTE IMMEDIATE v_sql
                USING p_code, -- code_returned
                      p_message_text, -- MESSAGE_TEXT
                      p_object_schema, -- object_schema
                      p_object_name, -- object_name
                      NVL(p_line_no, 0), -- line no
                      p_job_id, -- job_id
                      p_task_id -- task_id
                               ;
    
            -- commit to table
            IF p_skip_commit_yn IS NULL OR p_skip_commit_yn != 'Y'
            THEN
                COMMIT;
            END IF;
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                ROLLBACK;
                RETURN v_sqlcode;
        END fn_logging; -- function fn_logging
        ------------------------
        FUNCTION fn_clean_stopped_job(p_job_owner IN VARCHAR2, -- job owner
                                                              p_job_name IN VARCHAR2 -- job name
                                                                                    )
            RETURN NUMBER
        IS
            /*
    
                Purpose: Clean stopped jobs
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        3/5/2013    Initial Build
    
            */
            -- datapump jobs
            CURSOR c_datapump_jobs(cv_owner_name IN dba_datapump_jobs.owner_name%TYPE, cv_job_name IN dba_datapump_jobs.job_name%TYPE)
            IS
                SELECT *
                  FROM dba_datapump_jobs a
                 WHERE owner_name = cv_owner_name AND job_name = cv_job_name AND state NOT IN ('EXECUTING') AND state IN ('NOT RUNNING');
    
            vrec_dp_jobs c_datapump_jobs%ROWTYPE; -- record for datapump jobs result
    
            v_sql VARCHAR2(32767); -- sql to be executed
        BEGIN
    
            -- check for the value and don't delete and purge if it is running
            OPEN c_datapump_jobs(p_job_owner, p_job_name);
            FETCH c_datapump_jobs INTO vrec_dp_jobs;
            CLOSE c_datapump_jobs;
    
            -- verifying that it is not executing by using the record returned
            IF vrec_dp_jobs.job_name IS NOT NULL
            THEN
                v_sql := 'Delete TABLE ' || vrec_dp_jobs.owner_name || '.' || vrec_dp_jobs.job_name;
                EXECUTE IMMEDIATE v_sql;
    
                v_sql := 'PURGE TABLE ' || vrec_dp_jobs.owner_name || '.' || vrec_dp_jobs.job_name;
                EXECUTE IMMEDIATE v_sql;
            END IF;
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                RETURN SQLCODE;
        END fn_clean_stopped_job; -- function fn_clean_stopped_job
    
        ---------------------
        FUNCTION fn_create_permissions_script(p_remote_link IN VARCHAR2) -- remote link to remote database
            RETURN NUMBER
        IS
            /*
    
                Purpose: Create a permissions script that can be executed on a remote db to pull over user grants, roles, profiles, verify_function's, etc
    
                Directions: Run this script, get the script generated and run it on the target system, get the output and then run it here on this system to apply
                the correct permissions, etc.
    
                This is just like fn_create_userperm_script, but for a list of users from the table that are allowed to be created.
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/27/2013    Initial Build
    
            */
    
            -- Get any needed "verify_functions"
            CURSOR c_get_verify_function
            IS
                SELECT 'select DBMS_METADATA.get_ddl(''FUNCTION'', object_name, owner) ddl from dual; ' ddl
                  FROM dba_objects
                 WHERE     owner = 'SYS'
                       AND object_name IN (SELECT LIMIT
                                             FROM dba_profiles
                                            WHERE     profile != 'DEFAULT'
                                                  AND resource_name = 'PASSWORD_VERIFY_FUNCTION'
                                                  AND profile IN (SELECT profile
                                                                    FROM dba_users
                                                                   WHERE username IN (SELECT permission_name
                                                                                        FROM oradba.imp_user_role_cr_pref p
                                                                                       WHERE p.create_preference_yn = 'Y')));
    
            -- Create any needed Profiles
            CURSOR c_get_profiles
            IS
                SELECT 'select  DBMS_METADATA.get_ddl(''PROFILE'', ''' || profile || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE     username IN (SELECT permission_name
                                          FROM oradba.imp_user_role_cr_pref p
                                         WHERE p.create_preference_yn = 'Y')
                       AND profile NOT IN (SELECT profile FROM dba_profiles);
    
            -- Get the basic user ddl
            CURSOR c_user_basic_info
            IS
                SELECT 'select DBMS_METADATA.get_ddl(''USER'', ''' || username || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (SELECT permission_name
                                      FROM oradba.imp_user_role_cr_pref p
                                     WHERE p.create_preference_yn = 'Y');
    
            -- Get tablespace quota
            CURSOR c_tablespace_quota
            IS
                SELECT    'select DBMS_METADATA.get_granted_ddl(''TABLESPACE_QUOTA'', '''
                       || username
                       || ''') ddl from dual'
                       || ' where exists (select username '
                       || ' from dba_ts_quotas where max_bytes != -1 and username = '''
                       || username
                       || '''); '
                           ddl
                  FROM dba_users
                 WHERE username IN (SELECT permission_name
                                      FROM oradba.imp_user_role_cr_pref p
                                     WHERE p.create_preference_yn = 'Y');
    
            -- Get role grants for users
            CURSOR c_get_role_grants
            IS
                SELECT 'select DBMS_METADATA.get_granted_ddl(''ROLE_GRANT'', ''' || username || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (SELECT permission_name
                                      FROM oradba.imp_user_role_cr_pref p
                                     WHERE p.create_preference_yn = 'Y');
    
            -- Get system grants
            CURSOR c_get_system_grants
            IS
                SELECT 'select DBMS_METADATA.get_granted_ddl(''SYSTEM_GRANT'', ''' || username || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (SELECT permission_name
                                      FROM oradba.imp_user_role_cr_pref p
                                     WHERE p.create_preference_yn = 'Y');
    
            -- Get Objects grants
            CURSOR c_get_object_grants
            IS
                SELECT 'select DBMS_METADATA.get_granted_ddl(''OBJECT_GRANT'', ''' || username || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (SELECT permission_name
                                      FROM oradba.imp_user_role_cr_pref p
                                     WHERE p.create_preference_yn = 'Y');
    
            -- Get directory path
            CURSOR c_get_dir_path(cv_dir_name IN dba_directories.directory_name%TYPE)
            IS
                SELECT directory_path
                  FROM dba_directories d
                 WHERE directory_name = cv_dir_name;
    
            v_file_handle UTL_FILE.file_type; -- file handle
            v_directory VARCHAR2(32767) := 'DATA_PUMP_DIR'; -- directory name
            v_file_name VARCHAR2(32767) := 'metadata_script_' || p_remote_link || '_' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI'); -- filename
            v_buffer VARCHAR2(32767); -- buffere to write to file
            v_sqlcode NUMBER; -- sqlcode for error
            vc_comment_chars CONSTANT VARCHAR2(10) := '-- '; -- comment characters
            vc_local_fn CONSTANT VARCHAR2(30) := 'fn_create_profile_script'; -- local function name
            v_line_no INTEGER := 0; -- debug line number
            v_dump_dir_path VARCHAR2(32767); -- spool directory path
        BEGIN
            v_line_no := 100; -- debug line number
    
            -- open up file to write
            v_file_handle := UTL_FILE.fopen(location => v_directory, filename => v_file_name || '.sql', open_mode => 'w');
    
            -- start creating scripts and writing to buffer
            -- Compose header in comments
            -- Basic title
            -- Creation Date Time
            -- Created with xx procedure
            v_buffer :=
                   vc_comment_chars
                || ' Metadata script to pull data for users that will be (or have been) imported into this local database from '
                || p_remote_link
                || UTL_TCP.crlf;
            v_buffer :=
                v_buffer || vc_comment_chars || ' Creation started at: ' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI_SS') || SESSIONTIMEZONE || UTL_TCP.crlf;
            v_buffer := v_buffer || vc_comment_chars || ' Created using: ' || $$plsql_unit || '.' || vc_local_fn || UTL_TCP.crlf;
    
            -- compose basic sql parameters
            v_buffer := v_buffer || 'Set HEAD OFF' || UTL_TCP.crlf;
            v_buffer := v_buffer || 'Set ECHO OFF' || UTL_TCP.crlf;
            v_buffer := v_buffer || 'Set long 1000000' || UTL_TCP.crlf; -- watch memory on this one
            v_buffer := v_buffer || 'Set PAGES 0' || UTL_TCP.crlf;
            v_buffer := v_buffer || 'Set lines 160' || UTL_TCP.crlf;
            v_buffer := v_buffer || 'Set feedback off' || UTL_TCP.crlf;
    
            -- compose error handling for sqlplus
            v_buffer := v_buffer || 'WHENEVER SQLERROR CONTINUE ' || UTL_TCP.crlf; --  may want a different termination task here?? SQL.SQLCODE | WARNING | CONTINUE | Exit Error ??
    
            -- get directory
            OPEN c_get_dir_path(v_directory);
    
            FETCH c_get_dir_path INTO v_dump_dir_path;
    
            CLOSE c_get_dir_path;
    
            -- compose spool log file for execution
            v_buffer := v_buffer || 'spool ' || v_dump_dir_path || v_file_name || '_output.sql' || UTL_TCP.crlf; --  might want to send this to a ./output subdirectory or a complete file path
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
    
            -- Main script body start
            -- spool to output for final execution file
            v_buffer := v_buffer || 'select ''spool permission_add.output;'' from dual;' || UTL_TCP.crlf;
    
            -- Make the data nicer to execute in sqlplus --- Adds in the ';' terminator
            v_buffer := v_buffer || 'begin
            DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''SQLTERMINATOR'', TRUE);
            DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''PRETTY'', TRUE);
            end;
            /';
    
            v_line_no := 500; -- debug line number
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 550; -- debug line number
    
            -- Get verify function(s)
            FOR nverify_fns IN c_get_verify_function
            LOOP
                v_buffer := v_buffer || nverify_fns.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 600; -- debug line number
    
            -- Get Profile(s)
            FOR nprofiles IN c_get_profiles
            LOOP
                v_buffer := v_buffer || nprofiles.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
    
            -- Get basic user(s) info
            FOR nusers IN c_user_basic_info
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 700; -- debug line number
    
            -- Get tablespace Quota(s)
            FOR nusers IN c_tablespace_quota
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 800; -- debug line number
    
            -- Get role grant(s)
            FOR nusers IN c_get_role_grants
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
    
            -- Get System Grants
            FOR nusers IN c_get_system_grants
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 900; -- debug line number
    
            -- Get Object Grants
            FOR nusers IN c_get_object_grants
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 1000; -- debug line number
    
            -- main script body end
            -- composition close comments
            -- close title
            -- close date time
            v_buffer := v_buffer || vc_comment_chars || ' Metadata scrip generation completed.' || UTL_TCP.crlf;
            v_buffer := v_buffer || vc_comment_chars || ' Completed at: ' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI_SS') || SESSIONTIMEZONE || UTL_TCP.crlf;
            v_buffer := v_buffer || vc_comment_chars || ' Created using: ' || $$plsql_unit || '.' || vc_local_fn || UTL_TCP.crlf;
            -- stop spool to output for final execution file
            v_buffer := v_buffer || 'select ''spool off;'' from dual;' || UTL_TCP.crlf;
            -- Closeout parameters
            v_buffer := v_buffer || 'spool off;' || UTL_TCP.crlf;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 1500; -- debug line number
            UTL_FILE.fclose(v_file_handle);
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
                DBMS_OUTPUT.put_line(SUBSTR('Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
    
                BEGIN
                    UTL_FILE.fclose(v_file_handle);
                EXCEPTION
                    WHEN OTHERS
                    THEN
                        NULL;
                END;
    
                RETURN v_sqlcode;
        END fn_create_permissions_script; -- function fn_create_permissions_script
    
        --------------------
        FUNCTION fn_create_userperm_script(p_username IN VARCHAR2, --  create script for specific user
                                                                  p_remote_link IN VARCHAR2 -- remote link to remote database
                                                                                           )
            RETURN NUMBER
        IS
            /*
    
                Purpose: Create a permissions script that can be executed on a remote db to pull over user grants, roles, profiles, verify_function's, etc for a specific user
    
                Directions: Run this script, get the script generated and run it on the target system, get the output and then run it here on this system to apply
                the correct permissions, etc.
    
                This is just like fn_create_permissions_script, but for a single user
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        3/9/2013    Initial Build
    
            */
    
            -- Get any needed "verify_functions"
            CURSOR c_get_verify_function
            IS
                SELECT 'select DBMS_METADATA.get_ddl(''FUNCTION'', object_name, owner) ddl from dual; ' ddl
                  FROM dba_objects
                 WHERE     owner = 'SYS'
                       AND object_name IN (SELECT LIMIT
                                             FROM dba_profiles
                                            WHERE     profile != 'DEFAULT'
                                                  AND resource_name = 'PASSWORD_VERIFY_FUNCTION'
                                                  AND profile IN (SELECT profile
                                                                    FROM dba_users
                                                                   WHERE username IN (p_username)));
    
            -- Create any needed Profiles
            CURSOR c_get_profiles
            IS
                SELECT 'select  DBMS_METADATA.get_ddl(''PROFILE'', ''' || profile || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (p_username) AND profile NOT IN (SELECT profile FROM dba_profiles);
    
            -- Get the basic user ddl
            CURSOR c_user_basic_info
            IS
                SELECT 'select DBMS_METADATA.get_ddl(''USER'', ''' || username || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (p_username);
    
            -- Get tablespace quota
            CURSOR c_tablespace_quota
            IS
                SELECT    'select DBMS_METADATA.get_granted_ddl(''TABLESPACE_QUOTA'', '''
                       || username
                       || ''') ddl from dual'
                       || ' where exists (select username '
                       || ' from dba_ts_quotas where max_bytes != -1 and username = '''
                       || username
                       || '''); '
                           ddl
                  FROM dba_users
                 WHERE username IN (p_username);
    
            -- Get role grants for users
            CURSOR c_get_role_grants
            IS
                SELECT 'select DBMS_METADATA.get_granted_ddl(''ROLE_GRANT'', ''' || username || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (p_username);
    
            -- Get system grants
            CURSOR c_get_system_grants
            IS
                SELECT 'select DBMS_METADATA.get_granted_ddl(''SYSTEM_GRANT'', ''' || username || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (p_username);
    
            -- Get Objects grants
            CURSOR c_get_object_grants
            IS
                SELECT 'select DBMS_METADATA.get_granted_ddl(''OBJECT_GRANT'', ''' || username || ''') ddl from dual;' ddl
                  FROM dba_users
                 WHERE username IN (p_username);
    
            -- Get directory path
            CURSOR c_get_dir_path(cv_dir_name IN dba_directories.directory_name%TYPE)
            IS
                SELECT directory_path
                  FROM dba_directories d
                 WHERE directory_name = cv_dir_name;
    
            v_file_handle UTL_FILE.file_type; -- file handle
            v_directory VARCHAR2(32767) := 'DATA_PUMP_DIR'; -- directory name
            v_file_name VARCHAR2(32767) := 'metadata_script_' || p_remote_link || '_' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI'); -- filename
            v_buffer VARCHAR2(32767); -- buffere to write to file
            v_sqlcode NUMBER; -- sqlcode for error
            vc_comment_chars CONSTANT VARCHAR2(10) := '-- '; -- comment characters
            vc_local_fn CONSTANT VARCHAR2(30) := 'fn_create_profile_script'; -- local function name
            v_line_no INTEGER := 0; -- debug line number
            v_dump_dir_path VARCHAR2(32767); -- spool directory path
        BEGIN
            v_line_no := 100; -- debug line number
    
            -- open up file to write
            v_file_handle := UTL_FILE.fopen(location => v_directory, filename => v_file_name || '.sql', open_mode => 'w');
    
            -- start creating scripts and writing to buffer
            -- Compose header in comments
            -- Basic title
            -- Creation Date Time
            -- Created with xx procedure
            v_buffer :=
                   vc_comment_chars
                || ' Metadata script to pull data for users that will be (or have been) imported into this local database from '
                || p_remote_link
                || UTL_TCP.crlf;
            v_buffer :=
                v_buffer || vc_comment_chars || ' Creation started at: ' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI_SS') || SESSIONTIMEZONE || UTL_TCP.crlf;
            v_buffer := v_buffer || vc_comment_chars || ' Created using: ' || $$plsql_unit || '.' || vc_local_fn || UTL_TCP.crlf;
    
            -- compose basic sql parameters
            v_buffer := v_buffer || 'Set HEAD OFF' || UTL_TCP.crlf;
            v_buffer := v_buffer || 'Set ECHO OFF' || UTL_TCP.crlf;
            v_buffer := v_buffer || 'Set long 1000000' || UTL_TCP.crlf; -- watch memory on this one
            v_buffer := v_buffer || 'Set PAGES 0' || UTL_TCP.crlf;
            v_buffer := v_buffer || 'Set lines 160' || UTL_TCP.crlf;
            v_buffer := v_buffer || 'Set feedback off' || UTL_TCP.crlf;
    
            -- compose error handling for sqlplus
            v_buffer := v_buffer || 'WHENEVER SQLERROR CONTINUE ' || UTL_TCP.crlf; --  may want a different termination task here?? SQL.SQLCODE | WARNING | CONTINUE | Exit Error ??
    
            -- get directory
            OPEN c_get_dir_path(v_directory);
    
            FETCH c_get_dir_path INTO v_dump_dir_path;
    
            CLOSE c_get_dir_path;
    
            -- compose spool log file for execution
            v_buffer := v_buffer || 'spool ' || v_dump_dir_path || v_file_name || '_output.sql' || UTL_TCP.crlf; --  might want to send this to a ./output subdirectory or a complete file path
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
    
            -- Main script body start
            -- spool to output for final execution file
            v_buffer := v_buffer || 'select ''spool permission_add.output;'' from dual;' || UTL_TCP.crlf;
    
            -- Make the data nicer to execute in sqlplus --- Adds in the ';' terminator
            v_buffer := v_buffer || 'begin
            DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''SQLTERMINATOR'', TRUE);
            DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''PRETTY'', TRUE);
            end;
            /';
    
            v_line_no := 500; -- debug line number
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 550; -- debug line number
    
            -- Get verify function(s)
            FOR nverify_fns IN c_get_verify_function
            LOOP
                v_buffer := v_buffer || nverify_fns.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 600; -- debug line number
    
            -- Get Profile(s)
            FOR nprofiles IN c_get_profiles
            LOOP
                v_buffer := v_buffer || nprofiles.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
    
            -- Get basic user(s) info
            FOR nusers IN c_user_basic_info
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 700; -- debug line number
    
            -- Get tablespace Quota(s)
            FOR nusers IN c_tablespace_quota
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 800; -- debug line number
    
            -- Get role grant(s)
            FOR nusers IN c_get_role_grants
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
    
            -- Get System Grants
            FOR nusers IN c_get_system_grants
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 900; -- debug line number
    
            -- Get Object Grants
            FOR nusers IN c_get_object_grants
            LOOP
                v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
            END LOOP;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 1000; -- debug line number
    
            -- main script body end
            -- composition close comments
            -- close title
            -- close date time
            v_buffer := v_buffer || vc_comment_chars || ' Metadata scrip generation completed.' || UTL_TCP.crlf;
            v_buffer := v_buffer || vc_comment_chars || ' Completed at: ' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI_SS') || SESSIONTIMEZONE || UTL_TCP.crlf;
            v_buffer := v_buffer || vc_comment_chars || ' Created using: ' || $$plsql_unit || '.' || vc_local_fn || UTL_TCP.crlf;
            -- stop spool to output for final execution file
            v_buffer := v_buffer || 'select ''spool off;'' from dual;' || UTL_TCP.crlf;
            -- Closeout parameters
            v_buffer := v_buffer || 'spool off;' || UTL_TCP.crlf;
    
            -- Write to buffer
            UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
            v_buffer := NULL;
            v_line_no := 1500; -- debug line number
            UTL_FILE.fclose(v_file_handle);
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
                DBMS_OUTPUT.put_line(SUBSTR('Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
    
                BEGIN
                    UTL_FILE.fclose(v_file_handle);
                EXCEPTION
                    WHEN OTHERS
                    THEN
                        NULL;
                END;
    
                RETURN v_sqlcode;
        END fn_create_userperm_script; -- function fn_create_userperm_script
    END pkg_dp_util; -- package body pkg_dp_util
    /
    

    I encourage you to leave comments if this is interesting, and I’ll add to it if there is any interest.

    DBMS_DATAPUMP PL/SQL Example SCHEMA Import across a link with TABLESPACE REMAPS

    I like to automate as much as possible, so to have to type in the remapping for a Tablespace every time is tiresome. Here’s a simple example with it automated. This example either uses a table to remap the tablespaces or defaults to a specific tablespace. You may also want to check to see if the tablespace exists and accept putting objects into the same tablespace, or you may want to remap with your mapping table. An option to do this is added as well.

    Here’s the table:

    CREATE TABLE oradba.remap_tablespace
    (
        local_tablespace VARCHAR2(30 BYTE) NOT NULL,
        remote_tablespace VARCHAR2(30 BYTE) NOT NULL,
        insert_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
        insert_user VARCHAR2(30 BYTE) NOT NULL,
        update_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
        update_user VARCHAR2(30 BYTE) NOT NULL
    )
    
    /
    
    -- Constraints for ORADBA.REMAP_TABLESPACE
    
    ALTER TABLE oradba.remap_tablespace
    ADD CONSTRAINT rempa_tablespace_uk1 UNIQUE (local_tablespace, remote_tablespace)
    USING INDEX
      TABLESPACE  users
      )
    /
    
    -- Triggers for ORADBA.REMAP_TABLESPACE
    
    CREATE OR REPLACE TRIGGER oradba.remap_tablespace_bi
        BEFORE INSERT
        ON oradba.remap_tablespace
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
        :new.insert_ts := SYSDATE;
        :new.insert_user := USER;
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    
    END;
    /
    
    CREATE OR REPLACE TRIGGER oradba.remap_tablespace_bu
        BEFORE UPDATE
        ON oradba.remap_tablespace
        REFERENCING NEW AS new OLD AS old
        FOR EACH ROW
    BEGIN
        :new.update_ts := SYSDATE;
        :new.update_user := USER;
    END;
    /
    
    -- Comments for ORADBA.REMAP_TABLESPACE
    
    COMMENT ON TABLE oradba.remap_tablespace IS 'Cross Reference to remap remote tablespaces to local tablespaces.'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.insert_ts IS 'Insert Timestamp'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.insert_user IS 'Insert Userstamp'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.local_tablespace IS 'Local_Tablespace'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.remote_tablespace IS 'Remote Tablespace'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.update_ts IS 'Update Timestamp'
    /
    COMMENT ON COLUMN oradba.remap_tablespace.update_user IS 'Update Userstamp'
    /
    

    Here are the functions and procedure, I usually have these in a package. I’m just adding them in as a tool set you can arrange/modify as you want. You may have to change the calls slightly, I’ve pulled these from 2 different packages.

    Put this default in the spec:

    vcg_default_tablespace constant oradba.remap_tablespace.local_tablespace%type := 'USERS'; -- package global default for the default tablespace
    

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

    ———

    FUNCTION fn_remap_tablespaces(p_remote_tablespace IN VARCHAR2, -- The "FROM" tablespace at the remote database
                                                                      p_bool_always_remap IN BOOLEAN DEFAULT FALSE, -- always remap and don't use the local name if it exists, but still use default if not found on remap
                                                                                                                   p_local_tablespace IN OUT VARCHAR2 -- The "TO" tablespace here on the local database
                                                                                                                                                     )
            RETURN NUMBER
        IS
            /*
    
    
                Purpose: Does a lookup to determine a cross reference for tablespace changes
                         Uses the default tablespace name when no match is found.
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/21/2012    Initial Build
    
            */
            -- Lookup remap value
            CURSOR c_get_tablespace_remaps(cv_remote_tablespace IN oradba.remap_tablespace.remote_tablespace%TYPE)
            IS
                SELECT a.local_tablespace to_tbs
                  FROM oradba.remap_tablespace a
                 WHERE remote_tablespace = cv_remote_tablespace;
    
            -- Check for local match
            CURSOR c_get_existing_tablespaces(cv_remote_tablespace IN oradba.remap_tablespace.remote_tablespace%TYPE)
            IS
                SELECT tablespace_name
                  FROM dba_tablespaces
                 WHERE tablespace_name = cv_remote_tablespace;
    
            v_line_no INTEGER := 0; -- debug line no
            v_sqlcode NUMBER; -- sqlcode error
        BEGIN
            v_line_no := 100; -- debug line no
    
            -- look up local tablespace - skip if this is true and use remap values instead
            IF NVL(p_bool_always_remap, FALSE) = FALSE
            THEN
                v_line_no := 200; -- debug line no
    
                OPEN c_get_existing_tablespaces(p_remote_tablespace);
    
                FETCH c_get_existing_tablespaces INTO p_local_tablespace;
    
                CLOSE c_get_existing_tablespaces;
    
            END IF;
    
            -- When not found then look for a remap value
            IF p_local_tablespace IS NULL
            THEN
    
                -- Look up a match for the local tablespace
                OPEN c_get_tablespace_remaps(p_remote_tablespace);
    
                FETCH c_get_tablespace_remaps INTO p_local_tablespace;
    
                CLOSE c_get_tablespace_remaps;
    
            END IF;
    
            -- set to default if still null
            IF p_local_tablespace IS NULL
            THEN
                p_local_tablespace := vcg_default_tablespace;
            END IF;
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
                RETURN v_sqlcode;
        END fn_remap_tablespaces; -- Function fn_remap_tablespaces
    

    —-

    FUNCTION fn_tbs_remaps_for_link(p_remote_link   IN VARCHAR2, -- remote link - database data to be imported via link
                                        p_handle        IN NUMBER, -- handle opened for import
                                        p_schema_name   IN VARCHAR2, -- schema to be imported
                                        p_table_name    IN VARCHAR2 DEFAULT NULL -- table or object to be imported
                                                                                )
            RETURN NUMBER
        IS
            /*
    
                Purpose: This function will add the remap lines for tablespaces associated
                         with a given schema to be loaded over a database link
    
                         For example, if the remote tablespace is called DATA, and the only
                         tablespace locally is USERS.  Users can be defined as the default tablespace.
    
                         OR
    
                         Multiple tablespaces can be mapped in the local table on how the tablespaces should be remapped
                         (table name: REMAP_TABLESPACE)
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/21/2012    Initial Build
    
            */
    
            TYPE t_cur_tablespaces IS REF CURSOR; --Create type as ref cursor
    
            vrefcur_tablespace t_cur_tablespaces; -- create reference cursor variable
            v_remote_tablespace oradba.remap_tablespace.remote_tablespace%TYPE; -- remote tablespace
            v_local_tablespace oradba.remap_tablespace.local_tablespace%TYPE; -- local tablespace
            v_select VARCHAR2(32767);
            v_return NUMBER; -- return value from function
            v_message VARCHAR2(32767); -- error message
            v_sqlcode NUMBER; -- sqlcode error
        BEGIN
    
            -- write select statement
            v_select := 'select distinct tablespace_name
                    from dba_segments@' || p_remote_link || '
                    where owner = ''' || p_schema_name || '''';
    
            -- Loop through tablespaces and write remap statements
            IF p_table_name IS NOT NULL
            THEN
                v_select := v_select || ' and segment_name = (''' || p_table_name || ''',''%'')';
            END IF;
            dbms_output.put_line(substr('Value of v_select='||to_char(v_select),1,255));
            -- open ref cursor
            OPEN vrefcur_tablespace FOR v_select;
    
            LOOP
    
                FETCH vrefcur_tablespace INTO v_remote_tablespace;
                EXIT WHEN vrefcur_tablespace%NOTFOUND;
    
                -- Get remap value
                v_return :=
                    fn_remap_tablespaces(p_remote_tablespace => v_remote_tablespace, p_bool_always_remap => FALSE, p_local_tablespace => v_local_tablespace);
    
                IF v_return < 0
                THEN
                    v_message := 'Failure in fn_remap_tablespaces, cannot remap remote tablespace:' || v_remote_tablespace;
                    raise_application_error(-20001, v_message);
                END IF;
    
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_remote_tablespace=' || TO_CHAR(v_remote_tablespace), 1, 255));
                DBMS_OUTPUT.put_line(SUBSTR('Value of v_local_tablespace=' || TO_CHAR(v_local_tablespace), 1, 255));
                DBMS_OUTPUT.put_line('-----------');
                DBMS_DATAPUMP.metadata_remap(p_handle,
                                             'REMAP_TABLESPACE',
                                             v_remote_tablespace,
                                             v_local_tablespace);
    
    
            END LOOP;
    
            RETURN 0;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := SQLCODE;
    
                RETURN v_sqlcode;
        END fn_tbs_remaps_for_link; --function fn_tbs_remaps_for_link
    

    ——————–

    PROCEDURE p_imp_schema_vialink2(p_schema_name IN VARCHAR2, -- schema to load
                                                                 p_remote_link IN VARCHAR2 -- DB Link to source database
                                                                                          )
        IS
            /*
    
    
                Purpose: Import a schema - This version automatically remaps the tablespaces
                based on either a default value or a mapping in a table (see fn_tbs_remaps_for_link for details)
    
                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
            v_message VARCHAR2(4000); -- error message to be raised
            v_job_name VARCHAR2(4000); -- job name created
            v_return NUMBER; -- function return error code
        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
            v_job_name := vc_operation || '_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI_SS');
    
            BEGIN
                v_handle :=
                    DBMS_DATAPUMP.open(operation => vc_operation,
                                       job_mode => vc_job_mode,
                                       remote_link => p_remote_link,
                                       job_name => v_job_name,
                                       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 := 500; -- debug line no
            -- Filter for schema
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
            v_line_no := 600; -- debug line no
    
            -- Remap tablespace if needed - using function
            v_return := oradba.util_imp_exp.fn_tbs_remaps_for_link(p_remote_link => p_remote_link, p_handle => v_handle, p_schema_name => p_schema_name);
    
            -- check for errors
            IF v_return < 0
            THEN
                v_message := 'Tablespace Remaps failed for remote link: ' || p_remote_link || ' Job Name:' || v_job_name;
                raise_application_error(-20001, v_message);
            END IF;
    
            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_vialink2; -- procedure p_imp_schema_vialink2
    

    Oracle Datapump PL/SQL Import Table Example

    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
    /
    
    --------------------------------------
    
    PROCEDURE p_imp_table(p_schema_name IN VARCHAR2, -- schema for talbe
                                                    p_table_name IN VARCHAR2, -- table to load
                                                                             p_dumpfile_name IN VARCHAR2 -- dumpfile_name to load
                                                                                                        )
    IS
        /*
    
    
            Purpose: Import a table
    
            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) := 'TABLE'; -- 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 := 600; -- debug line no
        -- Filter for table
        DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_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_table; -- Procedure p_imp_table
    

    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
    /
    

    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
    

    Datapump PL/SQL Example for Job Status Monitoring

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

    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
    )

    I’m sure you some of you could take this and remove all, but percent complete. Although some of you will want it all.

    create 
    PROCEDURE p_job_status(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_status   IN OUT BOOLEAN, -- true when job status has data
                               p_operation         IN OUT VARCHAR2, -- operation
                               p_job_mode          IN OUT VARCHAR2, -- job_mode
                               p_bytes_processed   IN OUT NUMBER, -- bytes_processed
                               p_total_bytes       IN OUT NUMBER, -- total_bytes
                               p_percent_done      IN OUT NUMBER, -- percent_done
                               p_degree            IN OUT NUMBER, -- degree
                               p_error_count       IN OUT NUMBER, -- error_count
                               p_state             IN OUT VARCHAR2, -- state
                               p_phase             IN OUT NUMBER, -- phase
                               p_restart_count     IN OUT NUMBER, -- restart_count
                               p_Worker_Status_list in out ku$_WorkerStatusList1120, -- worker status list
                               p_file_list in out  ku$_DumpFileSet1010 -- file list
                                                                )
        IS
            /*
    
                       Purpose: attaches and returns the job 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_status,
                                     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_status) != 0
            THEN
                p_bool_job_status := TRUE;
            ELSE
                p_bool_job_status := FALSE;
            END IF;
    
            -- Detach from handle
            BEGIN
                DBMS_DATAPUMP.detach(handle => v_handle);
            EXCEPTION
                WHEN OTHERS
                THEN
                    NULL;
            END;
    
            IF p_bool_job_status
            THEN
                -- Return data
                p_operation := v_status.job_status.operation; -- operation
                p_job_mode := v_status.job_status.job_mode; -- job_mode
                p_bytes_processed := v_status.job_status.bytes_processed; -- bytes_processed
                p_total_bytes := v_status.job_status.total_bytes; -- total_bytes
                p_percent_done := v_status.job_status.percent_done; -- percent_done
                p_degree := v_status.job_status.degree; -- degree
                p_error_count := v_status.job_status.error_count; -- error_count
                p_state := v_status.job_status.state; -- state
                p_phase := v_status.job_status.phase; -- phase
                p_restart_count := v_status.job_status.restart_count; -- restart_count
    
                -- Get Worker Status List
                p_Worker_Status_list := v_status.job_status.worker_status_list; -- worker status list
    
                -- Get dump Files List
                p_file_list := v_status.job_status.files; -- files list
    
    
            END IF;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_sqlcode := sqlcode;
                dbms_output.put_line(substr('Value of v_sqlcode='||to_char(v_sqlcode),1,255));
                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_status; -- end procedure p_job_status
        /
    

    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
    

    Example Datapump PL/SQL Script to Export a Table with a Where Clause

    Try this with the following parameters

    Schema: SCOTT
    TABLE: DEPT
    TABLE_FILTER: where deptno = '10'

    create PROCEDURE exp_tables_w_qfilter
    (
    p_schema_name IN VARCHAR2, -- schema name
    p_table_name IN VARCHAR2, -- source tables
    p_table_filter IN VARCHAR2 DEFAULT NULL -- table predicate to filter data (where clause)
    )
        IS
            /*
    
    
                Purpose: Export tables
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/15/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_line_no INTEGER := 0; -- debug line no
            v_sqlcode NUMBER; -- sqlcode
            v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
            vc_job_mode CONSTANT VARCHAR2(200) := 'TABLE'; -- Job mode
       
        BEGIN
            v_line_no := 100; -- debug line no
    
            -- Ceate the log and dumpfile names
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
            ELSE
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.dmp';
            END IF;
    
            v_line_no := 150; -- debug line no
    
            -- Open the job
            BEGIN
                v_handle :=
                    DBMS_DATAPUMP.open(operation => 'EXPORT',
                                       job_mode => vc_job_mode,
                                       job_name => 'EXPORT_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI'),
                                       version => v_compatible);
            EXCEPTION
                WHEN OTHERS
                THEN
                    DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
                    RAISE;
            END;
    
            v_line_no := 200; -- 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 => 'dp' || '_%U_' || v_dumpfile_name,
                                   directory => v_default_dir,
                                   filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
            v_line_no := 500; -- debug line no
    
    
            -- Filter for the schemma
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
            v_line_no := 550; -- debug line no
    
            --Filter for the table
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
    
            v_line_no := 570; -- debug line no
    
            -- Add a subquery
            DBMS_DATAPUMP.data_filter(handle => v_handle, name => 'SUBQUERY', VALUE => p_table_filter);
    
            v_line_no := 600; -- debug line no
    
            -- Get the start time
            v_start_time := SYSDATE;
    
            -- Add a start time to the log file
            DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
    
            v_line_no := 700; -- debug line no
            -- Start the job
            DBMS_DATAPUMP.start_job(handle => v_handle);
            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 exp_tables_w_qfilter; -- Procedure exp_tables_w_qfilter
        /
    

    Oracle Datapump PL/SQL Export of a Single Schema Example

    Example of exporting a single schema with Data Pump API:

    --------------------------------------------
        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
    
        ---------------
    
        PROCEDURE exp_schema(p_schema_name IN VARCHAR2 -- schema name
                                                      )
        IS
            /*
    
    
                Purpose: Export schema
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/15/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(200) := 'SCHEMA'; -- Job mode
            v_filter_name VARCHAR2(30); -- filter name
            v_filter_value NUMBER; -- filter value
        BEGIN
            v_line_no := 100; -- debug line no
    
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
            ELSE
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.dmp';
            END IF;
    
            -- Open the job
            BEGIN
                v_handle :=
                    DBMS_DATAPUMP.open(operation => 'EXPORT',
                                       job_mode => vc_job_mode,
                                       job_name => 'EXPORT_' || 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 := 200; -- debug line no
    
            -- Set the degree
            -- DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
    
            -- 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 => 'dp' || '_%U_' || v_dumpfile_name,
                                   directory => v_default_dir,
                                   filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
            v_line_no := 500; -- debug line no
    
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
            v_line_no := 600; -- debug line no
    
            -- Get the start time
            v_start_time := SYSDATE;
    
            v_line_no := 700; -- debug line no
    
            -- Add a start time to the log file
            DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
    
            v_line_no := 710; -- debug line no
            -- Start the job
            DBMS_DATAPUMP.start_job(handle => v_handle);
            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 exp_schema; -- Procedure exp_schema
    

    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
    

    Oracle Datapump a Single Table Export Example

    Example of datapump for a single table Export. These should go into a package.

    Don’t forget to change the html versions of “less than” and “greater than” back to the correct characters.

    Try varying the parallel parameter. I really like the SAMPLE parameter for sub-setting data.

        --------------------------------------------
        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
    
        -----------------------------------------------------
    
        PROCEDURE exp_table(p_schema_name    IN VARCHAR2, -- schema name
                            p_table_name     IN VARCHAR2, -- Table_name
                            p_filter_name    IN VARCHAR2 DEFAULT NULL, -- Options NAME (VALUES) 'INCLUDE_ROWS' (0=No, non-zero=Yes),
                            -- OR 'SAMPLE' (% of rows to sample) - specify a value below
                            p_filter_value   IN NUMBER DEFAULT NULL -- included value corresponding to filter name from p_filter_name
                                                                   )
        IS
            /*
    
    
                Purpose: Export tables
    
                MODIFICATION HISTORY
                Person      Date        Comments
                ---------   ------      -------------------------------------------
                dcox        2/15/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(200) := 'TABLE'; -- Job mode
            v_filter_name VARCHAR2(30) := p_filter_name; -- filter name
            v_filter_value NUMBER := p_filter_value; -- filter value
        BEGIN
            v_line_no := 100; -- debug line no
    
            IF v_compatible = 'COMPATIBLE'
            THEN
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
            ELSE
                v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
                v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.dmp';
            END IF;
    
            -- Open the job
            BEGIN
                v_handle :=
                    DBMS_DATAPUMP.open(operation => 'EXPORT',
                                       job_mode => vc_job_mode,
                                       job_name => 'EXPORT_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI'),
                                       version => v_compatible);
            EXCEPTION
                WHEN OTHERS
                THEN
                    DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
                    RAISE;
            END;
    
            v_line_no := 200; -- debug line no
    
            -- Set the degree
            -- DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
    
            -- 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 => 'dp' || '_%U_' || v_dumpfile_name,
                                   directory => v_default_dir,
                                   filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
            v_line_no := 500; -- debug line no
    
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
            v_line_no := 600; -- debug line no
            DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
    
            v_line_no := 700; -- debug line no
            -- filter for table name
            v_filter_name := NVL(v_filter_name, 'INCLUDE_ROWS');
            v_filter_value := NVL(v_filter_value, '100');
            DBMS_DATAPUMP.data_filter(handle => v_handle,
                                      name => v_filter_name,
                                      VALUE => v_filter_value,
                                      table_name => p_table_name,
                                      schema_name => p_schema_name);
    
            v_line_no := 600; -- debug line no
            -- Get the start time
            v_start_time := SYSDATE;
            -- Add a start time to the log file
            DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
    
            v_line_no := 700; -- debug line no
            -- Start the job
            DBMS_DATAPUMP.start_job(handle => v_handle);
            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 exp_table; -- Procedure exp_table