My Ah-ha! Moment with Self-Joins

I was recently taking an online class in SQL from Oracle. I will admit that sometimes self-joins still baffle my mind. So I decided to try what they said.

Oracle's statement was:

"To join a table to itself, the table is given two names or aliases. This will make the database “think” that there are two tables. Choose alias names that relate to the data's association with that table."

So I decided to create two EMP tables in the Scott schema with just the information I needed.

create table e_emp as select empno, ename, mgr from emp;

create table m_emp as select empno, ename, mgr from emp;

Now it's very easy to see that 'MGR' in the 'E' table will have to connect to 'EMPNO' in the 'M' table to get the report I want.

So I'll code a very basic query from these two separate tables with the aliases 'E' and 'M' and will end up with the report shown below:

Select e.empno as Emp#, e.ename as Employee

     , m.empno as Mgr#, m.ename as Manager

  from e_emp e

     , m_emp m

 where e.mgr = m.empno;

But Oracle doesn't make me create two tables. I can run the same query listing EMP twice and get the same thing. The only thing to do is change the two table names 'e_emp' and 'm_emp' both to 'emp.'

Select e.empno as Emp#, e.ename as Employee

     , m.empno as Mgr#, m.ename as Manager

  from emp e

     , emp m

 where e.mgr = m.empno;

SQL*Plus Output -> HTML

*I saw this on Uwe Hesse's blog, and it was too cool not to keep a copy where I knew I could find it again.  The only thing I added to his script was the 'Start' in front of 'Firefox' that's needed by Windows (and the comment about removing the 'l').

HTML.SQL:
set termout off

set markup HTML ON HEAD " -
 -
" -
BODY " " -
TABLE "border='1' align='center' summary='Script output'" -
SPOOL ON ENTMAP ON PREFORMAT OFF

spool myoutput.html

--------------------------------------------------------------------------
-- if you want your query to display in firefox, uncomment the 'l' below.
--------------------------------------------------------------------------
--l
/

spool off
set markup html off spool off
host start firefox myoutput.html
set termout on

Run your query in SQL+.

conn hr/hr
set linesize 2000 pagesize 60 feedback off

Select *
  From Employees
 Where Job_Id = 'SH_CLERK'
 Order by Last_Name;

Hmm.. this is a bit too much for the screen.  You can either start formatting with 'Column' or run HTML.SQL.

Beautiful! Thanks Uwe Hesse and Tanel Poder.

Flashback a Table, Index and Trigger Script

This script performs a flashback of tables, indexes and triggers. If the item already exists, it will be renamed with the base_object number.


1) After displaying a list of tables that are in the Recycle Bin, the script will ask for a table to be flashbacked.

2) If a table with that name already exists, a message is displayed stating that the base number will be added to the name.

3) A report is then displayed showing indexes and triggers that will also be reestablished.

4) The report labeled 4 shows the items recreated.

5) Lets you know what else might have to be done.

Click here for a downloadable attachment.

Define OnOff = Off
rem +--------------------------------------------------------------------------+
rem | Script ID:     FlashTbl.sql
rem |
rem | Purpose:       This performs a flashback of tables, indexes and triggers.
rem |                If the table already exists, it will be renamed with the
rem |                base_object number.
rem |
rem | Developer:     Lynn Tobias
rem | Script Date:   August 08, 2008
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: Dual, User_Recyclebin
rem |                
rem | Called by:     n/a
rem | Calls:         FlashObj.Sql (created in this script)
rem |
rem | Variables:     OnOff          - Used to debug
rem |                Dropped_Table  - Name of table to flashback
rem |                Ctr            - if table found in user_table then '1'
rem |                Renamed_Phrase - the Renamed_Phrase phrase of flashback 
rem |                                 if a table already exists
rem |                New_Name       - the name of the table with the base number
rem |                                 if Ctr=1 otherwise,original name
rem | Output:        n/a
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem |                
rem +--------------------------------------------------------------------------+

--Clear Screen

--+- 1 -------------------------------------------------------------------------
--| Show the dropped tables available for selection
--+-----------------------------------------------------------------------------

