I see posts on Oracle related forums about various releases (anything that isn't 11.x or 12.x) being "unsupported". This is wrong. Of course you should upgrade any 9i or 10g databases, but you don't have to.

Oracle Corporation's lifetime support policy is documented here,
Lifetime Support Policy
take a look, and you'll see that release 10.2 was in premier support until end July 2010 when it went into extended support. At end July 2013, it goes into sustaining support. Sustaining support will continue indefinitely. Even release 8.1.7 will have sustaining support indefinitely.
So what is sustaining support? That is documented here,
Lifetime support benefits
To summarize, extended support gives you everything you are likely to need. What you do not get is certification against new Oracle products or new third party products (principally, operating systems). But does that matter? I don't think so. For example, release 11.2.0.3 (still in premier support) is not certified against Windows 8, but it works fine.
Sustaining support has a more significant problem: no more patches. Not even patches for security holes, or changes in regulatory requirements. The security patch issue may of course be serious, but regulatory issues are unlikely to matter (this is a database, not a tax management system.) Think about it: 10g has been around for many years. It is pretty well de-bugged by now. If you hit a problem with no work around, you are pretty unlucky. Sustaining support gives you access to technical support, available patches, software, and documentation. That is all most sites will ever need.
Right now, I am working on a 9.2.0.8 database. It cannot be upgraded because the application software is written by a company that does not permit a database upgrade. Why not? Well, the reason may be commercial: they have a replacement product that is supported on newer databases. But that is nothing to do with me. The database works, the software works. Making it work better is a challenge - but that is what a DBA is paid to do. Don't just write it off as "unsupported".
Of course I am not suggesting that users should not upgrade to current releases - but upgrades are a huge project, and can have major implications. Running out dated software is silly, unless you have an irrefutable reason for so doing. The lack of security patches make you vulnerable to data loss. The lack of regulatory patches may make it illegal. The lack of newer facilities will be restricting the utility of the system. You may be losing money by not taking of advantage of changes of newer technology that can better exploit your hardware.
If anyone is looking for consulting support to upgrade their database - my boss will be happy to give you a quote. But I won't refuse to support you in the meantime.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows:
START_GAP END_GAP
4 4
7 7
11 14
16 19
24 24

First, run the following code, to create tab1 table:


CREATE TABLE tab1
(
col1 INTEGER
);


Then, insert a few rows:


INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (2);
INSERT INTO tab1 VALUES (3);
INSERT INTO tab1 VALUES (5);
INSERT INTO tab1 VALUES (6);
INSERT INTO tab1 VALUES (8);
INSERT INTO tab1 VALUES (9);
INSERT INTO tab1 VALUES (10);
INSERT INTO tab1 VALUES (15);
INSERT INTO tab1 VALUES (20);
INSERT INTO tab1 VALUES (21);
INSERT INTO tab1 VALUES (22);
INSERT INTO tab1 VALUES (23);
INSERT INTO tab1 VALUES (25);
INSERT INTO tab1 VALUES (26);

COMMIT;

With data, you can take care of solving the gaps problem…

One of the most efficient solutions to the gaps problem involves using analytic functions (also known as window functions)


WITH aa AS
(SELECT col1 AS cur_value, LEAD (col1) OVER (ORDER BY col1) AS next_value
FROM tab1)
SELECT cur_value + 1 AS start_gap, next_value - 1 AS end_gap
FROM aa
WHERE next_value - cur_value > 1
ORDER BY start_gap


Using the LEAD function, you can return for each current col1 value (call it cur_value) the next value in the sequence (call it next_value). Then you can filter only pairs where the difference between the two is greater than the one.

One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s).

Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1

A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it’s a really helpful feature, at least during the unit testing of the code.

I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily :

NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table.

SP2 Error

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             VARCHAR2(1024)
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL> truncate table sperrorlog;

Table truncated.

SQL> set errorlogging on;
SQL> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.27.29.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

ORA Error

SQL> truncate table sperrorlog;

Table truncated.

SQL> select * from dula;
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.36.08.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

select * from dula
ORA-00942: table or view does not exist

