Visit the Quest Software Home Page. Pipelines Home
PL/SQL Pipeline  Oracle PL/SQL Tips of the Month - 2005

JanuaryFORMAT_ERROR_STACK versus SQLERRM
February PL/SQL Distance Calculator
March Use DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM
April Running Hierarchy Queries Against DUAL
May Using a Comma-separated List in an SQL Query
June Analyzing Laggards Updated 7/11/05
July NVL Smarts
August Execute Shell Commands from PL/SQL
September Record-level DML
October Getting the SQL Plan for a Cursor with DBMS_XPLAN
November Procedure to Convert Numbers to Text
December Name Those Exceptions!

2004 Tips 2003 Tips 2002 Tips
2001 Tips 2000 Tips 1999 Tips
1998 Tips 1997 Tips

January's Tip of the Month

FORMAT_ERROR_STACK versus SQLERRM
Compliments of Steven Feuerstein, Pipeline SYSOP.
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20501

It is generally preferable to use DBMS_UTILITY.FORMAT_ERROR_STACK in place of SQLERRM. Both SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK return the text describing the most recent error. SQLERRM is, however, limited to the first 255 characters of that text. DBMS_UTILITY.FORMAT_ERROR_STACK returns the full error message text, and for that reason should be used in place of SQLERRM.

You may still want to use SQLERRM if you need to retrieve the error message for a given error code, as in:

BEGIN
  DBMS_OUTPUT.PUT_LINE (SQLERRM (-1855));
END;
/

SQLERRM accepts an error code as its only parameter and returns the error message string (default value is SQLCODE). The DBMS_UTILITY.FORMAT_ERROR_STACK function only returns the error message for the current SQLCODE value.

If you are using Oracle10g, you may also make use of the new built-in function, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, to obtain the line number on which an error was originally raised. Note that this line number is reset whenever an exception is re-raised with RAISE; or another exception is raised within an exception.

February's Tip of the Month

PL/SQL Distance Calculator
Compliments of Leon Rzhemovskiy, Unika Solutions (unika_solutions-leon@yahoo.ca).
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20502

The success or failure of a marketing campaign can often be determined by the distance bewteen the customer and the retail outlet. Using the same city or area code as a distance measurement is rarely accurate enough. Measuring the actual distance between the retail outlet and the customer's postal code is a reliable solution.

The following function calculates the distance between 2 points using the Great Circle formula:

/*:: This routine calculates the distance between two points (given the :*/
/*:: latitude/longitude of those points). 
/*:: Passed to function: :*/
/*:: lat1, lon1 = Latitude and Longitude of point 1 (in radians) :*/
/*:: lat2, lon2 = Latitude and Longitude of point 2 (in radians) :*/
/*:: v_r earth radius  :*/
/*:: result returned in kilometers  :*/
/*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
FUNCTION get_geo_distance(lat1 IN NUMBER, lon1 IN NUMBER,lat2 IN NUMBER, lon2 IN NUMBER) RETURN
NUMBER 
IS
  v_r NUMBER :=6378.7; 
BEGIN
  IF lat1=lat2 AND lon1=lon2 THEN 
    RETURN 0;
  END IF;   
  RETURN v_r * ACOS(
                    SIN(lat1) * SIN(lat2) + 
                    COS(lat1) * COS(lat2) * COS(lon2- lon1)
                 );
EXCEPTION WHEN OTHERS THEN
  RETURN NULL; 
END;

The only missing part is a data file with longitude and latitude information for every postal code. This is available as a subscription with monthly updates or as a one time purchase. My comparison between vendors suggests ZIPCodeWorld.com as a winner with regards to data accuracy.

March's Tip of the Month

Use DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM
Compliments of Steven Feuerstein, Pipeline SYSOP.
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20503

Note: This is an amplified version of our January tip, also provided by Steven.

SQLERRM is a function that returns the error message for a particular error code. If you do not pass an error code to SQLERRM, then it returns the error message associated with the value returned by SQLCODE.

DBMS_UTILITY.FORMAT_ERROR_STACK, like SQLERRM, returns the message associated with the current error, i.e., the value returned by SQLCODE. It differs from SQLERRM in two ways:

  1. Its length is not restricted; it will return the full error message string. SQLERRM will truncate the string at either 255 or 512 bytes (depending on your version of Oracle).

  2. You cannot pass an error code number to this function; it cannot be used to return the message for a "random" error code.

You should as a rule call this function inside your exception handler logic to obtain the full error message. Of course, you will not want to call DBMS_OUTPUT.PUT_LINE to display the value, since PUT_LINE has trouble with strings of more than 255 characters!

While it is true that you should use DBMS_UTILITY.FORMAT_ERROR_STACK in place of SQLERRM, that doesn't mean SQLERRM is totally irrelevant. In fact, you can use it to answer the following questions:

  • Is a particular number a valid Oracle error?

  • What is the error message corresponding to an error code?

As mentioned earlier, SQLERRM will return the error message for an error code. If, however, you pass SQLERRM a code that is not valid, it does not raise an exception. Instead, it returns a string in one of the following two forms:

If the number is negative...

ORA-NNNNN: Message NNNNN not found;  product=RDBMS; facility=ORA

If the number is positive...

-NNNNN: non-ORACLE exception

You can use these facts to build functions to neatly return information about whatever code you are currently working with. Here is the code for a package with such programs:

CREATE OR REPLACE PACKAGE oracle_error_info
IS
   FUNCTION is_app_error (code_in IN INTEGER)
      RETURN BOOLEAN;

   FUNCTION is_valid_oracle_error (
      code_in            IN   INTEGER
    , app_errors_ok_in   IN   BOOLEAN DEFAULT TRUE
    , user_error_ok_in   IN   BOOLEAN DEFAULT TRUE
   )
      RETURN BOOLEAN;

   PROCEDURE validate_oracle_error (
      code_in            IN       INTEGER
    , message_out        OUT      VARCHAR2
    , is_valid_out       OUT      BOOLEAN
    , app_errors_ok_in   IN       BOOLEAN DEFAULT TRUE
    , user_error_ok_in   IN       BOOLEAN DEFAULT TRUE
   );
END oracle_error_info;
/

CREATE OR REPLACE PACKAGE BODY oracle_error_info
IS
   FUNCTION is_app_error (code_in IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN code_in BETWEEN -20999 AND -20000;
   END is_app_error;

   PROCEDURE validate_oracle_error (
      code_in            IN       INTEGER
    , message_out        OUT      VARCHAR2
    , is_valid_out       OUT      BOOLEAN
    , app_errors_ok_in   IN       BOOLEAN DEFAULT TRUE
    , user_error_ok_in   IN       BOOLEAN DEFAULT TRUE
   )
   IS
      l_message   VARCHAR2 (32767);

      PROCEDURE set_failure
      IS
      BEGIN
         message_out := NULL;
         is_valid_out := FALSE;
      END set_failure;
   BEGIN
      l_message := SQLERRM (code_in);

      -- If SQLERRM does not find an entry, it return a string like one of these:
      -- If the number is negative...
      -- ORA-NNNNN: Message NNNN not found;  product=RDBMS; facility=ORA
      -- If the number is positive...
      --  -13000: non-ORACLE exception
      -- If the positive number is too big, we get numeric overflow.
      IF is_app_error (code_in) AND NOT app_errors_ok_in
      THEN
         set_failure;
      ELSIF code_in = 1 AND NOT user_error_ok_in
      THEN
         set_failure;
      ELSIF    l_message LIKE 'ORA-_____: Message%not found;%'
            OR l_message LIKE '%: non-ORACLE exception%'
      THEN
         set_failure;
      ELSE
         message_out := l_message;
         is_valid_out := TRUE;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         --numeric overflow
         IF SQLCODE = -1426
         THEN
            set_failure;
         ELSE
            RAISE;
         END IF;
   END validate_oracle_error;

   FUNCTION is_valid_oracle_error (
      code_in            IN   INTEGER
    , app_errors_ok_in   IN   BOOLEAN DEFAULT TRUE
    , user_error_ok_in   IN   BOOLEAN DEFAULT TRUE
   )
      RETURN BOOLEAN
   IS
      l_message   VARCHAR2 (32767);
      retval      BOOLEAN;
   BEGIN
      validate_oracle_error (code_in
                           , l_message
                           , retval
                           , app_errors_ok_in
                           , user_error_ok_in
                            );
      RETURN retval;
   END is_valid_oracle_error;
END oracle_error_info;
/

You can find this program and many other useful templates and reusable code inside Qnxo, a new product that Steven has created. For more information, visit www.qnxo.com.

April's Tip of the Month

Running Hierarchy Queries Against DUAL
Compliments of Oleg Savkin.
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20504

The following are some findings regarding the use of hierarchical queries from DUAL.

For example, consider the following table with amounts/numbers:

select *
from my_table:

20
30
31
45
2000
...
N

Now consider the task of finding all missing amounts/numbers in this table. It was easy to do like this:

select rownum
from dual
connect by level <= N
minus
select col
from my_table

This query worked fast enough, as you can see below from the tests done by Daniel Clamage.

So, the following query...

select rownum
from dual
connect by level <= N

...can be used to generate sequences of numbers, and is very simple to utilize.

Test for Oracle 9i (9.2.0)

This query runs fine in Oracle 9i, but will return only 1 record if you run it as it is: 

SQL>SELECT rownum from dual connect by level < 100;

    ROWNUM
----------
         1

The next example will return all records:

SQL>select * from (SELECT rownum from dual connect by level < 10)
  2  /

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

Test for Oracle 8i (8.1.7)

SQL>SELECT rownum from dual connect by level < 10;

    ROWNUM
----------
         1

Result: one row.

SQL>select * from (SELECT rownum from dual connect by level < 10);
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected

Result: Oracle error

Test for Oracle 10g

I didn't run it in 10g, but Daniel Clamage mentioned it runs normally, and returns all records.

Of course, there are lots of other ways to generate sequences of numbers. Consider the following package example was proposed by Daniel Clamage:

CREATE OR REPLACE
type typ_num is table of number;
/

create or replace package numtest is
  function num(p_num in integer)
  return typ_num;
end numtest;
/

create or replace package body numtest is
function num(p_num in integer)
return typ_num is
  n typ_num := typ_num();
begin
  n.extend(p_num);
  return(n);
end num;
end numtest;
/

select rownum from table(numtest.num(9));

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

Here is the timing for both approaches - query from dual and from package:

Query from dual:

SQL> select count(*) cnt
  2  from (
  3    select rownum
  4    from dual
  5    connect by level <= 1000000
  6  );

       CNT
----------
   1000000

Elapsed: 00:00:00.90
SQL> /

       CNT
----------
   1000000

Elapsed: 00:00:00.90

Query from package:

SQL> select count(*) cnt
  2  from (
  3    select rownum
  4    from table(numtest.num(1000000))
  5  );

       CNT
----------
   1000000

Elapsed: 00:00:00.64
SQL> /

       CNT
----------
   1000000

Finally, here are static explain plans for each:

begin
  explan9i('select count(*) cnt
from (
  select rownum
  from dual
  connect by level <= 1000000
)');
end;
/
Plan hash value: 4168197557
 
----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |       |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                 |      |     1 |       |            |          |
|   2 |   VIEW                          |      |     1 |       |     2   (0)| 00:00:01 |
|   3 |    COUNT                        |      |       |       |            |          |
|   4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

begin
  explan9i('select count(*) cnt
from (
  select rownum
  from table(numtest.num(1000000))
)');
end;
/
Plan hash value: 2760869553
 
---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |       |    24   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |      |     1 |       |            |          |
|   2 |   VIEW                               |      |  8168 |       |    24   (0)| 00:00:01 |
|   3 |    COUNT                             |      |       |       |            |          |
|   4 |     COLLECTION ITERATOR PICKLER FETCH| NUM  |       |       |            |          |
---------------------------------------------------------------------------------------------

The connect-by has a much lower cost (2 vs. 24).

May's Tip of the Month

Using a Comma-separated List in an SQL Query
Compliments of William Robertson, PL/SQL SYSOP.
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20505

After "How do I display multiple rows as one comma-separated list?", one of the next most-asked Oracle questions on the Web is a related one:

My procedure is passed a comma-separated list of IDs, for example 7369,7499,7839,7902. I tried to use it in my code like this:

DECLARE
	p_empno_list CONSTANT VARCHAR2(20) := '7369,7499,7839,7902';
BEGIN
	FOR r IN (
		SELECT * FROM emp
		WHERE  empno IN (p_empno_list)
		)
	LOOP
		DBMS_OUTPUT.PUT_LINE(RPAD(r.empno,9) || r.ename);
	END LOOP;
END;
/

but it just gives me an error:

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4

(When SQL is expecting a character string such as SMITH, but is passed a comma-separated list such as SMITH,JONES,FORD,MILLER, no error is produced, but the query simply returns no rows.)

Clearly, SQL does not consider...

WHERE empno IN (p_empno_list)

...to be equivalent to:

WHERE empno IN (7369,7499,7839,7902)

And of course it cannot, and should not. What if you had a value such as '123, Fake St, Springfield' that represented a single address line? How would you feel if you used v_address_line in a query similar to the one above, and SQL chose to treat it as not one but three values ('123', 'Fake St' and 'Springfield'), just because it happened to contain commas and the query used IN? It would be wrong! SQL has to treat each variable as one value.

What the programmer is really looking for is a different type of variable that explicitly represents a set of values. Fortunately Oracle SQL supports exactly this, in the form of collections.1

You create a scalar collection type like this:

CREATE TYPE INTEGER_TT AS TABLE OF INTEGER
/

The name itself is not important except that it should give an indication of what it represents, and the fact that you will have to live with it. I use _TT ("table type") for collection types (see my PL/SQL Coding Standards for more suggestions regarding naming standards). Since it should be a generic type that you will reuse in many different places, it is better to refer to the base datatype, INTEGER, than the fact that right now you want it for an employee ID. While you are at it, why not create a couple more:

CREATE TYPE NUMBER_TT AS TABLE OF NUMBER
/

CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(4000)
/

Now, when your list of IDs is an INTEGER_TT instead of a plain old character string, you can use it in SQL:

DECLARE
	p_empno_list CONSTANT INTEGER_TT := INTEGER_TT(7369,7499,7839,7902);
BEGIN
	FOR r IN (
		SELECT empno, ename 2
		FROM   emp
		WHERE  empno IN
		       ( SELECT column_value 3
		         FROM   TABLE(p_empno_list) )
		)
	LOOP
		DBMS_OUTPUT.PUT_LINE(RPAD(r.empno,9) || r.ename);
	END LOOP;
END;
/

7369     SMITH
7499     ALLEN
7839     KING
7902     FORD

The only problem now is how to get an INTEGER_TT collection into your procedure in place of a character string. The simplest solution is to change your procedure's parameter list so that it is by definition passed one. If an existing application has been built to pass character strings (I can never understand why anyone would do this as it just creates more work, but apparently it happens frequently), here are some suggestions:

  • Change the calling procedure so that it calls your procedure as 
    YOURPROC(INTEGER_TT(7369,7499,7839,7902));

    instead of

    YOURPROC('7369,7499,7839,7902');
  • Create an overloaded version of your procedure that accepts a VARCHAR2 parameter. Within this second version, convert the VARCHAR2 string into an INTEGER_TT collection using a SPLIT() function (you'll have to write your own - an example is here), and call the first version passing the INTEGER_TT collection.
     
  • As a last resort, continue to accept a character string as a parameter to your procedure, but convert it to an INTEGER_TT within the procedure.

It is also worth mentioning the construction WHERE empno MEMBER OF p_empno_list available from 10g onwards. For a demo of this, see Alternative IN-list binding in 10g on Adrian Billington's site.

1 The only types of collection that SQL can handle are those you define in SQL - types defined in a PL/SQL package are not recognised (PL/SQL understands SQL, not the other way around). This gives you a choice between Nested Table types and VARRAYs. Use Nested Table types and not VARRAYs unless there is some good reason to set a limit to the number of elements (this is the only difference between Nested Tables and VARRAYs), when the collection is designed to contain some particular number of elements (twelve months, seven days, five sexes etc).

2 In 9i, SELECT * used with TABLE() and no explicit CAST() gives error ORA-22905 cannot access rows from a non-nested table item.

3 COLUMN_VALUE is the default attribute name in a scalar collection type.

June's Tip of the Month

Analyzing Laggards
Compliments of Darryl Hurley, PL/SQL SYSOP.
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20506

Recently I had to write code to parse through simple test results to quickly find any differences. Given two set ID values I needed to find any values in the second set that differ from those in the first. This was to be used as a polling mechanism to determine if downstream actions need to be taken.

Here’s the table I had to query the test results from:

SQL> desc demo
 Name          Null?    Type
 ------------- -------- ------
 SET_ID        NOT NULL NUMBER
 LINK_ID       NOT NULL NUMBER
 RESULT        NOT NULL NUMBER

I needed to identify the RESULT differences by LINK_ID. Here’s an example data set:

    SET_ID    LINK_ID     RESULT
---------- ---------- ----------
         1          1         22
         1          2         22
         2          1         22
         2          2         33
         2          3         44

The differences in this set would be link 2 and 3. My first thought was to use the MINUS operator that would build two result sets and then subtract the matching entries to leave the unmatched ones behind.

SQL> SELECT link_id,
  2         result
  3    FROM demo
  4   WHERE set_id = 2
  5  MINUS
  6  SELECT link_id,
  7         result
  8    FROM demo
  9   WHERE set_id = 1;

   LINK_ID     RESULT
---------- ----------
         2         33
         3         44

However, my polling code will be running almost constantly as new test results arrive throughout the day and some result sets will be very large. Thus I need to consider the amount of work the database has to do to determine the differences. To get a baseline I’ll use AUTOTRACE on my MINUS query.

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        258  bytes sent via SQL*Net to client
        345  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

Nothing too worrisome there but I still want to look at ways to reduce the numbers.

I’ve always liked Oracle’s set operators (UNION, MINUS and INTERSECT) and have used them in a lot of situations requiring result set comparisons. But perhaps I’d become too reliant upon them and should start looking at alternatives. For my current requirement it might be easier to work through the records once ordered by LINK_ID and SET_ID and compare the values in the RESULTS column across sequential rows. The first two rows I’d look at would be these:

    SET_ID    LINK_ID     RESULT
---------- ---------- ----------
         1          1         22
         2          1         22

I’d quickly determine that they were the same, meaning the test of link 1 in both set 1 and 2 produced a result of 22. Unfortunately Oracle queries are very focused on the present - the present row that is. It would be nice to look at fields in the previous record for comparison. As luck would have it I was reading about Oracle’s great new analytical query functions and decided this might be a good time to get my feet wet.

It so happens that Oracle offers a function that does exactly what I want – to look at a previous record for comparison. It’s called the LAG function. I assume that’s a scientific term and not short for laggard. I quickly set about seeing how it might help. Here’s what I came up with.

SQL> SELECT set_id,
  2         link_id,
  3         LAG(result) OVER (ORDER BY link_id) prev_result,
  4         result
  5    FROM demo
  6   WHERE set_id IN (1,2)
  7  ORDER BY link_id;

    SET_ID    LINK_ID PREV_RESULT     RESULT
---------- ---------- ----------- ----------
         1          1                     22
         2          1          22         22
         1          2          22         22
         2          2          22         33
         2          3          33         44

The result set is exactly what I need. Each record includes the previous record's RESULT column. The exception, of course, is the first record. The way I like to think of the LAG function specified on line 3 is that it performs a pseudo-query of the demo table ordered by LINK_ID getting values from the RESULT column. It then matches these values up, lagging 1 record behind to assemble the result set. That’s some very handy functionality but the really exciting thing is the AUTOTRACE output.

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        355  bytes sent via SQL*Net to client
        345  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

The consistent gets have been cut in half. That’s because Oracle only had to visit each block in memory one time to build the result set instead of the two visits required by the MINUS operator.

Now I’ll put it into an inline view to get the list of differing links.

SQL> SELECT link_id
  2    FROM ( SELECT set_id,
  3                  link_id,
  4                  LAG(result) OVER (ORDER BY link_id) prev_result,
  5                  result
  6             FROM demo
  7            WHERE set_id IN (1,2)
  8           ORDER BY link_id )
  9   WHERE set_id = 2
 10     AND prev_result <> result;

   LINK_ID
----------
         2
         3

The moral of the story is just because these functions are labeled as analytics, don’t be afraid to investigate them for their other benefits as well - mainly cheaper queries.

Update to Tip - 7/11/05

Eagle-eyed Pipeliner András Gábor found a potential flaw in the June PL/SQL Tip Of The Month. The tip makes an assumption about the order of the records in the demo table. Here's a corrected version of the query.

SELECT link_id
  FROM ( SELECT set_id,
                link_id,
                LAG(result) OVER (ORDER BY link_id, set_id) prev_result,
                result
           FROM demo
          WHERE set_id IN (1,2)
       ORDER BY link_id, set_id )
 WHERE set_id = 2
   AND prev_result <> result;

Even this version will have issues if looking for the smaller set id (1), thus proving the old adage that when something new is used, one cannot make any assumptions.

July's Tip of the Month

NVL Smarts
Reprinted from oracledba.co.uk
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20507

Oracle appears to have a new smart means for handling NVL around bind variables.

A common issue for reports and any query where users can pass parameters is how to handle the "optional" parameter. Here's a typical example:

Table CP can be queried where column X is equal to optional parameter P. Should we code:

select * 
from CP 
where ( X = :P or :P is null)
Or 
select * 
from CP 
where X = NVL(:P,X)

As always, the best way to find this out, is with a test case. First, some test data: 

SQL> create table CP ( x number not null, y number);

Table created.

SQL> insert into cp select rownum,rownum
  2  from all_objects
  3  where rownum < 20000;

19999 rows created.

SQL> commit;

Commit complete.

SQL> create index cp1 on cp (x );

Index created.

SQL> analyze table cp estimate statistics;

Table analyzed.

Now lets see what happens with each scenario:

SQL> variable p number
SQL> set autotrace traceonly explain
SQL> select *
  2  from cp
  3  where ( x = :p or :p is null )
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=1001 Bytes=68068)
   1    0   TABLE ACCESS (FULL) OF 'CP' (Cost=33 Card=1001 Bytes=68068)

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=2 Bytes=136)
   1    0   CONCATENATION
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'CP' (Cost=1 Card=1 Bytes=68)
   4    1     FILTER
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'CP' (Cost=1 Card=1 Bytes=68)
   6    5         INDEX (RANGE SCAN) OF 'CP1' (NON-UNIQUE) (Cost=1 Card=1)

The second one possibly looks worse because it looks like a full scan and an index scan, but look at the cost for the full scan. This looks a little odd - the cost is "1" whereas the previous explain plan thinks the full scan costs "33". In the second case, what Oracle is doing is smart, where it will defer the decision on the whether to do the full scan or the index scan based on whether the parameter is actually provided or not. This apparently has come in somewhere around the 8i stage - it does not appear to do this on 8.0. We can prove this "smart-choice" with some timing results:

SQL> set autotrace traceonly statistics
SQL> exec :p := 123;

PL/SQL procedure successfully completed.

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          3  physical reads
          0  redo size
        307  bytes sent via SQL*Net to client
        214  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

So we used the index to quickly get to the row, but when we null out the parameter:

SQL> exec :p := null;

PL/SQL procedure successfully completed.

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

19999 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       1537  consistent gets
        210  physical reads
          0  redo size
     623623  bytes sent via SQL*Net to client
      92189  bytes received via SQL*Net from client
       1336  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      19999  rows processed

And here we did the full scan (the best option because no parameter was given).

You'll find that if you use the other syntax, you will get a full tablescan every time. The reason for this is that the queries actually could return a different result. It all all depends on the column being queried not containing any nulls. If the column could contain nulls, then the check...

where x = nvl(:p,x)

...will not pick up any rows for which X is null (whereas the other query will).

The moral of the story:

  1. Ensure that any columns that will not be null are defined as such in the database
     
  2. Use the NVL clause on those columns to handle optional parameters
     
  3. If the column can contain nulls, if possible, dissolve the SQL into two queries: 
    if :p is null then 
select * 
from CP 
else 
select * 
from CP 
where x = :p 
end if;
    to at least get the index benefit when the parameter is actually given.

August's Tip of the Month

Execute Shell Commands from PL/SQL
Compliments of Labo-Oracle.com
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20508

A shell command can be executed from PL/SQL using a Java class. The creation of a Java class is divided into three steps :

  1. Create the Java Source
  2. Publish the Call specification
  3. Grant privileges
So first, we need to create the Java class to perform the shell command: 
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
finalCommand[0] = "C:\\windows\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}

final Process pr = Runtime.getRuntime().exec(finalCommand);
new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
}
}).start();

new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}

public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}

};
/

show errors java source "Host"

Next we publish the call specification using a PL/SQL "wrapper" procedure:

CREATE OR REPLACE PROCEDURE Host_Command (p_command IN VARCHAR2)
AS LANGUAGE JAVA 
NAME 'Host.executeCommand (java.lang.String)';
/

The relevant permissions must be granted from SYS for JServer to access the file system:

EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'java.io.FilePermission', 
                                '<>', 'read ,write, execute, delete');

EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 
                                'writeFileDescriptor', '');

EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 
                                'readFileDescriptor', '');

The affects of the grant will not be noticed until the grantee reconnects.

And finally, to test it, we call the PL/SQL procedure with our command text:

SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
Host_Command (p_command => 'move C:\test1.txt C:\test2.txt');
END;
/

September's Tip of the Month

Record-level DML
Compliments of Steven Feuerstein, from the August issue of OPP/News
BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20509

PL/SQL is tightly integrated with SQL, no doubt about it. That doesn't mean, however, that it isn't possible to make that integration even tighter. Oracle took another step in this direction in Oracle9i Database Release 2, when it added native support for inserting and updating rows of data with PL/SQL records.

Prior to this release, if I had filled up a %ROWTYPE record with data and wanted to insert that record into my table, I would have to separate out each of the individual fields in the INSERT statement. Here is an example. Given this table:

CREATE TABLE books (
 isbn VARCHAR2(13),
 title VARCHAR2(200),
 summary VARCHAR2(2000),
 author VARCHAR2(200),
 page_count NUMBER
)
/

I can perform a record-level insert as follows:

DECLARE
 my_book books%ROWTYPE;
BEGIN
 my_book.isbn := '1-56592-335-9';
 my_book.title := 'ORACLE PL/SQL PROGRAMMING';
 my_book.summary := 'General user guide and reference';
 my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';
 my_book.page_count := 1000;

  INSERT INTO books VALUES my_book;
END; 
/

    Notice that I do not place the record inside parentheses. That difference in syntax tells Oracle that you are passing an entire record. Notice also that with this approach, you will need to have already generated the primary key value and placed it into your record, or rely on a database trigger to generate the value.

    You can also perform updates with a record. The following example inserts a row into the books table with a %ROWTYPE record. Notice that I use a new keyword, ROW, to indicate that I am updating the entire row with a record:

    DECLARE
     my_book books%ROWTYPE;
    BEGIN
     my_book.isbn := '1-56592-335-9';
     my_book.title := 'ORACLE PL/SQL PROGRAMMING';
     my_book.summary := 'General user guide and reference';
     my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';
     my_book.page_count := 980; -- new page count for 3rd edition 
     
     UPDATE books
     SET ROW = my_book
     WHERE isbn = my_book.isbn;
    END;
    /

    There are some restrictions on record-based updates:

    • You must update an entire row with the ROW syntax. You cannot update a subset of columns based on a user-defined record type.

    • You cannot perform an update using a subquery.

    I think that generally we will find record-based INSERTs more useful than record-based UPDATEs. Regardless, it is always a pleasure to see (and use) a PL/SQL that is ever more tightly integrated with the underlying SQL language!

    October's Tip of the Month

    Getting the SQL Plan for a Cursor with DBMS_XPLAN
    Compliments of Adrian Billington
    BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20510

    DBMS_XPLAN has been extended in 10g to show the actual SQL plan for a cursor ( i.e. a particular execution of a SQL statement ).

    In 9i, the method to achieve the same involved creating a view over V$SQL_PLAN that emulated the PLAN_TABLE and then "tricking" DBMS_XPLAN into thinking it was reporting on an explain plan. While this worked very nicely, Oracle has now supplied a proper means of accessing this information. This information is useful to examine the plan of statements that didn't behave in the way you expected. It is especially useful in that it also exposes the execution plan of currently running SQL.

    Let's run a simple SQL statement that will give a fairly large plan as it's over a dictionary view...

    @set_prompt
set echo on trimspool on pages 100 lines 155
spool dbms_xplan_display_cursor_demo.lst

SELECT /*+ FIND_ME_PLEASE */ COUNT(*)
FROM   user_tables;

  COUNT(*)