Set Heading On Newpage 2 Linesize 200 Pagesize 60 Term On
Define xSubHead = '    DROPPED TABLES IN USER_RECYCLEBIN'

Ttitle -
Left 'User: '  _user         col 68 'F L A S H B A C K' col 120 'Date:  ' _DATE  skip 1 -
Left 'Script: ' FlashTbl.Sql col 55 xSubHead            col 120 'Page: ' sql.pno skip 2

Column Object_Name    Heading Object|Name          Format A15 
Column Original_Name  Heading Original|Name        Format A30
Column Operation      Heading Oper-|ation          Format A5
Column Type           Heading Type                 Format A7
Column Ts_Name        Heading T(bl)|S(pc)|Name     Format A5
Column Createtime     Heading Create|Time          Format A10
Column Droptime       Heading Drop|Time            Format A10
Column Dropscn        Heading Drop|Scn             Format 9,999,999
Column Partition_Name Heading Part-|ition|Name     Format A5
Column Can_Undrop     Heading Can|Undrop           Format A6
Column Can_Purge      Heading Can|Purge            Format A5
Column Related        Heading Related              Format 99,999
Column Base_Object    Heading Base|Object          Format 99,999
Column Purge_Object   Heading Purge|Object         Format 99,999
Column Space          Heading Space|(Blocks)       Format 999

Select Object_Name,  Original_Name, Operation,   Type,           Ts_Name,    
       Createtime,   Droptime,      Dropscn,     Partition_Name, Can_Undrop, 
       Can_Purge,    Related,       Base_Object, Purge_Object,   Space
  From User_Recyclebin
 Where Type = 'TABLE'
 Order By Original_Name;

Ttitle Off

--+- 2 -------------------------------------------------------------------------
--| Ask for a table to flashback
--+-----------------------------------------------------------------------------

Accept Dropped_Table Prompt 'Name of table to flashback: '

--+- 3 -------------------------------------------------------------------------
--| If that tablename already exists, CTR is set to '1'. Post a message.
--+-----------------------------------------------------------------------------

Set Heading Off NewPage 1
Column Ctr New_Value Ctr NoPrint 

Select Case When Count(*) = 1
            Then 'Note: A table with that name exists. This will be renamed with base#.'
            Else ' '
        End ,
      --------------------------------------------------------------------------
      Count(*) As Ctr
      --------------------------------------------------------------------------
 From User_Tables
Where Table_Name = Upper('&Dropped_Table')
/
Prompt
--+- 4 ------------------------------------------------------------------------
--| Pull everything that needs to be fixed (table, indexes, triggers).
--| Also, determine the new name if a liked-name table already exists.
--+-----------------------------------------------------------------------------

Create Global Temporary Table GT_Flash
  On Commit Preserve Rows
  As
--------------------------------------------------
Select urb.*,  
       --------------------------------------------- 
               Case When &Ctr = 1 
                    Then Substr(
                                Original_Name||'_'||Base_Object
                               ,1,30
                                )
                    Else Original_Name
                End 
                As 
       New_Name
  From User_Recyclebin urb
 Where Base_Object = 
                    --+- 4.1 ------------------------------------------------
                    --| Get the latest base# where it's the table requested
                    --| This is necessary as there may be more than one table
                    --| with the name selected.
                    --+------------------------------------------------------
                   (
                    Select Max(Base_Object)
                      From User_Recyclebin
                     Where Original_Name  = Upper('&Dropped_Table')
                   );

--+- 5 -------------------------------------------------------------------------
--| If Ctr = 1, build the 'rename' portion of the flashback statement. 
--+-----------------------------------------------------------------------------

Set Term &OnOff
Column Renamed_Phrase New_Value Renamed_Phrase

Select          Case When &Ctr = 1 
                     Then 'Rename To '||New_Name
                     Else ' '
                  End 
                  As 
       Renamed_Phrase
  From GT_Flash
 Where Original_Name  = Upper('&Dropped_Table');

Set Term On

--+- 6 ------------------------------------------------------------------------
--| Display the other objects (Triggers, Indexes)
--+-----------------------------------------------------------------------------

