Jobname Check for Data Pump

rem +--------------------------------------------------------------------------+
rem | Script ID:     ckjob.sql
rem |
rem | Purpose:       Check the job name to see if a table exists with that name
rem |                before Data Pump job is run
rem |              
rem | Developer:     Lynn Tobias
rem | Script Date:   9/2/2008
rem | Oracle Ver:    10g
rem |
rem | Table(s) Used: All_Catalog
rem |             
rem | Variables:     Schema JobName Ctr OnOff
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem |             
rem +--------------------------------------------------------------------------+
Define OnOff = Off
Clear Screen
Set Echo &OnOff Term On

Prompt +--------------------------------------------------------------------------+
Prompt | This will check your Data Pump jobname against the schema.               |
Prompt | Since Data Pump creates a table with this name, it cannot already exist. |
Prompt +--------------------------------------------------------------------------+
Prompt
--+-----------------------------------------------------------------------------
--| Ask them for schema (if the current it will be equal to _User) and jobname
--+-----------------------------------------------------------------------------
Accept Schema Default &_User Prompt 'Type Schema or press enter for default (&_user.): '
Prompt
Accept JobName               Prompt 'Your jobname  : '

--+-----------------------------------------------------------------------------
--| Turn off the display and get the count of tables like the job name
--+-----------------------------------------------------------------------------
Set Term &OnOff
Column Ctr New_Value Ctr

Select Count(*) Ctr
 From All_Catalog
Where Table_Name = Upper('&JobName')
  And Owner = '&Schema';

--+-----------------------------------------------------------------------------
--| Based on the count, display an error or not
--+-----------------------------------------------------------------------------
Set Pagesize 50 Term On Heading &OnOff Feedback &OnOff

Ttitle Left 'Results of jobname check: ' Skip 1 -
           '------------------------- ' Skip 2

Select Case when &Ctr > 0
           Then 'Error: A jobname can''t be the name of a table or view in the schema'
           Else 'JobName is ok to use.'
       End
 From Dual;
Ttitle off
Undefine Schema JobName Ctr