Simple Permissions Example for ACL and Network Test

Here a simple example for granting the permissions to all hosts and all ports for a user. Then below is a function to test the network connection.

For scanning ports see: Scanning Ports Example

DECLARE
    p_user VARCHAR2 ( 30 ) := 'DCOX'; -- user to receive grants
BEGIN

    DBMS_NETWORK_ACL_ADMIN.create_acl (
                                        acl => 'everything.xml',
                                        description => 'Full Access',
                                        principal => UPPER ( p_user ),
                                        is_grant => TRUE,
                                        privilege => 'connect',
                                        start_date => SYSDATE - 1,
                                        end_date => NULL
                                       );
    DBMS_NETWORK_ACL_ADMIN.add_privilege (
                                           acl => 'everything.xml',
                                           principal => UPPER ( p_user ),
                                           is_grant => TRUE,
                                           privilege => 'resolve',
                                           start_date => SYSDATE - 1,
                                           end_date => NULL
                                          );

    DBMS_NETWORK_ACL_ADMIN.assign_acl (
                                        acl => 'everything.xml',
                                        HOST => '*', -- All hosts and ports
                                        lower_port => 1,
                                        upper_port => 65535
                                       );

    COMMIT;
END;
/

Function to test a single port:

create or replace FUNCTION fn_check_network
(p_remote_host in varchar2, -- host name
 p_port_no in integer default 1521 -- port number
 )
  RETURN  number IS
/*

    Purpose: Check Network Connection to tns port

    MODIFICATION HISTORY
    Person      Date        Comments
    ---------   ------      -------------------------------------------
    dcox        8/19/2013    Initial Build

*/
    v_connection   utl_tcp.connection; -- tcp connection
BEGIN

    v_connection := utl_tcp.open_connection(REMOTE_HOST=>p_remote_host, REMOTE_PORT=>p_port_no, IN_BUFFER_SIZE=>1024, OUT_BUFFER_SIZE=>1024,   TX_TIMEOUT=>5);
    RETURN 0 ;
EXCEPTION
   WHEN others THEN
       return sqlcode ;
END fn_check_network;   -- Function fn_check_network
/

Don’t forget to grant UTL_TCP to the user where you create the function. And if you add the following function grant execute on DBMS_NETWORK_ACL_ADMIN to that user too.

More complicated, but easier to maintain:

CREATE OR REPLACE FUNCTION dcox.fn_grant_acl_permission ( p_user IN VARCHAR2 -- username to grant acl permissions
                                                                             )
    RETURN NUMBER
IS
BEGIN

    DBMS_NETWORK_ACL_ADMIN.create_acl (
                                        acl => p_user || '_everything.xml',
                                        description => 'Full Access',
                                        principal => UPPER ( p_user ),
                                        is_grant => TRUE,
                                        privilege => 'connect',
                                        start_date => SYSDATE - 1,
                                        end_date => NULL
                                       );
    DBMS_NETWORK_ACL_ADMIN.add_privilege (
                                           acl => p_user || '_everything.xml',
                                           principal => UPPER ( p_user ),
                                           is_grant => TRUE,
                                           privilege => 'resolve',
                                           start_date => SYSDATE - 1,
                                           end_date => NULL
                                          );

    DBMS_NETWORK_ACL_ADMIN.assign_acl (
                                        acl => p_user || '_everything.xml',
                                        HOST => '*', -- All hosts and ports
                                        lower_port => 1,
                                        upper_port => 65535
                                       );

    COMMIT;
    RETURN 0; -- success
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN SQLCODE;
END fn_grant_acl_permission; -- Function fn_grant_acl_permission
/


I like Violin plots in R

I like the Violin plot and here’s one compared to a box plot.  It’s a box plot and a Kernel Density plot together.

VIOLINPLOT_Example Violin PlotBOXPLOT_exampleBox Plot

The R code to produce it is below.

Read more on the wiki Wiki Violin Plots

I like these sites too. R-Bloggers Vioplots with Clipping, Box Plots Reference