Set Newpage 2
Ttitle On
Define xSubHead = 'ITEMS THAT WILL BE REESTABLISHED AFTER FLASHBACK'
Set Heading On

Select *
  From GT_Flash
 Where Original_Name != Upper('&Dropped_Table')  -- everything but the table
;

--+- 7 -------------------------------------------------------------------------
--| Build the statements to rebuild indexes and triggers. Get the Base_Object 
--| since there can be more than one with that name. (Base_Object (and RELATED) 
--| will match across tables, indexes and triggers.)
--|
--| Examples:
--| ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
--| ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;
--+-----------------------------------------------------------------------------

Spool FlashObj.Sql

Ttitle Off
Set Heading &OnOff Feedback &OnOff Verify &OnOff Term &OnOff

Select 'Alter ' || Type || ' "' || Object_Name || '" Rename To ' ||New_Name ||' ;'
  From GT_Flash
 Where Original_Name != Upper('&Dropped_Table') -- everything but the table
;

Spool Off

--+- 8 -------------------------------------------------------------------------
--| Flashback the table
--+-----------------------------------------------------------------------------

Flashback Table &Dropped_Table To Before Drop &Renamed_Phrase
/
--+- 9 -------------------------------------------------------------------------
--| Bring back indexes and triggers
--+-----------------------------------------------------------------------------

@FlashObj

--+- 9 -------------------------------------------------------------------------
--| Show them anything in User_Objects that is also in our global table
--+-----------------------------------------------------------------------------

Set Term On Heading On

Ttitle On
Column Object_Name Format A30

Define xSubHead = 'ITEM(S) RECOVERED AND NOW FOUND IN USER_OBJECTS';

Select Object_Name, Object_Type, Created, Status
  From User_Objects
 Where Object_Name In 
                     --+- 9.1 --------------------------------------------------
                     --| Find out if there is an active table with that name
                     --+--------------------------------------------------------
                     (
                     Select New_Name
                       From GT_Flash
                     )
;

--+- 10 ------------------------------------------------------------------------
--| Now bring the bad news...
--+-----------------------------------------------------------------------------

Prompt
Prompt Follow-up:
Prompt =========
Prompt Views and procedures defined on the table are not recompiled and remain in the invalid state. -
These old names must be retrieved manually and applied.
Prompt
Prompt Dropped bitmap indexes are not placed in the recycle bin. Constraint names are also not retrievable.
Prompt

--+- 10 ------------------------------------------------------------------------
--| Clean up
--+-----------------------------------------------------------------------------

Truncate Table GT_Flash;
   Drop Table GT_Flash;

Undefine Dropped_Table Ctr Renamed_Phrase New_Name    
Ttitle Off

Documenting SQL Queries

(This is an update to a previously posted query.) This code will help document a SQL query as it looks through that code to find the list of tables, columns, amper-variables, and other called queries referenced. I added the following to the older version: called queries, a reference to the query name in the spooled file, and also to the spool filename.

There are some limitations in this code:

• It does not handle 'Select *'. You'll just have to search for these for now -- maybe fixed in the future.

• It only ignores comments that begin in column 1, therefore, if your comment starting in column 30 said "Deleted Dept_No," your report would show that name. (A future fix.)

As shown in the following example, the SomeQuery.SQL (on the left) generates the report on the right.








This code was long enough that I've stored it as 'NamesInCode.SQL'. You'll have to scroll down the page to see the file, and download button.

Generate Code to Create a CSV File