Like shown above, you can capture PLS errors too.

If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file -

truncate table sperrorlog;
selct * from dual;
select * from dula;

SQL> @D:sperrorlog_test.sql;

Table truncated.

SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.17.000000 AM

D:sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:sperror..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM
D:sperrorlog_test.sql
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM
D:sperrorlog_test.sql
select * from dula
ORA-00942: table or view does not exist

SQL>

In addition to above, if you want to be particularly specific about each session’s error to be spooled into a file you could do this -

SQL> set errorlogging on identifier my_session_identifier

SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';

To spool the session specific errors into a file, just do this -

SQL> spool error.log
SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';
SQL> spool off

The CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifies leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH.

Let’s continue with the example from my previous posts about hierarchical data: the skeleton from the old song “Dem Dry Bones”.

UPDATE skeleton SET connected_to_the=NULL WHERE bone='head';
SELECT * FROM skeleton;

BONE                                     CONNECTED_TO_THE
---------------------------------------- ----------------------------------------
shoulder                                 neck
back                                     shoulder
hip                                      back
thigh                                    hip
knee                                     thigh
leg                                      knee
foot                                     heel
head
neck                                     head
toe                                      foot
arm                                      shoulder
wrist                                    arm
ankle                                    leg
heel                                     ankle
finger                                   wrist
a rib                                    back
b rib                                    back
c rib                                    back

With CONNECT BY, we can use the CONNECT_BY_ISLEAF pseudocolumn to identify leaf nodes:

SELECT bone, level, 
ltrim(sys_connect_by_path(bone,' -> '),' -> ') AS path
FROM skeleton
WHERE connect_by_isleaf=1
START WITH connected_to_the IS NULL
CONNECT BY prior bone=connected_to_the 
ORDER siblings BY 1;

BONE      LEVEL PATH                                                                                            
--------- ----- ----------------------------------------------------------------------------------------------- 
finger        6 head -> neck -> shoulder -> arm -> wrist -> finger                                              
a rib         5 head -> neck -> shoulder -> back -> a rib                                                       
b rib         5 head -> neck -> shoulder -> back -> b rib                                                       
c rib         5 head -> neck -> shoulder -> back -> c rib                                                       
toe          12 head -> neck -> shoulder -> back -> hip -> thigh -> knee -> leg -> ankle -> heel -> foot -> toe

This pseudocolumn takes a little more thought to replicate using recursive WITH than the LEVEL pseudocolumn and the SYS_CONNECT_BY_PATH, which, as we saw in my last post, fall naturally out of the recursion.

We can imitate CONNECT_BY_ISLEAF by searching DEPTH FIRST and using the LEAD function to peek at the next row’s the_level value. If the next row’s level is higher than the current row, then it’s a child of the current row; otherwise, it’s not a child. Since, with DEPTH FIRST, all the children of a row come out before any siblings, if the next row isn’t a child, then the current row is a leaf.

WITH skellarchy (bone, parent, the_level) AS
 ( SELECT bone, connected_to_the, 0  FROM skeleton 
   WHERE bone = 'head'                         
 UNION ALL
   SELECT s.bone, s.connected_to_the , r.the_level + 1
   FROM skeleton s, skellarchy r
   WHERE r.bone = s.connected_to_the           
 )
SEARCH DEPTH FIRST BY bone SET bone_order
CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N'
SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level,
  lead(the_level) OVER (ORDER BY bone_order) AS next_level,
  CASE 
    WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL
    ELSE 'LEAF'
  END is_leaf
FROM skellarchy
ORDER BY bone_order;

BONE_TREE                                      THE_LEVEL NEXT_LEVEL IS_L
--------------------------------------------- ---------- ---------- ----
head                                                   0          1
  neck                                                 1          2
    shoulder                                           2          3
      arm                                              3          4
        wrist                                          4          5
          finger                                       5          3 LEAF
      back                                             3          4
        a rib                                          4          4 LEAF
        b rib                                          4          4 LEAF
        c rib                                          4          4 LEAF
        hip                                            4          5
          thigh                                        5          6
            knee                                       6          7
              leg                                      7          8
                ankle                                  8          9
                  heel                                 9         10
                    foot                              10         11
                      toe                             11            LEAF