----------
        12

    Now let's get the relevant information from v$sql. To enable this to run as a single script, I'm using substitution variables for simplicity. Normally, I would expect to just issue the query manually and examine the output just in case there were multiple rows returned from similar statements...

    col sql_id       new_value sqlid
col child_number new_value childno

SELECT sql_id, child_number
FROM   v$sql
WHERE  sql_text LIKE '%FIND_ME_PLEASE%'
AND    LOWER(sql_text) NOT LIKE 'select sql_id%';

SQL_ID        CHILD_NUMBER
------------- ------------
24v0zf5as1y1t            0

    Now we have the relevant information to get the ACTUAL execution plan from V$SQL_PLAN...

    col plan_table_output format a120

SELECT plan_table_output
FROM   TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '&sqlid', &childno ) );
old   2: FROM   TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '&sqlid', &childno ) )
new   2: FROM   TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '24v0zf5as1y1t',          0 ) )

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID  24v0zf5as1y1t, child number 0
-------------------------------------
SELECT /*+ FIND_ME_PLEASE */ COUNT(*) FROM   user_tables

Plan hash value: 3933268824

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |       |       |   254 (100)|          |
|   1 |  SORT AGGREGATE                  |                  |     1 |   132 |            |          |
|*  2 |   HASH JOIN OUTER                |                  |     8 |  1056 |   253   (1)| 00:00:04 |
|   3 |    NESTED LOOPS OUTER            |                  |     8 |  1032 |   251   (1)| 00:00:04 |
|   4 |     NESTED LOOPS OUTER           |                  |     8 |   976 |   243   (1)| 00:00:03 |
|   5 |      NESTED LOOPS                |                  |     8 |   944 |   243   (1)| 00:00:03 |
|   6 |       NESTED LOOPS OUTER         |                  |     8 |   888 |   235   (1)| 00:00:03 |
|*  7 |        HASH JOIN                 |                  |     8 |   800 |   227   (1)| 00:00:03 |
|   8 |         MERGE JOIN CARTESIAN     |                  |     5 |   355 |    51   (0)| 00:00:01 |
|   9 |          NESTED LOOPS            |                  |     1 |    68 |    48   (0)| 00:00:01 |
|* 10 |           FIXED TABLE FULL       | X$KSPPI          |     1 |    55 |    24   (0)| 00:00:01 |
|* 11 |           FIXED TABLE FIXED INDEX| X$KSPPCV (ind:2) |     1 |    13 |    24   (0)| 00:00:01 |
|  12 |          BUFFER SORT             |                  |     5 |    15 |    27   (0)| 00:00:01 |
|  13 |           TABLE ACCESS FULL      | TS$              |     5 |    15 |     3   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS FULL        | TAB$             |    15 |   435 |   175   (0)| 00:00:03 |
|  15 |        TABLE ACCESS CLUSTER      | SEG$             |     1 |    11 |     1   (0)| 00:00:01 |
|* 16 |         INDEX UNIQUE SCAN        | I_FILE#_BLOCK#   |     1 |       |     0   (0)|          |
|* 17 |       TABLE ACCESS BY INDEX ROWID| OBJ$             |     1 |     7 |     1   (0)| 00:00:01 |
|* 18 |        INDEX UNIQUE SCAN         | I_OBJ1           |     1 |       |     0   (0)|          |
|* 19 |      INDEX UNIQUE SCAN           | I_OBJ1           |     1 |     4 |     0   (0)|          |
|  20 |     TABLE ACCESS BY INDEX ROWID  | OBJ$             |     1 |     7 |     1   (0)| 00:00:01 |
|* 21 |      INDEX UNIQUE SCAN           | I_OBJ1           |     1 |       |     0   (0)|          |
|  22 |    TABLE ACCESS FULL             | USER$            |    59 |   177 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CX"."OWNER#"="CU"."USER#")
   7 - access("T"."TS#"="TS"."TS#")
  10 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  11 - filter("KSPPI"."INDX"="KSPPCV"."INDX")
  14 - filter(BITAND("T"."PROPERTY",1)=0)
  16 - access("T"."TS#"="S"."TS#" AND "T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#")
  17 - filter("O"."OWNER#"=:B1)
  18 - access("O"."OBJ#"="T"."OBJ#")
  19 - access("T"."BOBJ#"="CO"."OBJ#")
  21 - access("T"."DATAOBJ#"="CX"."OBJ#")