I was continually reworking the code to create CSV files for a particular application, so I wrote a query to do it for me. (It is long enough that I stored it as an attachment on the following page Cr_Csv.Sql. You'll have to scroll to the bottom to see the attachment.)

It will read through the User_Tab_Columns table, and pull all columns (except those in tables in the recycle bin). The data is dumped in column_id order. The first row will be the column_names. After that, each row of data is converted to the CSV format.

You will need to modify the code in the 'With' clause if you want other tables (or a select few). You may also need to add additional data types that may contain a comma as I'm only covering 'DATE', and 'VARCHAR2'.

Also, if your column_names could be longer than 1000 characters you will want to bump that so there is no wrapping. This is currently set to be one long literal with commas between each name (called Headings).

After the code is generated, it is automatically run. You will see a list of each table as it is processed:

If you looked at the output under notepad, it would look something like the following:
And in Excel or Quattro Pro, you would see this:








If you have either success or problems with this code, I'd appreciate it if you would email me. Enjoy.

11g: Mixed Name + Positional Notations

Changes with 11g:

• you can mix named with positional parameters in a call.  
• the SQL code matches the PL/SQL code.

Notes:

• Exclusionary Notation: one or more can be excluded if optional. (When formal parameters have defaults they are optional.)
• You still can't call a function from SQL when any of its formal parameters are defined as IN OUT or OUT mode–only variables. 
• Mixed must list all positional parameters first.

CREATE OR REPLACE FUNCTION Family
     ( Boy Varchar2  := 'Mickey'
     , Girl Varchar2 := 'Minney'
     , Dog Varchar2  := 'Pluto' ) 
                       RETURN Varchar2 IS
BEGIN
     RETURN Boy||'+'||Girl||'+'||Dog;
END;
/

Column Notation Format A15
Column Family   Format A15

Select 'Positional'           As Notation,
       Family('Dick'
             ,'Jane'
             ,'Spot')         As Family
From Dual;

Select 'Named'                As Notation,
       Family(Girl => 'Jane'
             ,Dog  => 'Spot'
             ,Boy  => 'Dick') As Family
From Dual;

Select 'Mixed'                As Notation,
       Family(        'Dick'
             ,Dog  => 'Spot'
             ,Girl => 'Jane') As Family
From Dual;

Select 'Exclusionary'         As Notation,
       Family(        'Dick'
             ,Girl => 'Jane') As Family
From Dual;

11g: SIMPLE_INTEGER is faster than PLS_INTEGER

Reading the improvements on 11g, I came across these new data types. They have NOT NULL constraints and should be faster.

• SIMPLE_INTEGER - based on PLS_INTEGER
• SIMPLE_FLOAT  - based on BINARY_FLOAT
• SIMPLE_DOUBLE - based on BINARY_DOUBLE.

With a basic time test, I did find SIMPLE_INTEGER to be faster, and I don't know where I go wrong on the others, but they're are actually running slower for me.  Also, I'm suppose to see a difference if  plsql_code_type is native instead of interpreted.  At least with this test, it was incredible minor.  Anyway, here's some code you can copy, and test with.  If you know why they're not all faster, please let me know.

ALTER SESSION SET plsql_code_type = 'NATIVE'; -- or INTERPRETED

SET SERVEROUTPUT ON
DECLARE
    v_pls_integer    PLS_INTEGER    := 0;
    v_simple_integer SIMPLE_INTEGER := 0;
    v_start          NUMBER;
    v_end            NUMBER;
    v_pls_time       NUMBER;
    v_simple_time    NUMBER;
    v_loops          NUMBER         := 90000000;
BEGIN
    ----------------------------------------------------------
    -- SIMPLE_INTEGER test (New datatype)
    ----------------------------------------------------------
    v_start := DBMS_UTILITY.GET_TIME;
    
    FOR i in 1..v_loops LOOP
        v_simple_integer := v_simple_integer + 1;
    END LOOP;
    
    v_end         := DBMS_UTILITY.GET_TIME;
    v_simple_time := v_end - v_start;
    
    DBMS_OUTPUT.PUT_LINE ( 'The new Simple_Integer took '
                        || (v_end - v_start)
                        || q'[ 100th's of a second.]');
    ----------------------------------------------------------
    -- PLS_INTEGER TEST (Old datatype)
    ----------------------------------------------------------
    v_start := DBMS_UTILITY.GET_TIME;
    
    FOR i in 1..v_loops LOOP
        v_pls_integer := v_pls_integer + 1;
    END LOOP;
    
    v_end      := DBMS_UTILITY.GET_TIME;
    v_pls_time := v_end - v_start;
    
    DBMS_OUTPUT.PUT_LINE ( 'The old Pls_Integer took    '
                        || (v_end - v_start)
                        || q'[ 100th's of a second.]');
    ----------------------------------------------------------
    -- Calculate 
    ----------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE ( 'With'
                        || To_char(v_loops,'99,999,999')
                        || ' iterations, there was a '
                        || Round(100 - v_Simple_Time/v_Pls_Time * 100,1)
                        ||'% improvement.');
END;
/

Tiny URL: http://tinyurl.com/simpleinteger

Writing Generated Code with Q-Quotes

Q-quotes have just made generating code too easy. It used to take several tries for me to get it right, but no more! I'm usually generating code because I want to pull some different values from tables (i.e., table_name).

Here are the steps:

1) Write out a sample of the code you want generated. Obviously, you'll want to run it first to make sure you're getting the final results needed.