> x2 <- racot$ELAPSED_SECONDS[racot$THREADS==2 & racot$TEST_CORES==2]
> x4 <- racot$ELAPSED_SECONDS[racot$THREADS==4 & racot$TEST_CORES==2]
> x8 <- racot$ELAPSED_SECONDS[racot$THREADS==8 & racot$TEST_CORES==2]
> x16 <- racot$ELAPSED_SECONDS[racot$THREADS==16 & racot$TEST_CORES==2]
> vioplot(x2,x4,x8,x16,names=c("2","4","8","16"),col="Blue")
> title('RAC ONE - 2 Cores per Proc (4 Total)',xlab="Long Threads",ylab="Elapsed Seconds")

Oracle XE – Uninstall causes Windows Event Log to Stop

I’ve seen this a couple of times now.  After uninstalling Oracle XE the Windows 7 eventlog service stops (Windows Event Log).  Before anyone blames Oracle alone on this I’ve found similar posts with the same issue on installing/deinstalling Sql Server.

Here’s the best fix I’ve found, and I’m posting this as much for myself as anyone else:

Hope this helps.

The main issue I’ve found was correct by changing the permissions on the folder and items in the folder %WINDIR%\system32\winevt\Logs.

Quick SQL Load Testin’

Here’s a quick and simple load testing script. Add you own base.sql set of scripts to run and a sub-directory called output and you’ll be ready. More examples later.

base.sql file:

select 'x' from dual;
select count(*) from dba_objects where status != 'VALID';
select count(*) from dba_objects;

login.sql file:

set arraysize 5000
set autocommit OFF
set autoprint OFF
set autorecovery OFF
set autotrace OFF
set echo ON
set editfile "afiedt.buf"
set embedded OFF
set escape OFF
set escchar OFF
set exitcommit ON
set flagger OFF
set flush ON
set heading ON
set linesize 300
set long 300
set longchunksize 80
set newpage 1
set null ""
set numformat ""
set numwidth 40
set pagesize 0
set PAUSE OFF
set recsep WRAP
set serveroutput ON
set shiftinout INVISIBLE
set showmode OFF
set sqlblanklines OFF
set sqlcase MIXED
set sqlcontinue "> "
set sqlnumber ON
set sqlpluscompatibility 11.2.0
set sqlprompt "Default SQL> "
set suffix "sql"
set tab ON
set termout ON
set timing ON
set trimout ON
set trimspool OFF
set verify ON
set wrap off
set xmloptimizationcheck OFF
set errorlogging OFF

Exit.sql file:

exit

Korn Shell script start_load.ksh expects a sub-directory called output and testuser1 through testuser with a password you specify (or you could use the same account over and over). Permissions are up to you.

#!/usr/bin/ksh

# Environmental Variables

set -x
# Mail
RUN_MAIL_TO='david.cox@yourcompany.com'; export RUN_MAIL_TO

# Oracle
ORACLE_BASE=/u01/app/oracle

# Logging and Working Files
LOG=/home/oracle/log/load_test.$(date '+%Y%m%d.%H%M%S').log
WORKING_DIR=/home/oracle/scripts/ksh/odaperf
WORKING_DIR_OUTPUT=/home/oracle/scripts/ksh/odaperf/output
BASE_SCRIPT=$WORKING_DIR/base.sql

export LOG WORKING_DIR WORKING_DIR_OUTPUT BASE_SCRIPT

# Process
PROCESS_NAME="11g Oracle Load Test"
NO_OF_PARALLEL_SESSIONS=10 # Min is 2 or larger
SQL_TIMES=3
RAMP_UP_TIME=21
((SESSION_DELAY=NO_OF_PARALLEL_SESSIONS-1))
((SESSION_DELAY=$RAMP_UP_TIME / $SESSION_DELAY))
export PROCESS_NAME NO_OF_PARALLEL_SESSIONS SQL_TIMES RAMP_UP_TIME SESSION_DELAY

echo "Starting ${PROCESS_NAME} at `date` on `hostname` \n" >>$LOG

