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