Select 'Table Name: '||'SALGRADE', 'Row Count: '||Count(*) From SALGRADE;

2) Break down the query into literal, and variable parts. In this example, in two different places, I want the actual table name from User_Tables to be inserted into the code.

Select 'Table Name: '||'-- the ending quote prefaces the following table_name
SALGRADE
', 'Row Count: '||Count(*) From -- the beginning quote closes the table_name
SALGRADE
;

3) Change the sample table name (SALGRADE) to the actual column name (Table_Name), and add the concatenation bars on the front, and back.

Select 'Table Name: '||'
||table_name||
', 'Row Count: '||Count(*) From
||table_name||
;

4) Add q'[ and ]' to the front and end of each literal line. (As you may know, it can be other things than square brackets [].) On the last literal of a semicolon, I just stuck with the regular single quotes since there were no embedded quotes on that line.

q'[Select 'Table Name: '||']'
||table_name||
q'[', 'Row Count: '||Count(*) From ]'
||table_name||
';'

5) Now add a 'SELECT' at the beginning, and a 'FROM' at the end (and any other options you want ORDER BY or WHERE).

Select q'[Select 'Table Name: '||']' 
       ||table_name|| 
       q'[', 'Row Count: '||Count(*) From ]' 
       ||table_name|| 
       ';'
  From User_Tables 
 Where Table_Name in ('EMP','DEPT','SALGRADE');

6) Add a SPOOL command, and use a 'SQL' extension. Spool Off at the end. If you're brave, you can run the queries immediately. I usually test a line or two before I do that. In this example, I had previously SET PAGESIZE=0 to kill the headers.

Spool TestQ.SQL

Select q'[Select 'Table Name: '||']' 
       ||table_name|| 
       q'[', 'Row Count: '||Count(*) From ]' 
       ||table_name||
       ';' 
  From User_Tables 
 Where Table_Name in ('EMP','DEPT','SALGRADE');

Spool Off

/* Run the spooled query */
@TestQ

Go forth and enjoy!

Finding Names in SQL Queries

(This is an update to a previously posted query.) This code will help document a SQL query as it looks through that code to find the list of tables, columns, amper-variables, and other called queries referenced. I added the following to the older version: called queries, a reference to the query name in the spooled file, and also to the spool filename.

There are some limitations in this code:

• It does not handle 'Select *'. You'll just have to search for these for now -- maybe fixed in the future.

• It only ignores comments that begin in column 1, therefore, if your comment starting in column 30 said "Deleted Dept_No," your report would show that name. (A future fix.)

As shown in the following example, the SomeQuery.SQL (on the left) generates the report on the right.








This code was long enough that I've stored it as 'NamesInCode.SQL'. You'll have to scroll down the page to see the file, and download button.

Delete Based on Another Table

I recently had to delete records in one table based on the data in another table. This isn't particularly tricky, but the only examples I could find online ended up deleting all the records in the table.

The code at the beginning is just to set up a table I can use as an example.

Conn Scott/Tiger
Set Feedback On Echo On

Select *
From Dept;

Drop Table Dept2 Purge;

Create Table Dept2 As
Select * From Dept;

Insert Into Dept2 Values(50,'HR','PITTSBURGH');
Insert Into Dept2 Values(60,'HOUSEKEEPING','LA');
Insert Into Dept2 Values(70,'MANAGEMENT','ATLANTA');