echo "Session Delay: ${SESSION_DELAY} /n"

# Build Script File for SQL_TIMES Iterations *********************
#
# Loop through NO_OF_PARALLEL_SESSIONS and run sql SQL_TIMES
# This means that there will be NO_OF_PARALLEL_SESSIONS to simulate sessions 
# and the base sql script will be run SQL_TIMES for each session started
#
# To do this Let's create a script from the base  that is the same as the base script run SQL_TIMES
# Let's delete copies already created in case the base script has been modified 
# Note all change should be made to the base test script

nLoopIteration=1
# Remove existing concatonates SQL SCRIPT FILE of the form base_x<no of iterations>.sql 
RUN_BASE=base_x${SQL_TIMES}
export RUN_BASE

rm ${RUN_BASE}.sql
# rebuild concatonated sql script file
while [ $nLoopIteration -le ${SQL_TIMES} ] 
do 
	cat ${BASE_SCRIPT} >> ${RUN_BASE}.sql # Append
	((nLoopIteration=nLoopIteration + 1))
done

echo "Built run script $ at `date`\n"

# Now that our special base script is created, let's start the processes with no-hup
# We can add in a ramp up delay rounded to the narest second -- ramp_up_time
# So our delay between each session start is sess_delay = ramp_up_time / (NO_OF_PARALLEL_SESSIONS-1)
# Assumption: we start a session - then sleep between sessions so the last session starts up at end of ramp_up_time period roughly
# zero time for kick off on each session is assumed (not completely true) and no program time - 
# So this means ramp up time is a rough estimate

nLoopIterations=1
while [ $nLoopIterations -le $NO_OF_PARALLEL_SESSIONS ]
do
        # Create spool command
	echo "spool ${WORKING_DIR_OUTPUT}/$(date '+%Y%m%d.%H%M%S')_${RUN_BASE}_p${nLoopIterations}.out" > ${WORKING_DIR}/${RUN_BASE}_p${nLoopIterations}.spooltext # overwrite spool text file
	# Create full sql file
	cat ${WORKING_DIR}/${RUN_BASE}_p${nLoopIterations}.spooltext ${WORKING_DIR}/login.sql ${WORKING_DIR}/${RUN_BASE}.sql exit.sql > ${WORKING_DIR}/${RUN_BASE}_p${nLoopIterations}.sql
	nohup $ORACLE_HOME/bin/sqlplus -s testuser${nLoopIterations}/PasswordGoesHere @${WORKING_DIR}/${RUN_BASE}_p${nLoopIterations}.sql  > ${WORKING_DIR_OUTPUT}/${RUN_BASE}_p${nLoopIterations}.nohupout 2>&1 &
        ((nLoopIterations=nLoopIterations + 1))
        sleep $SESSION_DELAY
done

echo "All nohup job started at `date`/n"


echo "Startup Initiated of ${PROCESS_NAME} at `date` on `hostname` /n" >>$LOG
mailx -s "${PROCESS_NAME} at `date` on `hostname` STARTED" ${RUN_MAIL_TO} < $LOG

exit 0

Oracle XE Support – helpful note

I was looking for Oracle support and got a response from oracle like the following. This was very helpful for me, hope it helps someone else.

Oracle Express Edition (XE) is not supported by normal Oracle Support Services because it is distributed at no charge.

The support for Oracle XE is done via a forum on OTN for which you have to register first.
This is documented in the FAQ accessible via
http://www.oracle.com/technology/products/database/xe/pdf/dbxe_faq.pdf
or
http://www.oracle.com/technetwork/products/express-edition/overview/index.html

Please refer to the next statement from above references:
“Support is provided through a free Oracle Discussion Forum monitored by Oracle employees as well as community experts.”

You can refer the below note for more details.
Where To Get Support For Oracle Express Edition (XE) ? (Doc ID 352768.1)

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=&gt;1,
     p_completion_code=&gt;p_completion_code,
     p_complete_status=&gt;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.