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.

Thursday, March 12, 2009

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

No comments:

Post a Comment