Watch out for Cycles

The first point of caution about this solution concerns cycles. In my last post, I had created a cycle by making the ‘head’ node’s parent the ‘toe’ node. If I’d left the cycle in the data, the toe node wouldn’t be a leaf any more, but this query would falsely identify the head as a leaf:

UPDATE skeleton SET connected_to_the='toe' WHERE bone='head';

BONE_TREE                                      THE_LEVEL NEXT_LEVEL IS_L
--------------------------------------------- ---------- ---------- ----
head                                                   0          1
  neck                                                 1          2
    shoulder                                           2          3
      arm                                              3          4
        wrist                                          4          5
          finger                                       5          3 LEAF
      back                                             3          4
        a rib                                          4          4 LEAF
        b rib                                          4          4 LEAF
        c rib                                          4          4 LEAF
        hip                                            4          5
          thigh                                        5          6
            knee                                       6          7
              leg                                      7          8
                ankle                                  8          9
                  heel                                 9         10
                    foot                              10         11
                      toe                             11         12
                        head                          12            LEAF
 
19 rows selected.

This can be corrected for by adding WHERE IS_A_CYCLE=’N’ to the query.

Respect the order of evaluation…

A second point of caution: if I add a WHERE clause to the query that limits the number of levels, the last line of the resultset will always be identified as a leaf.

WITH skellarchy (bone, parent, the_level) AS
 ( SELECT bone, connected_to_the, 0  FROM skeleton 
   WHERE bone = 'head'                         
 UNION ALL
   SELECT s.bone, s.connected_to_the , r.the_level + 1
   FROM skeleton s, skellarchy r
   WHERE r.bone = s.connected_to_the           
 )
SEARCH DEPTH FIRST BY bone SET bone_order
CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N'
SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level,
  lead(the_level) OVER (ORDER BY bone_order) AS next_level,
  CASE 
    WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL
    ELSE 'LEAF'
  END is_leaf
FROM skellarchy
WHERE the_level < 8 
ORDER BY bone_order;

BONE_TREE                                                     THE_LEVEL NEXT_LEVEL IS_L
------------------------------------------------------------ ---------- ---------- ----
head                                                                  0          1
  neck                                                                1          2
    shoulder                                                          2          3
      arm                                                             3          4
        wrist                                                         4          5
          finger                                                      5          3 LEAF
      back                                                            3          4
        a rib                                                         4          4 LEAF
        b rib                                                         4          4 LEAF
        c rib                                                         4          4 LEAF
        hip                                                           4          5
          thigh                                                       5          6
            knee                                                      6          7
              leg                                                     7            LEAF      <<<=====

The leg is falsely identified as a leaf, and NEXT_LEVEL comes out as NULL, even though the ‘leg’ row has a child row. Why is that? It’s because this solution uses the LEAD analytic function. With analytic functions, WHERE clauses are evaluated before the analytic functions.

Highlighting the relevant bits from the query:

WITH skellarchy AS ...[recursive WITH subquery]...
SELECT ... LEAD(the_level) OVER (ORDER BY bone_order) AS next_level ... --analytic function
FROM skellarchy
WHERE the_level < 8 ...                                                 --where clause

To quote the documentation:

Analytic functions compute an aggregate value based on a group of rows…. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row…. Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed.

In the query above, “where the_level < 8" will be evaluated before LEAD(the_level). The EXPLAIN PLAN shows this very clearly:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |          |     2 |    76 |     8  (25)| 00:00:01 |
|   1 |  WINDOW BUFFER                           |          |     2 |    76 |     8  (25)| 00:00:01 |  <<=== LEAD
|*  2 |   VIEW                                   |          |     2 |    76 |     8  (25)| 00:00:01 |  <<=== filter("THE_LEVEL"<8)
|   3 |    UNION ALL (RECURSIVE WITH) DEPTH FIRST|          |       |       |            |          |
|*  4 |     TABLE ACCESS FULL                    | SKELETON |     1 |    24 |     2   (0)| 00:00:01 |
|*  5 |     HASH JOIN                            |          |     1 |    49 |     5  (20)| 00:00:01 |
|   6 |      RECURSIVE WITH PUMP                 |          |       |       |            |          |
|   7 |      TABLE ACCESS FULL                   | SKELETON |    18 |   432 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("THE_LEVEL"<8)
   4 - filter("BONE"='head')
   5 - access("R"."BONE"="S"."CONNECTED_TO_THE")