48 rows selected.

spool off

    November's Tip of the Month

    Procedure to Convert Numbers to Text
    Compliments of Zakir Gulamali, positiveimager@yahoo.com
    BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20511

    The procedure below converts a value between 1 and a (Zillion -1) to its text equivalent.

    CREATE OR REPLACE PROCEDURE Num2Txt(p_num IN number, p_txt OUT varchar2) IS
-- Procedure to convert a value between 1 and a (Zillion -1) to its Text equivalent
-- Author: Zakir, Harrisburg, PA
-- Email : positiveimager@yahoo.com
-- Will appreciate your feedback - Thanks :)
-- variable Declaration
v_char  		varchar2(50):=null;
v_int			integer:=null;
v_dec_int		integer:=null;
v_dec_char		varchar2(25):=null;
c_trillions_hundreds	varchar2(25):=null;
c_trillions_tens	varchar2(25):=null;
c_trillions_units	varchar2(25):=null;
c_trillions_unit	varchar2(25):=null;
c_billions_hundreds	varchar2(25):=null;
c_billions_tens		varchar2(25):=null;
c_billions_units	varchar2(25):=null;
c_billions_unit		varchar2(25):=null;
c_millions_hundreds	varchar2(25):=null;
c_millions_tens		varchar2(25):=null;
c_millions_units	varchar2(25):=null;
c_millions_unit		varchar2(25):=null;
c_thousands_hundreds	varchar2(25):=null;
c_thousands_tens	varchar2(25):=null;
c_thousands_units	varchar2(25):=null;
c_thousands_unit	varchar2(25):=null;
c_hundreds  		varchar2(25):=null;
c_tens	    		varchar2(25):=null;
c_units     		varchar2(25):=null;
c_unit			varchar2(25):=null;
c_dec_tens	    	varchar2(25):=null;
c_dec_units     	varchar2(25):=null;
c_dec_unit		varchar2(25):=null;
-- Declaring Place holders
p_trillions_hundreds	varchar2(25):=null;
p_trillions_tens	varchar2(25):=null;
p_trillions_units	varchar2(25):=null;
p_trillions_unit	varchar2(25):=null;
p_billions_hundreds	varchar2(25):=null;
p_billions_tens		varchar2(25):=null;
p_billions_units	varchar2(25):=null;
p_billions_unit		varchar2(25):=null;
p_millions_hundreds	varchar2(25):=null;
p_millions_tens		varchar2(25):=null;
p_millions_units	varchar2(25):=null;
p_millions_unit		varchar2(25):=null;
p_thousands_hundreds	varchar2(25):=null;
p_thousands_tens	varchar2(25):=null;
p_thousands_units 	varchar2(25):=null;
p_thousands_unit	varchar2(25):=null;
p_hundreds  		varchar2(25):=null;
p_tens	    		varchar2(25):=null;
p_units     		varchar2(25):=null;
p_unit			varchar2(25):=null;
p_dec_tens	    	varchar2(25):=null;
p_dec_units     	varchar2(25):=null;
p_dec_unit		varchar2(25):=null;
-- Assigning obvious text values
v_one  			varchar2(25):=' One';
v_two  			varchar2(25):=' Two';
v_three 		varchar2(25):=' Three';
v_four  		varchar2(25):=' Four';
v_five  		varchar2(25):=' Five';
v_six  			varchar2(25):=' Six';
v_seven 		varchar2(25):=' Seven';
v_eight 		varchar2(25):=' Eight';
v_nine  		varchar2(25):=' Nine';
v_ten  			varchar2(25):=' Ten';
v_eleven 		varchar2(25):=' Eleven';
v_twelve    		varchar2(25):=' Twelve';
v_thirteen  		varchar2(25):=' Thirteen';
v_fourteen  		varchar2(25):=' Fourteen';
v_fifteen   		varchar2(25):=' Fifteen';
v_sixteen   		varchar2(25):=' Sixteen';
v_seventeen 		varchar2(25):=' Seventeen';	
v_eighteen  		varchar2(25):=' Eighteen';	
v_nineteen  		varchar2(25):=' Nineteen';
v_twenty  		varchar2(25):=' Twenty';
v_thirty  		varchar2(25):=' Thirty';
v_forty   		varchar2(25):=' Forty';
v_fifty   		varchar2(25):=' Fifty';
v_sixty   		varchar2(25):=' Sixty';
v_seventy 		varchar2(25):=' Seventy';
v_eighty 		varchar2(25):=' Eighty';
v_ninety 		varchar2(25):=' Ninety';
v_hundred 		varchar2(25):=' Hundred';
v_thousand 		varchar2(25):=' Thousand';
v_million		varchar2(25):=' Million';
v_billion		varchar2(25):=' Billion';
v_Trillion		varchar2(25):=' Trillion';
BEGIN
-- Seperating value into integer and decimal part
v_int:=floor(p_num);
v_dec_int:=(p_num - v_int)*100;
v_char:=to_char(v_int);
v_dec_char:=to_char(v_dec_int);
-- Storing substring values into respective holders
c_trillions_hundreds    :=substr(v_char,-15,1);
c_trillions_tens        :=substr(v_char,-14,1);
c_trillions_units       :=substr(v_char,-14,2);
c_trillions_unit	:=substr(v_char,-13,1);
c_billions_hundreds     :=substr(v_char,-12,1);
c_billions_tens         :=substr(v_char,-11,1);
c_billions_units        :=substr(v_char,-11,2);
c_billions_unit		:=substr(v_char,-10,1);
c_millions_hundreds	:=substr(v_char,-9,1);
c_millions_tens		:=substr(v_char,-8,1);
c_millions_units	:=substr(v_char,-8,2);
c_millions_unit		:=substr(v_char,-7,1);
c_thousands_hundreds	:=substr(v_char,-6,1);
c_thousands_tens	:=substr(v_char,-5,1);
c_thousands_units	:=substr(v_char,-5,2);
c_thousands_unit	:=substr(v_char,-4,1);
c_hundreds  		:=substr(v_char,-3,1);
c_tens			:=substr(v_char,-2,1);
c_units     		:=substr(v_char,-2,2);
c_unit	    		:=substr(v_char,-1,1);
c_dec_tens	    	:=substr(v_dec_char,1,1);
c_dec_units     	:=substr(v_dec_char,1,2);
c_dec_unit		:=substr(v_dec_char,2,1);
-- Finding pennies!
	IF c_dec_units < '20' THEN
      		CASE c_dec_units
			WHEN '00' THEN p_dec_units:=null;
			WHEN '01' THEN p_dec_units:=v_one;
   			WHEN '02' THEN p_dec_units:=v_two;
			WHEN '03' THEN p_dec_units:=v_three;
			WHEN '04' THEN p_dec_units:=v_four;
 			WHEN '05' THEN p_dec_units:=v_five;
			WHEN '06' THEN p_dec_units:=v_six;
			WHEN '07' THEN p_dec_units:=v_seven;
			WHEN '08' THEN p_dec_units:=v_eight;
			WHEN '09' THEN p_dec_units:=v_nine;
			WHEN '10' THEN p_dec_units:=v_ten;
			WHEN '11' THEN p_dec_units:=v_eleven;
			WHEN '12' THEN p_dec_units:=v_twelve;
			WHEN '13' THEN p_dec_units:=v_thirteen;
			WHEN '14' THEN p_dec_units:=v_fourteen;
			WHEN '15' THEN p_dec_units:=v_fifteen;
			WHEN '16' THEN p_dec_units:=v_sixteen;
			WHEN '17' THEN p_dec_units:=v_seventeen;
			WHEN '18' THEN p_dec_units:=v_eighteen;
			WHEN '19' THEN p_dec_units:=v_nineteen;
          		  ELSE p_dec_units:=null;
      		END CASE c_dec_units;
	ELSE
		CASE c_dec_unit
			WHEN '1' THEN p_dec_unit:=v_one;
			WHEN '2' THEN p_dec_unit:=v_two;
			WHEN '3' THEN p_dec_unit:=v_three;
			WHEN '4' THEN p_dec_unit:=v_four;
			WHEN '5' THEN p_dec_unit:=v_five;
			WHEN '6' THEN p_dec_unit:=v_six;
			WHEN '7' THEN p_dec_unit:=v_seven;
			WHEN '8' THEN p_dec_unit:=v_eight;
			WHEN '9' THEN p_dec_unit:=v_nine;
			  ELSE p_dec_unit:=null;
		END CASE c_dec_unit;
	END IF;
        	CASE c_dec_tens
        		WHEN '2' THEN p_dec_tens:=v_twenty;
			WHEN '3' THEN p_dec_tens:=v_thirty;
			WHEN '4' THEN p_dec_tens:=v_forty;
			WHEN '5' THEN p_dec_tens:=v_fifty;
			WHEN '6' THEN p_dec_tens:=v_sixty;
			WHEN '7' THEN p_dec_tens:=v_seventy;
			WHEN '8' THEN p_dec_tens:=v_eighty;
			WHEN '9' THEN p_dec_tens:=v_ninety;
          	  	  ELSE p_dec_tens:=null;
      		END CASE c_dec_tens;
