Oracle Exceptions found in DBA_Source

rem +--------------------------------------------------------------------------+
rem | Script ID:     Exceptions.sql
rem |
rem | Purpose:       Display a list of exception numbers assigned by Oracle.
rem |               
rem | Developer:     Lynn Tobias
rem | Script Date:   3/12/2009
rem | Oracle Ver:    10g
rem |
rem | Table(s) Used: DBA_Source
rem |               
rem | Output:        Exceptions.Txt
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem +--------------------------------------------------------------------------+
Clear Screen
Column Exception Format A30     Heading 'Exception Name'
Column Name      Format A30     Heading 'DBA_Source Name'
Column Errnum    Format 9999999 Heading 'Exception'
Spool Exceptions.Txt

Select
       --- 1 ---------------------------------------------------------------------------------------
       -- 1.1 Replace:       There were so many variations that to make the regexp simpler, I 
       --                    removed all the spaces.
       -- 1.2 Replace(2):    I also removed the quotes from the few that used those on the numbers.
       -- 1.3 Regexp_Substr: Look for a string starting with a comma, then one or more of anything
       --                    and ending with a closed parenthesis.
       -- 1.4 Substr:        To remove the leading comma and the trailing parenthesis, start the 
       --                    substring at position two, and go for a length of the string minus 2.
       -- 1.5 To_Number:     Convert it to a number so it will sort correctly.
       ---------------------------------------------------------------------------------------------
               To_Number(
                         Substr(
                                Regexp_Substr(
                                              Replace(
                                                      Replace(Text,' ',Null)
                                                     ,'''',Null
                                                     )
                                              ,',.{1,}\)'
                                             ) 
                               ,2
                               ,Length(Regexp_Substr(Replace
                                        (Replace(Text,' ',Null)
                                        ,'''',Null),',.{1,}\)')
                                      )
                                      -2
                               )
                        ) 
               As
       Errnum
       --- 2 ---------------------------------------------------------------------------------------
       -- This is the name field from DBA_Source
       ---------------------------------------------------------------------------------------------
     ,         Initcap(Name)
               As
       Name      
       --- 3 ---------------------------------------------------------------------------------------
       -- 3.1 RegExp_Substr: Find the text starting with a open parenthesis for one or more letters 
       --                    and ending with a comma.
       -- 3.2 Substr:        Drop '(' and ',' from string by substringing starting at position 2, 
       --                    and going for a length of the original string minus 2.
       -- 3.3 InitCap:       Make it consistent and more readable
       ---------------------------------------------------------------------------------------------
     ,         InitCap(
                       Substr(
                              Regexp_Substr(
                                            Text
                                           ,'\(.{1,}\,'
                                           )
                             ,2
                             ,Length(Regexp_Substr(Text,'\(.{1,}\,'))
                              -2
                                    )
                      ) 
               As
       Exception
       ---------------------------------------------------------------------      
  From Dba_Source 
 Where Upper(Text) Like '%EXCEPTION_INIT%'
   And Text Not Like '--%'
 Order By Errnum Desc;

Prompt
Prompt Note: This list has been spooled to Exceptions.Txt