On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, September 2, 2008

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

No comments:

Post a Comment