The WINDOW BUFFER (analytic window) is evaluated after the VIEW which filters on “THE_LEVEL”<8. So, "lead(the_level) over (order by bone_order)" will be null where the_level=7, and the 'leg' wrongly identified as a leaf node. What we actually want is for the analytic function LEAD to run over the whole resultset, and only then limit the results to show the levels 0-7. The obvious way to do this is to wrap the query in a second SELECT statement:

SELECT * FROM (
  WITH skellarchy (bone, parent, the_level) AS
   ( SELECT bone, connected_to_the, 0  FROM skeleton 
     WHERE bone = 'head'                         
   UNION ALL
     SELECT s.bone, s.connected_to_the , r.the_level + 1
     FROM skeleton s, skellarchy r
     WHERE r.bone = s.connected_to_the           
   )
  SEARCH DEPTH FIRST BY bone SET bone_order
  CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N'
  SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level,
    lead(the_level) OVER (ORDER BY bone_order) AS next_level,
    CASE 
      WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL
      ELSE 'LEAF'
    END is_leaf
  FROM skellarchy
  ORDER BY bone_order
) WHERE the_level < 8;

BONE_TREE                                                     THE_LEVEL NEXT_LEVEL IS_L
------------------------------------------------------------ ---------- ---------- ----
head                                                                  0          1
  neck                                                                1          2
    shoulder                                                          2          3
      arm                                                             3          4
        wrist                                                         4          5
          finger                                                      5          3 LEAF
      back                                                            3          4
        a rib                                                         4          4 LEAF
        b rib                                                         4          4 LEAF
        c rib                                                         4          4 LEAF
        hip                                                           4          5
          thigh                                                       5          6
            knee                                                      6          7
              leg                                                     7          8

Now, the analytic function in the inner query is evaluated first, before the WHERE clause in the outer query. We can see this in the EXPLAIN PLAN too, of course. Now the WINDOW BUFFER (analytic window) is evaluated before the VIEW with filter(“THE_LEVEL”<8) :

------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |          |     2 |  4068 |     8  (25)| 00:00:01 |
|*  1 |  VIEW                                     |          |     2 |  4068 |     8  (25)| 00:00:01 |  <<=== filter("THE_LEVEL"<8)
|   2 |   WINDOW BUFFER                           |          |     2 |    76 |     8  (25)| 00:00:01 |  <<=== LEAD
|   3 |    VIEW                                   |          |     2 |    76 |     8  (25)| 00:00:01 |
|   4 |     UNION ALL (RECURSIVE WITH) DEPTH FIRST|          |       |       |            |          |
|*  5 |      TABLE ACCESS FULL                    | SKELETON |     1 |    24 |     2   (0)| 00:00:01 |
|*  6 |      HASH JOIN                            |          |     1 |    49 |     5  (20)| 00:00:01 |
|   7 |       RECURSIVE WITH PUMP                 |          |       |       |            |          |
|   8 |       TABLE ACCESS FULL                   | SKELETON |    18 |   432 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("THE_LEVEL"<8)
   5 - filter("BONE"='head')
   6 - access("R"."BONE"="S"."CONNECTED_TO_THE")

This is one case of the general point that, as Tom Kyte explains in this Ask Tom answer,“select analytic_function from t where CONDITION” is NOT THE SAME AS “select * from (select analytic_function from t) where CONDITION”.

So, to sum up my last few posts, we can do everything that CONNECT BY can do with the 11g recursive WITH syntax. Plus, the recursive WITH syntax makes it easy to express simple recursive algorithms in SQL.