-- Finding Units, Tens and Hundreds
        IF c_units < '20' THEN
      		CASE c_units
			WHEN '00' THEN p_units:=null;
			WHEN '01' THEN p_units:=v_one;
   			WHEN '02' THEN p_units:=v_two;
			WHEN '03' THEN p_units:=v_three;
			WHEN '04' THEN p_units:=v_four;
 			WHEN '05' THEN p_units:=v_five;
			WHEN '06' THEN p_units:=v_six;
			WHEN '07' THEN p_units:=v_seven;
			WHEN '08' THEN p_units:=v_eight;
			WHEN '09' THEN p_units:=v_nine;
			WHEN '10' THEN p_units:=v_ten;
			WHEN '11' THEN p_units:=v_eleven;
			WHEN '12' THEN p_units:=v_twelve;
			WHEN '13' THEN p_units:=v_thirteen;
			WHEN '14' THEN p_units:=v_fourteen;
			WHEN '15' THEN p_units:=v_fifteen;
			WHEN '16' THEN p_units:=v_sixteen;
			WHEN '17' THEN p_units:=v_seventeen;
			WHEN '18' THEN p_units:=v_eighteen;
			WHEN '19' THEN p_units:=v_nineteen;
          		  ELSE p_units:=null;
      		END CASE c_units;
	ELSE
		CASE c_unit
			WHEN '1' THEN p_unit:=v_one;
			WHEN '2' THEN p_unit:=v_two;
			WHEN '3' THEN p_unit:=v_three;
			WHEN '4' THEN p_unit:=v_four;
			WHEN '5' THEN p_unit:=v_five;
			WHEN '6' THEN p_unit:=v_six;
			WHEN '7' THEN p_unit:=v_seven;
			WHEN '8' THEN p_unit:=v_eight;
			WHEN '9' THEN p_unit:=v_nine;
			  ELSE p_unit:=null;
		END CASE c_unit;
	END IF;
        	CASE c_tens
        		WHEN '2' THEN p_tens:=v_twenty;
			WHEN '3' THEN p_tens:=v_thirty;
			WHEN '4' THEN p_tens:=v_forty;
			WHEN '5' THEN p_tens:=v_fifty;
			WHEN '6' THEN p_tens:=v_sixty;
			WHEN '7' THEN p_tens:=v_seventy;
			WHEN '8' THEN p_tens:=v_eighty;
			WHEN '9' THEN p_tens:=v_ninety;
          	  	  ELSE p_tens:=null;
      		END CASE c_tens;
		CASE c_hundreds
			WHEN '1' THEN p_hundreds:=v_one||v_hundred;
			WHEN '2' THEN p_hundreds:=v_two||v_hundred;
			WHEN '3' THEN p_hundreds:=v_three||v_hundred;
			WHEN '4' THEN p_hundreds:=v_four||v_hundred;
			WHEN '5' THEN p_hundreds:=v_five||v_hundred;
			WHEN '6' THEN p_hundreds:=v_six||v_hundred;
			WHEN '7' THEN p_hundreds:=v_seven||v_hundred;
			WHEN '8' THEN p_hundreds:=v_eight||v_hundred;
			WHEN '9' THEN p_hundreds:=v_nine||v_hundred;
          		  ELSE p_hundreds:=null;
       		END CASE c_hundreds;