Select * From Dept2;

This code deletes from the newly created table where a record exists in the original table that has a matching key.

Delete From Dept2
 Where Exists
          (Select 1
             From Dept
            Where Dept.Deptno = Dept2.Deptno);

Select * From Dept2;

Timestamp in an External Table

Since this wasn't particularly easy to find, and it took several tries, I decided to post it here. As most things, once you know how to write it, it's not particularly tricky.

Input Data from External Table:
1,Jan 8 1998 12:43:27:012AM
2,Feb 19 1998 2:43:27:111PM
3,Jan 8 2008 3:49:27:693PM

Create Table Ext_Dt
(  Item       Number
 , Needed_By  Timestamp
)
Organization External
(
 Type Oracle_Loader
 Default Directory Ext
 Access Parameters
 (
    Records Delimited By Newline
    Badfile 'Date.Bad'
    Logfile 'Date.Log'
    Fields Terminated By ','
    Missing Field Values Are Null
       (
          Item,
          Needed_By Char Date_Format Timestamp Mask 'Mon Dd Yyyy Hh:Mi:Ss:Ff3am'
       )
 )
Location (Ext:'Dt.Txt')
)
Reject Limit Unlimited;

Select Item
     , Needed_By
     , To_Char(Needed_By,'Mon' ) Mth
     , To_Char(Needed_By,'Hh24') Hr
  From Ext_Dt;

Database Terminology

Physical	Logical/ Relational	Logical/ Obj.Orient	Definition	Examples
Table	Entity	Class	Something you want to store data for	Department, Employees
Column	Attribute	Attribute	An item that helps describes the entity	Department Location, Employee Birthdate
Row	Instance	Object	A specific example of the entity	Pittsburgh department, Lynn's birthdate: 01/01/1970

Selecting from a Variable TableName

A question was posted on ITToolBox asking how to select from a table based on a column. You can't use a CASE clause in the FROM statement, but the following -- though convoluted -- does work.

'New_Value' in the 'Column' statement will create a new variable named 'Table' that can then be used in the final query.

Set Verify Off Echo Off Linesize 200

Accept Key Prompt 'Enter the key for the table you wish to view (EmpNo, DeptNo): '

/*----------------------------------------------------------*/
/* Turn off the terminal so this query doesn't display      */
/*----------------------------------------------------------*/
Set Term Off
Column Table_From_Case New_Value Table

Select        Case Upper('&Key') When 'EMPNO'  Then 'EMP'
                                When 'DEPTNO' Then 'DEPT'
             End
             As
     Table_From_Case
From Dual;

Set Term On

/*----------------------------------------------------------*/
/* Select from the tablename set up with COLUMN/NEW_VALUE   */
/*----------------------------------------------------------*/

Select *
 From &Table
Where Rownum < 3;

How to Code Single Quotes with Q-Quote

Starting in 10g, use a 'q', a pair of single quotes, and a pair of brackets to code a literal that uses a single quote. With this method, you can code it exactly as it is.

Select q'!He's ok!'        As Single_Quote_In_Middle
     , q'['I like him.']'  As Single_Quote_At_Ends
     , q'(')'              As Single_Quote_Alone
     , q'<''>'             As Two_Single_Quotes
  From Dual;

To delete or insert a name with a single quote:

Delete From Emp Where EName = q'\O'MALLEY\';

Insert Into Emp ( EmpNo , EName         , DeptNo )
         Values ( 7474  , q'{O'MALLEY}' , 10     );
Commit;

To search for a name with a single quote in it:

Select EmpNo, Ename, DeptNo
 From Emp
Where Ename Like q'[%'%]';

Prior to 10g:

