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.

Sunday, June 22, 2008

Cleaning up Defines


If you've ever had problems with code and realized a defined variable had not been cleaned up, this may come in handy. I realize that you should undefine things at the bottom of a query, but sometimes in the rush, this gets left out.

First an external table is created with the following code:
Drop Table Defines;

Create Table Defines
(Def_Var Varchar2(250)
)
Organization External
(Type Oracle_Loader
Default Directory Ext
Access Parameters
(Records Delimited By Newline)
Location (Ext:'defines.txt')
);
After that is done one time, the following query can be run at any time to clear any defines left behind. The spooled file will be read as a table to generate a list of defined names.
--+-------------------------------------------------------------------+
-- Send output from Define to a spool file that is set up as an
-- external table
--+-------------------------------------------------------------------+

Set Term Off

Spool Defines.Txt
Define
Spool Off

--+-------------------------------------------------------------------+
-- Get a list of the defines that need to be cleared and write that to
-- a file with a SQL extension to be executed.
--+-------------------------------------------------------------------+

Set Pagesize 0
Set Echo Off

Spool Undef.SQL

Select 'Undefine '||
------------------------------------------------------------------------
-- Pick up the word between 'DEFINE' and '='
------------------------------------------------------------------------
Substr(
Def_Var, -- search this string
8, -- start at this position
-----------------------------------------------
-- for this length of Instr -
-- (Instr is counting for one so subtract 8 -
-- from the results.) -
-----------------------------------------------
(
Instr(
Def_Var, -- search this string
'=' -- for this substring
)
-8
)
-----------------------------------------------
)
As
SubVar
------------------------------------------------------------------------
From Defines
Where Substr(Def_Var,8,1) <> '_' -- don't include system variables
And Substr(Def_Var,1,6) = 'DEFINE' -- ignore lines not DEFINEs
;
Spool Off

@Undef

Set Term On


Notes on Instr and Substr: INSTR returns the position of a particular occurrence of a substring in a string. SUBSTR returns certain characters from the string.

Instr(
Search_This_String,
For_This_Substring,
Begin_Search_At_Starting_Pos,
Look_For_This_Occurrence
)

Substr(
Search_This_String,
Start_From_This_Position,
For_Optional_Length
)
See Oracle documentation for further detail:
External Tables Concepts
Substr

No comments:

Post a Comment