-- Finding Thousands
	IF c_thousands_units < '20' THEN
        	CASE c_thousands_units
			WHEN '01' THEN p_thousands_units:=v_one||v_thousand;
			WHEN '02' THEN p_thousands_units:=v_two||v_thousand;
			WHEN '03' THEN p_thousands_units:=v_three||v_thousand;
			WHEN '04' THEN p_thousands_units:=v_four||v_thousand;
			WHEN '05' THEN p_thousands_units:=v_five||v_thousand;
			WHEN '06' THEN p_thousands_units:=v_six||v_thousand;
			WHEN '07' THEN p_thousands_units:=v_seven||v_thousand;
			WHEN '08' THEN p_thousands_units:=v_eight||v_thousand;
			WHEN '09' THEN p_thousands_units:=v_nine||v_thousand;
			WHEN '10' THEN p_thousands_units:=v_ten||v_thousand;
			WHEN '11' THEN p_thousands_units:=v_eleven||v_thousand;
			WHEN '12' THEN p_thousands_units:=v_twelve||v_thousand;
			WHEN '13' THEN p_thousands_units:=v_thirteen||v_thousand;
			WHEN '14' THEN p_thousands_units:=v_fourteen||v_thousand;
			WHEN '15' THEN p_thousands_units:=v_fifteen||v_thousand;
			WHEN '16' THEN p_thousands_units:=v_sixteen||v_thousand;
			WHEN '17' THEN p_thousands_units:=v_seventeen||v_thousand;
			WHEN '18' THEN p_thousands_units:=v_eighteen||v_thousand;
			WHEN '19' THEN p_thousands_units:=v_nineteen||v_thousand;
			  ELSE   p_thousands_units:=null;
		END CASE c_thousands_units;
	ELSE
 		CASE c_thousands_unit
			WHEN '1' THEN p_thousands_unit:=v_one||v_thousand;
         		WHEN '2' THEN p_thousands_unit:=v_two||v_thousand;
			WHEN '3' THEN p_thousands_unit:=v_three||v_thousand;
			WHEN '4' THEN p_thousands_unit:=v_four||v_thousand;
			WHEN '5' THEN p_thousands_unit:=v_five||v_thousand;
			WHEN '6' THEN p_thousands_unit:=v_six||v_thousand;
			WHEN '7' THEN p_thousands_unit:=v_seven||v_thousand;
			WHEN '8' THEN p_thousands_unit:=v_eight||v_thousand;
			WHEN '9' THEN p_thousands_unit:=v_nine||v_thousand;
          		 ELSE p_thousands_unit:=null;
      		END CASE c_thousands_unit;
	END IF;
		CASE c_thousands_tens
        		WHEN '2' THEN p_thousands_tens:=v_twenty;
			WHEN '3' THEN p_thousands_tens:=v_thirty;
			WHEN '4' THEN p_thousands_tens:=v_forty;
			WHEN '5' THEN p_thousands_tens:=v_fifty;
			WHEN '6' THEN p_thousands_tens:=v_sixty;
			WHEN '7' THEN p_thousands_tens:=v_seventy;
			WHEN '8' THEN p_thousands_tens:=v_eighty;
			WHEN '9' THEN p_thousands_tens:=v_ninety;
          	  	  ELSE p_thousands_tens:=null;
      		END CASE c_thousands_tens;	
		CASE c_thousands_hundreds
			WHEN '0' THEN p_thousands_hundreds:=null;
			WHEN '1' THEN p_thousands_hundreds:=v_one||v_hundred;
			WHEN '2' THEN p_thousands_hundreds:=v_two||v_hundred;
			WHEN '3' THEN p_thousands_hundreds:=v_three||v_hundred;
			WHEN '4' THEN p_thousands_hundreds:=v_four||v_hundred;
			WHEN '5' THEN p_thousands_hundreds:=v_five||v_hundred;
			WHEN '6' THEN p_thousands_hundreds:=v_six||v_hundred;
			WHEN '7' THEN p_thousands_hundreds:=v_seven||v_hundred;
			WHEN '8' THEN p_thousands_hundreds:=v_eight||v_hundred;
			WHEN '9' THEN p_thousands_hundreds:=v_nine||v_hundred;
			 ELSE p_thousands_hundreds:=null;
		END CASE c_thousands_hundreds;
-- <<< Millions >>>
	IF c_millions_units < '20' THEN
        	CASE c_millions_units
			-- WHEN '00' THEN p_millions_units:=v_million;
			WHEN '01' THEN p_millions_units:=v_one||v_million;
			WHEN '02' THEN p_millions_units:=v_two||v_million;
			WHEN '03' THEN p_millions_units:=v_three||v_million;
			WHEN '04' THEN p_millions_units:=v_four||v_million;
			WHEN '05' THEN p_millions_units:=v_five||v_million;
			WHEN '06' THEN p_millions_units:=v_six||v_million;
			WHEN '07' THEN p_millions_units:=v_seven||v_million;
			WHEN '08' THEN p_millions_units:=v_eight||v_million;
			WHEN '09' THEN p_millions_units:=v_nine||v_million;
			WHEN '10' THEN p_millions_units:=v_ten||v_million;
			WHEN '11' THEN p_millions_units:=v_eleven||v_million;
			WHEN '12' THEN p_millions_units:=v_twelve||v_million;
			WHEN '13' THEN p_millions_units:=v_thirteen||v_million;
			WHEN '14' THEN p_millions_units:=v_fourteen||v_million;
			WHEN '15' THEN p_millions_units:=v_fifteen||v_million;
			WHEN '16' THEN p_millions_units:=v_sixteen||v_million;
			WHEN '17' THEN p_millions_units:=v_seventeen||v_million;
			WHEN '18' THEN p_millions_units:=v_eighteen||v_million;
			WHEN '19' THEN p_millions_units:=v_nineteen||v_million;
			  ELSE   p_millions_units:=null;
		END CASE c_millions_units;
	ELSE
 		CASE c_millions_unit
			-- WHEN '0' THEN p_millions_unit:=v_million;
			WHEN '1' THEN p_millions_unit:=v_one||v_million;
         		WHEN '2' THEN p_millions_unit:=v_two||v_million;
			WHEN '3' THEN p_millions_unit:=v_three||v_million;
			WHEN '4' THEN p_millions_unit:=v_four||v_million;
			WHEN '5' THEN p_millions_unit:=v_five||v_million;
			WHEN '6' THEN p_millions_unit:=v_six||v_million;
			WHEN '7' THEN p_millions_unit:=v_seven||v_million;
			WHEN '8' THEN p_millions_unit:=v_eight||v_million;
			WHEN '9' THEN p_millions_unit:=v_nine||v_million;
          		 ELSE p_millions_unit:=null;
      		END CASE c_millions_unit;
	END IF;
		CASE c_millions_tens
        		WHEN '2' THEN p_millions_tens:=v_twenty;
			WHEN '3' THEN p_millions_tens:=v_thirty;
			WHEN '4' THEN p_millions_tens:=v_forty;
			WHEN '5' THEN p_millions_tens:=v_fifty;
			WHEN '6' THEN p_millions_tens:=v_sixty;
			WHEN '7' THEN p_millions_tens:=v_seventy;
			WHEN '8' THEN p_millions_tens:=v_eighty;
			WHEN '9' THEN p_millions_tens:=v_ninety;
          	  	  ELSE p_millions_tens:=null;
      		END CASE c_millions_tens;
		CASE c_millions_hundreds
			WHEN '0' THEN p_millions_hundreds:=null;
			WHEN '1' THEN p_millions_hundreds:=v_one||v_hundred;
			WHEN '2' THEN p_millions_hundreds:=v_two||v_hundred;
			WHEN '3' THEN p_millions_hundreds:=v_three||v_hundred;
			WHEN '4' THEN p_millions_hundreds:=v_four||v_hundred;
			WHEN '5' THEN p_millions_hundreds:=v_five||v_hundred;
			WHEN '6' THEN p_millions_hundreds:=v_six||v_hundred;
			WHEN '7' THEN p_millions_hundreds:=v_seven||v_hundred;
			WHEN '8' THEN p_millions_hundreds:=v_eight||v_hundred;
			WHEN '9' THEN p_millions_hundreds:=v_nine||v_hundred;
			 ELSE p_millions_hundreds:=null;
		END CASE c_millions_hundreds;