Select 'He''s ok'         As Single_Quote_In_Middle
     , '''I like him.'''  As Single_Quote_At_Ends
     , ''''               As Single_Quote_Alone
     , ''''''             As Two_Single_Quotes
  From Dual;

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

Line Breaks

To get a line break in your output data, use 'Chr(10).' It must be concatenated to the columns it's between

  1* Select 'Hi' || Chr(10) || 'There' From Dual
SQL> /

Hi
There

Regular Expressions

The create table statement and all the code to produce this report can be found here. Scroll down to the end to find the attachment: RegExp.Sql

This is the data the queries run against:

Select * From Fam;

NAME  PHONE           DSCR
----- --------------- --------------------------------------------------------------------------------
Me    555-123-0987    Fun-loving aunt with a lot of nieces and nephews. 111-123-4567
Don   555-234-2323    alternate phone (555)234-2222. Father Father of mostly-grown kids.
Craig 555-888-8888    at-home {nuts} {nuts} +45 8 676 49 86
Glenn 555-232-8888    (123) 242-2424
Dee   232-388-3333    -farmer 787.222.9834

Description to Match:  3 of anything, a dash, then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'...-....')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

Description to Match:  3 of anything, a dash, then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'.{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

BRACKET EXPRESSIONS

Description to Match: 3#s, -, 4

NAME  REGEXP_SUBSTR(DSCR,'[12345]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers(1-5), -, 4

NAME  REGEXP_SUBSTR(DSCR,'[1-5]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers,-,4

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: Any 3 characters except for numbers,-,4

NAME  REGEXP_SUBSTR(DSCR,'[^[:digit:]]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   tly-grow

Description to Match: 3 numbers, A-G or X-z or 'n' or 'u' ,a dash then 4 more characters. 
Don's data doesn't show because he has an earlier digit value

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]A-GX-Znu]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers, a literal period

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}\.')
----- ------------------------------------------------------------------------------------------------
Don   222.
Dee   787.

SUBEXPRESSIONS

(find a "+" then 1-4 sections of 1-3 digits followed by a space. 
The last grouping will be 1 or more numbers) 
i.e., ([0-9]{1,3} ) means 1-3 numbers followed by a space

Description to Match: International phone #

NAME  REGEXP_SUBSTR(DSCR,'\+([0-9]{1,3}){1,4}([0-9]+)')
----- ------------------------------------------------------------------------------------------------
Craig +45 8 676 49 86

ALTERNATION

(use '|')  i.e., (-|\.)

Description to Match: 3 numbers, - or ., then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}(-|\.).{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424
Dee   787.222.

Description to Match: 3 numbers, - or ., then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}[.-].{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424
Dee   787.222.

Description to Match: Phone numbers with dashes, periods or parens

NAME  REGEXP_SUBSTR(DSCR,'([0-9]{3}[.-]|\([0-9]{3}\)?)'||'[0-9]{3}[.-][0-9]{4}')
----- ------------------------------------------------------------------------------------------------
Me     111-123-4567
Don   (555)234-2222
Glenn (123) 242-2424
Dee    787.222.9834

GREEDINESS

Description to Match: Get a word ending in a space (gets all it can find)

.+[[:space:]]
-------------
In the

Description to Match: To get 1st word only

[^[:space:]]
------------
In

BACKREFERENCES

Description to Match: Find double-words

                                     REGEXP_SUBSTR(DSCR,'(^|[[:space:][:punct:]]+)([[:alpha:]]+)'||
NAME  DSCR                                                     '[[:space:][:punct:]]+\2')
----- ------------------------------ -----------------------------------------------------------------
Don   alternate phone (555)234-2222. Father . Father Father
      Father Father of mostly-grown 
      kids.
Craig at-home {nuts} {nuts} +45 8    {nuts} {nuts
      676 49 86

Sequence Basics

This shows how to create a basic sequence. CurrVal will show what number was last used. Whenever NextVal is referenced, the sequence gets incremented.

Set Feedback Off Heading Off
Drop Table TestTbl;
Create Table TestTbl
      ( Id   Number(4),
        Note Varchar2(20) );

Drop Sequence Test_Seq;
Create Sequence Test_Seq
         Increment By 1 Start With 1
         NoMaxValue NoCache NoCycle;

Insert Into TestTbl Values(Test_Seq.Nextval, 'Apple');
Select 'CurrVal: '||Test_Seq.CurrVal From Dual;

CurrVal: 1

Insert Into TestTbl Values(Test_Seq.Nextval, 'Nuts' );
Select 'CurrVal: '||Test_Seq.CurrVal From Dual;

CurrVal: 2

Select 'ID: '  ||ID         ||' '||
       'Note: '||Note
 from TestTbl;

ID: 1 Note: Apple
ID: 2 Note: Nuts

Select 'CurrVal: '||Test_Seq.CurrVal ||' '||
       'NextVal: '||Test_Seq.NextVal
  From Dual;

CurrVal: 3 NextVal: 3

/

CurrVal: 4 NextVal: 4

Trigger - Check for Positive Values

Create Or Replace Trigger PositiveOnly
 Before Insert Or Update
 Of DeptNo On Dept
 For Each Row
Begin
 If :new.DeptNo -20100, 'Please insert a positive value');
 End If;
End PositiveOnly;
/

Range & List-Partitioned Tables

A range partition allows Oracle to place data in a partition based on the input value being between two values. 'MaxValue' is used to hold everything higher than the previous value listed.

A list partition allows Oracle to place data in a partition based on the input value matching a value in the list. The 'Default' is used to hold everything not mentioned in the list.

For instance, in the range-partitioned table below, data is grouped based on the year

partition Part_2004 - any year less than 2005

partition Part_2005 - any year between 2005 and 2006

partition Part_Max - any year greater than 2006

SQL> Create Table Test_Range
  2   ( Id    Number(3)
  3   , Year  Number(4)
  4   )
  5    Partition By Range(Year)
  6    (
  7      Partition Part_2004 Values Less Than (2005)
  8    , Partition Part_2005 Values Less Than (2006)
  9    , Partition Part_Max  Values Less Than (MaxValue)
 10    );
 
SQL> Insert Into Test_Range Values (1,2003);
SQL> Insert Into Test_Range Values (2,2004);
SQL> Insert Into Test_Range Values (3,2005);
SQL> Insert Into Test_Range Values (4,2006);
SQL> Insert Into Test_Range Values (5,2007);

SQL> Select * From Test_Range;

ID       YEAR
-- ----------
 1       2003
 2       2004
 3       2005
 4       2006
 5       2007

SQL> Select * from Test_Range PARTITION (Part_2004);

ID       YEAR
-- ----------
 1       2003
 2       2004

SQL> Select * from Test_Range PARTITION (Part_2005);

ID       YEAR
-- ----------
 3       2005

SQL> Select * from Test_Range PARTITION (Part_Max);

ID       YEAR
-- ----------
 4       2006
 5       2007

---

In the List-partitioned table below, data is grouped based on continents:

partition Part_Europe - France or Italy

partition Part_America - Canada or United States

partition Part_Others - any country not listed in the other two partitions.

SQL> Create Table Test_List
  2   ( Id      Number(3)
  3   , Country Varchar2(30)
  4   )
  5    Partition By List(Country)
  6     (
  7       Partition Part_Europe  Values ('France', 'Italy')
  8     , Partition Part_America Values ('Canada', 'United States')
  9     , Partition Part_Others  Values (Default)
 10     );

SQL> Insert Into Test_List Values (1,'France');
SQL> Insert Into Test_List Values (2,'Italy');
SQL> Insert Into Test_List Values (3,'Canada');
SQL> Insert Into Test_List Values (4,'United States');
SQL> Insert Into Test_List Values (5,'Australia');
SQL> Insert Into Test_List Values (6,'China');

SQL> Select * from Test_List;

ID COUNTRY
-- -------------
 1 France
 2 Italy
 3 Canada
 4 United States
 5 Australia
 6 China
 
SQL> Select * from Test_List PARTITION (Part_Europe );

ID COUNTRY
-- -------------
 1 France
 2 Italy

SQL> Select * from Test_List PARTITION (Part_America);

ID COUNTRY
-- -------------
 3 Canada
 4 United States
 
SQL> Select * from Test_List PARTITION (Part_Others );

ID COUNTRY
-- -------------
 5 Australia
 6 China

For more information see the Oracle documentation on Partitioned Tables and Indexes