-- <<< Billions >>>
	IF c_billions_units < '20' THEN
        	CASE c_billions_units
			-- WHEN '00' THEN p_billions_units:=v_billion;
			WHEN '01' THEN p_billions_units:=v_one||v_billion;
			WHEN '02' THEN p_billions_units:=v_two||v_billion;
			WHEN '03' THEN p_billions_units:=v_three||v_billion;
			WHEN '04' THEN p_billions_units:=v_four||v_billion;
			WHEN '05' THEN p_billions_units:=v_five||v_billion;
			WHEN '06' THEN p_billions_units:=v_six||v_billion;
			WHEN '07' THEN p_billions_units:=v_seven||v_billion;
			WHEN '08' THEN p_billions_units:=v_eight||v_billion;
			WHEN '09' THEN p_billions_units:=v_nine||v_billion;
			WHEN '10' THEN p_billions_units:=v_ten||v_billion;
			WHEN '11' THEN p_billions_units:=v_eleven||v_billion;
			WHEN '12' THEN p_billions_units:=v_twelve||v_billion;
			WHEN '13' THEN p_billions_units:=v_thirteen||v_billion;
			WHEN '14' THEN p_billions_units:=v_fourteen||v_billion;
			WHEN '15' THEN p_billions_units:=v_fifteen||v_billion;
			WHEN '16' THEN p_billions_units:=v_sixteen||v_billion;
			WHEN '17' THEN p_billions_units:=v_seventeen||v_billion;
			WHEN '18' THEN p_billions_units:=v_eighteen||v_billion;
			WHEN '19' THEN p_billions_units:=v_nineteen||v_billion; 
			  ELSE   p_billions_units:=null;
		END CASE c_billions_units;
	ELSE
 		CASE c_billions_unit
			WHEN '0' THEN p_billions_unit:=v_billion;
			WHEN '1' THEN p_billions_unit:=v_one||v_billion;
         		WHEN '2' THEN p_billions_unit:=v_two||v_billion;
			WHEN '3' THEN p_billions_unit:=v_three||v_billion;
			WHEN '4' THEN p_billions_unit:=v_four||v_billion;
			WHEN '5' THEN p_billions_unit:=v_five||v_billion;
			WHEN '6' THEN p_billions_unit:=v_six||v_billion;
			WHEN '7' THEN p_billions_unit:=v_seven||v_billion;
			WHEN '8' THEN p_billions_unit:=v_eight||v_billion;
			WHEN '9' THEN p_billions_unit:=v_nine||v_billion;
          		 ELSE p_billions_unit:=null;
      		END CASE c_billions_unit;
	END IF;
		CASE c_billions_tens
        		WHEN '2' THEN p_billions_tens:=v_twenty;
			WHEN '3' THEN p_billions_tens:=v_thirty;
			WHEN '4' THEN p_billions_tens:=v_forty;
			WHEN '5' THEN p_billions_tens:=v_fifty;
			WHEN '6' THEN p_billions_tens:=v_sixty;
			WHEN '7' THEN p_billions_tens:=v_seventy;
			WHEN '8' THEN p_billions_tens:=v_eighty;
			WHEN '9' THEN p_billions_tens:=v_ninety;
          	  	  ELSE p_billions_tens:=null;
      		END CASE c_billions_tens;
		CASE c_billions_hundreds
			WHEN '0' THEN p_billions_hundreds:=null;
			WHEN '1' THEN p_billions_hundreds:=v_one||v_hundred;
			WHEN '2' THEN p_billions_hundreds:=v_two||v_hundred;
			WHEN '3' THEN p_billions_hundreds:=v_three||v_hundred;
			WHEN '4' THEN p_billions_hundreds:=v_four||v_hundred;
			WHEN '5' THEN p_billions_hundreds:=v_five||v_hundred;
			WHEN '6' THEN p_billions_hundreds:=v_six||v_hundred;
			WHEN '7' THEN p_billions_hundreds:=v_seven||v_hundred;
			WHEN '8' THEN p_billions_hundreds:=v_eight||v_hundred;
			WHEN '9' THEN p_billions_hundreds:=v_nine||v_hundred;
			 ELSE p_billions_hundreds:=null;
		END CASE c_billions_hundreds;
-- <<< Trillions >>>
	IF c_trillions_units < '20' THEN
        	CASE c_trillions_units
			-- WHEN '00' THEN p_trillions_units:=v_trillion;
			WHEN '01' THEN p_trillions_units:=v_one||v_trillion;
			WHEN '02' THEN p_trillions_units:=v_two||v_trillion;
			WHEN '03' THEN p_trillions_units:=v_three||v_trillion;
			WHEN '04' THEN p_trillions_units:=v_four||v_trillion;
			WHEN '05' THEN p_trillions_units:=v_five||v_trillion;
			WHEN '06' THEN p_trillions_units:=v_six||v_trillion;
			WHEN '07' THEN p_trillions_units:=v_seven||v_trillion;
			WHEN '08' THEN p_trillions_units:=v_eight||v_trillion;
			WHEN '09' THEN p_trillions_units:=v_nine||v_trillion;
			WHEN '10' THEN p_trillions_units:=v_ten||v_trillion;
			WHEN '11' THEN p_trillions_units:=v_eleven||v_trillion;
			WHEN '12' THEN p_trillions_units:=v_twelve||v_trillion;
			WHEN '13' THEN p_trillions_units:=v_thirteen||v_trillion;
			WHEN '14' THEN p_trillions_units:=v_fourteen||v_trillion;
			WHEN '15' THEN p_trillions_units:=v_fifteen||v_trillion;
			WHEN '16' THEN p_trillions_units:=v_sixteen||v_trillion;
			WHEN '17' THEN p_trillions_units:=v_seventeen||v_trillion;
			WHEN '18' THEN p_trillions_units:=v_eighteen||v_trillion;
			WHEN '19' THEN p_trillions_units:=v_nineteen||v_trillion; 
			  ELSE   p_trillions_units:=null;
		END CASE c_trillions_units;
	ELSE
 		CASE c_trillions_unit
			WHEN '0' THEN p_trillions_unit:=v_trillion;
			WHEN '1' THEN p_trillions_unit:=v_one||v_trillion;
         		WHEN '2' THEN p_trillions_unit:=v_two||v_trillion;
			WHEN '3' THEN p_trillions_unit:=v_three||v_trillion;
			WHEN '4' THEN p_trillions_unit:=v_four||v_trillion;
			WHEN '5' THEN p_trillions_unit:=v_five||v_trillion;
			WHEN '6' THEN p_trillions_unit:=v_six||v_trillion;
			WHEN '7' THEN p_trillions_unit:=v_seven||v_trillion;
			WHEN '8' THEN p_trillions_unit:=v_eight||v_trillion;
			WHEN '9' THEN p_trillions_unit:=v_nine||v_trillion;
          		 ELSE p_trillions_unit:=null;
      		END CASE c_trillions_unit;
	END IF;
		CASE c_trillions_tens
        		WHEN '2' THEN p_trillions_tens:=v_twenty;
			WHEN '3' THEN p_trillions_tens:=v_thirty;
			WHEN '4' THEN p_trillions_tens:=v_forty;
			WHEN '5' THEN p_trillions_tens:=v_fifty;
			WHEN '6' THEN p_trillions_tens:=v_sixty;
			WHEN '7' THEN p_trillions_tens:=v_seventy;
			WHEN '8' THEN p_trillions_tens:=v_eighty;
			WHEN '9' THEN p_trillions_tens:=v_ninety;
          	  	  ELSE p_trillions_tens:=null;
      		END CASE c_trillions_tens;
		CASE c_trillions_hundreds
			WHEN '0' THEN p_trillions_hundreds:=null;
			WHEN '1' THEN p_trillions_hundreds:=v_one||v_hundred;
			WHEN '2' THEN p_trillions_hundreds:=v_two||v_hundred;
			WHEN '3' THEN p_trillions_hundreds:=v_three||v_hundred;
			WHEN '4' THEN p_trillions_hundreds:=v_four||v_hundred;
			WHEN '5' THEN p_trillions_hundreds:=v_five||v_hundred;
			WHEN '6' THEN p_trillions_hundreds:=v_six||v_hundred;
			WHEN '7' THEN p_trillions_hundreds:=v_seven||v_hundred;
			WHEN '8' THEN p_trillions_hundreds:=v_eight||v_hundred;
			WHEN '9' THEN p_trillions_hundreds:=v_nine||v_hundred;
			 ELSE p_trillions_hundreds:=null;
		END CASE c_trillions_hundreds;
-- Force Logic 
   IF (c_thousands_unit='0' AND c_thousands_units='00' AND c_thousands_tens='0'
       AND c_thousands_hundreds !='0') THEN
        p_thousands_unit:=' Thousand';
   END IF;
IF (c_millions_unit='0' AND c_millions_units='00' AND c_millions_tens='0'
    AND c_millions_hundreds !='0') THEN
    p_millions_unit:=' Million';
END IF;
   IF (c_billions_unit='0' AND c_billions_units='00' AND c_billions_tens='0'
       AND c_billions_hundreds !='0') THEN
        p_billions_unit:=' Billion';
   END IF;
IF (c_trillions_unit='0' AND c_trillions_units='00' AND c_trillions_tens='0'
    AND c_trillions_hundreds !='0') THEN
    p_trillions_unit:=' Trillion';
END IF;
IF v_dec_char = '0' THEN
   p_dec_unit:=' zero';
END IF;
-- <<< Finally, the output >>>
p_txt:='Dollars<'||p_trillions_hundreds||p_trillions_tens||p_trillions_units
                 ||p_trillions_unit||p_billions_hundreds||p_billions_tens
                 ||p_billions_units||p_billions_unit||p_millions_hundreds
                 ||p_millions_tens||p_millions_units||p_millions_unit
                 ||p_thousands_hundreds||p_thousands_tens||p_thousands_units
                 ||p_thousands_unit||p_hundreds||p_tens||p_units||p_unit||'>'
                 ||' AND <'||p_dec_tens||p_dec_units||p_dec_unit||'> Cents';
END;
/

    Pipeliner Rich Jesse points out that this code, while valid, is a bit verbose and notes that there are alternative solutions available:

    "A search for "spell a number" on http://asktom.oracle.com shows a simpler method. A big benefit being that the latter will be much easier to maintain for locale, since it's using Oracle's date functions to get the majority of the spelling."

    December's Tip of the Month

    Name Those Exceptions!
    Compliments of Steven Feuerstein, from the October issue of OPP/News
    BOOKMARK: http://www.quest-pipelines.com/tiptrack.asp?id=20512

    When a SELECT INTO does not return at least one row, Oracle raises the NO_DATA_FOUND exception. Most PL/SQL developers probably think this exception is some sort of built-in, reserved word exception in the PL/SQL language. It is not. rather, it is simply one of a relatively small number of exceptions that are defined in the STANDARD package. This package is one of two default packages in PL/SQL (the other being DBMS_STANDARD), which means that when you reference elements in either of these packages, you don't have to qualify them with the name of their package (though you can if you want to). Consider the following block of code: 

    DECLARE
     l_counter PLS_INTEGER;
     l_counter2 STANDARD.PLS_INTEGER;
     VARCHAR2 NUMBER;
    BEGIN
     SELECT 1
     INTO l_counter
     FROM SYS.DUAL
     WHERE 1 = 2;

     DBMS_STANDARD.COMMIT;
    EXCEPTION
     WHEN STANDARD.NO_DATA_FOUND
     THEN
     DBMS_STANDARD.raise_application_error (-20000, 'Yikes!');
    END;>

    This code will compile, which might come as a surprise to you. Conclusions to draw from this example:

    1. Many of the elements you consider to be reserved words in PL/SQL are simply datatypes, structures or programs defined in the STANDARD package. You can use those names in your own code (notice my declaration of a variable named "varchar2"), but I certainly suggest that you not do that. The resulting code is just too, too confusing.

    2. You can qualify references to elements in the default packages with the package name, but again I would recommend you not do this, to avoid extra clutter in your code.

    Now, I strongly encourage you to take some time to study the STANDARD package (found in the Rdbms/Admin subdirectory under your Oracle Home directory, now defined in two separate files: stdspec.sql and stdbody.sql). You can learn an awful lot about the PL/SQL language. For one thing, you will see that all of the system exceptions like NO_DATA_FOUND are created using the EXCEPTION_INIT pragma. Here is the statement for NO_DATA_FOUND:

    NO_DATA_FOUND EXCEPTION;
    PRAGMA EXCEPTION_INIT (NO_DATA_FOUND, 100);

    And here you thought Oracle errors were negative! Well, NO_DATA_FOUND is an oddity in the Oracle world: it has two error numbers associated with it, 100 (carried over from the earliest days of compliance with the ANSI standard) and -1403. The EXCEPTION_INIT pragma references 100, but -1403 comes along for the ride. [Actually, for whatever reason, Oracle will not allow you to use -1403 in an EXCEPTION_INIT pragma!]

    Thus, you could replace this exception section:

    EXCEPTION
     WHEN NO_DATA_FOUND
     THEN
     ...

    with this one:

    EXCEPTION
     WHEN OTHERS
     THEN
     IF SQLCODE = -1403
     THEN
     ...

    but you certainly wouldn't want to do this. The code is much less readable.

    Well, that same rule (namely, it is better to reference exceptions by name, rather than number) applies to Oracle exceptions for which there are no associated names. In this case, you will need to declare your own named exception and use the EXCEPTION_INIT pragma yourself. Let's take a look at an example.

    Hopefully you are all aware of the FORALL statement, which allows you to perform multiple DML statements much more efficiently than through row-by-row processing. If FORALL is news to you, then please go out and buy my book, Oracle PL/SQL Programming, and dive into chapter 14. Or visit Oracle's on-line doc center at:

    PL/SQL User Guide and Reference: FORALL

    Now, in Oracle9i Database Release 2, Oracle added the SAVE EXCEPTIONS clause to FORALL. If you use this clause, then Oracle will execute each DML statement associated with the specified rows of the driving collection. If it encounters one or more errors, it saves those errors in a pseudo collection named SQL%BULK_EXCEPTIONS. When it has completed all the DML processing and at least one error has occurred, Oracle will then raise the generic "FORALL failure" error, which has been assigned the number -24381.

    So you could write code like this when working with FORALL and SAVE EXCEPTIONS:

    EXCEPTION
     WHEN OTHERS
     THEN
     IF SQLCODE = -24381 
     THEN
     FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
     LOOP
     ... process the errors ...
     END LOOP;
     END IF;
    END;

    This would, however, be a bad idea. Do you think you will remember what -24381 refers to? I am pretty certain I will, but that is because I am an obsessive individual when it comes to PL/SQL. You surely have better things to do with your mind. Putting aside the issue of remembering, code like "SQLCODE = -24381" is also downright bewildering and intimidating. Developers coming across this code in later years will feel terribly inadequate because the code clearly expects them to know what that means, but they have no idea. So this code creates bad vibes, thereby reducing the quality of life of programmers, leading to more bugs, and possibly the collapse of civilization as we know it.

    See? Programmers can be super-heroes and save our world, too! Just don't hard code error numbers in the exception seciton. Well, yes, it would certainly be nice to reference a named exception in that part of this program, but Oracle did not choose to associate a name with -24381. That's all right, though. Don't give up. Just because Oracle didn't see fit to do this doesn't mean that you have to write nasty code. Instead, you can give your own name to this exception. Consider the following procedure (truncated implementation shown to allow us to focus on the key elements for the tip at hand):

    PROCEDURE upd_for_dept (
     dept_in IN employee.department_id%TYPE
     , newsal IN employee.salary%TYPE
    )
    IS
     bulk_errors EXCEPTION;
     PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
     ...
    BEGIN
     ...
     FORALL indx IN employees.FIRST .. employees.LAST SAVE EXCEPTIONS
     INSERT INTO employee_history
     (employee_id, salary, hire_date
     )
     VALUES (employees (indx), salaries (indx), hire_dates (indx)
     );
    EXCEPTION
     WHEN bulk_errors
     THEN
        FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
           log_error (SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
        END LOOP;
    END upd_for_dept;

    Notice that at the very beginning of my procedure I declare an exception and associate it with -24381. Then in my exception section, I reference the exception by name. The result is much more readable code, which will make programmers feel better about themselves and also preserve our quality of life.

    Bottom line: give names to any Oracle errors which you need to trap and handle in your code. Generally avoid placing obscure, scary, hard-coded numbers in any part of your application.

    PL/SQL Pipeline

    For questions or comments on this site: webmaster@quest-pipelines.com
    All content Copyright © Quest Software, Inc. All rights reserved.