Oracle PL/SQL Tips of the Month- 2000
 

January	How to Compare the Contents of Two Tables
February	Maintaining One Version of Code for Both Definer and Invoker Rights Models
March	Exception Handling and Rolling Back DML
April	Tailoring Your Code to Answer the Question
May	Safe Trapping of Failed DBMS_JOB Job
June	Achieving Aligned Decimals
July	Wildcard Characters and the LIKE Operator
August	Using LIKE Instead of SUBSTR
September	Identifying Valid UTL_FILE Directories
October	Indexing Nulls
November	Invoker Rights Method Leads to Lots of Parsing
December	Encapsulate Dynamic SQL Parsing to Improve Error Detection and Clean Up

1999 Tips
1998 Tips
1997 Tips

January's Tip of the Month

How to compare the contents of two tables

Have you ever wanted to compare the contents of two tables to make sure they are identical? A PL/SQL Pipeliner suggested that you should run the following two queries:

SELECT * FROM table1

MINUS

SELECT * FROM table2;


SELECT * FROM table2

MINUS

SELECT * FROM table1;

SELECT table1.*,count(*)

  FROM table1

 GROUP BY column1,column2,...

MINUS

SELECT table2.*,count(*)

  FROM table2

 GROUP BY column1,column2,...

SELECT table2.*,count(*)

  FROM table2

 GROUP BY column1,column2,...

MINUS

SELECT table1.*,count(*)

  FROM table1

 GROUP BY column1,column2,...

February's Tip of the Month

Update to February's Tip - February 15

Maintaining One Version of Code for Both Definer and Invoker Rights Models

Oracle8i introduces the invoker rights model,which allows code to resolve references to tables and views at run-time, based on the invoker and not the definer privileges. (See my latest book, Oracle PL/SQL Programming Guide to Oracle Features, for lots more details).

That is really great! One problem that you can run into, however, is needing to maintain a program for both Oracle8i and prior releases, where this feature was not available. I have come up with a way to use SQL*Plus substitution variables to achieve this effect. In the code you see below...

1. I determine the Oracle version (works for 7.3 and above).
    
2. Use the version to set the values of substitution parameters so that my AUTHID CURRENT_USER syntax (used for requesting invoker rights) is either commented out (prior to Oracle8i) or activated.

SET FEEDBACK OFF

SET VERIFY ON

REM

REM SQL*Plus script that automatically detects the Oracle version

REM and sets substitution variables that turn on or off

REM the new 8i AUTHID CURRENT_USER feature

REM

REM Steven Feuerstein

REM Detect version and set variables accordingly.


COLUMN col NOPRINT NEW_VALUE v_orcl_vers


SELECT SUBSTR(version,1,3) col

  FROM product_component_version

 WHERE UPPER(product) LIKE 'ORACLE7%'

    OR UPPER(product) LIKE 'PERSONAL ORACLE%'

    OR UPPER(product) LIKE 'ORACLE8%';


COLUMN col NOPRINT NEW_VALUE authidopen

SELECT DECODE (upper('&v_orcl_vers'),

               '8.1', '/**/',

               '/* Oracle 8.1 Only!') col  

  FROM dual;


COLUMN col NOPRINT NEW_VALUE authidclose

SELECT DECODE (upper('&v_orcl_vers'),

               '8.1', '/**/',

               '*/') col  

  FROM dual;

    

REM Now I can use those variables...


CREATE OR REPLACE PROCEDURE either_way 

   &authidopen AUTHID CURRENT_USER &authidclose

IS

   v_count PLS_INTEGER;

BEGIN

   SELECT COUNT(*) INTO v_count FROM emp;   

   DBMS_OUTPUT.PUT_LINE ('Emp count: ' || v_count);

END;

/

GRANT EXECUTE ON either_way TO PUBLIC;

SET SERVEROUTPUT ON

exec either_way;

CONNECT DEMO/DEMO

SET SERVEROUTPUT ON

DELETE FROM emp;

exec SCOTT.either_way;

ROLLBACK;

CONNECT SCOTT/TIGER

REM Now force a 7.3 implementation and compare the results...

DEFINE authidopen = /*

DEFINE authidclose = */

CREATE OR REPLACE PROCEDURE either_way 

   &authidopen AUTHID CURRENT_USER &authidclose

IS

   v_count PLS_INTEGER;

BEGIN

   SELECT COUNT(*) INTO v_count FROM emp;   

   DBMS_OUTPUT.PUT_LINE ('emp count: ' || v_count);

END;

/

GRANT EXECUTE ON either_way TO PUBLIC;

SET SERVEROUTPUT ON

exec either_way;

CONNECT DEMO/DEMO

SET SERVEROUTPUT ON

DELETE FROM emp;

exec SCOTT.either_way;

ROLLBACK;

CONNECT SCOTT/TIGERSET FEEDBACK OFF

February Tip of the Month Add-On: February 15, 2000

We received this suggestion for a shorter version of our February Tip from PL/SQL user Steve Cosner:

"I played with your Feb. tip,and have a little shorter version. You might like this -- all you need is one COLUMN command and one SELECT:"

COLUMN authid NOPRINT NEW_VALUE authid

SELECT 'AUTHID CURRENT_USER' authid

  FROM product_component_version

 WHERE INSTR(UPPER(product),'ORACLE') > 0

   AND SUBSTR(version,1,3) >= '8.1';

SQL> CONNECT simrusr/******@ATEST

Connected.

SQL> UNDEFINE authid

SQL> COLUMN authid NOPRINT NEW_VALUE authid

SQL> SELECT 'AUTHID CURRENT_USER' authid

  2    FROM product_component_version

  3   WHERE INSTR(UPPER(product),'ORACLE') > 0

  4     AND SUBSTR(version,1,3) >= '8.1';

SQL> DEFINE

DEFINE _O_VERSION = "Oracle8i Enterprise Edition Release 8.1.5.0.0

DEFINE AUTHID     = "AUTHID CURRENT_USER" (CHAR)

SQL> CREATE OR REPLACE PROCEDURE P1 &AUTHID IS BEGIN NULL;END;

  2  /

old   1: CREATE OR REPLACE PROCEDURE P1 &AUTHID IS BEGIN NULL;END;

new   1: CREATE OR REPLACE PROCEDURE P1 AUTHID CURRENT_USER IS BEGIN

NULL;END;

Procedure created.

SQL> SHOW ERRORS PROCEDURE P1

No errors.

SQL> DROP PROCEDURE P1;


Procedure dropped.


SQL> CONNECT AGTMS09/******@CASA

Connected.

SQL> UNDEFINE authid

SQL> COLUMN authid NOPRINT NEW_VALUE authid

SQL> SELECT 'AUTHID CURRENT_USER' authid

  2    FROM product_component_version

  3   WHERE INSTR(UPPER(product),'ORACLE') > 0

  4     AND SUBSTR(version,1,3) >= '8.1';


no rows selected


SQL> DEFINE

DEFINE _O_VERSION="Oracle7 Server Release 7.3.2.2.0 - Production Release

DEFINE AUTHID     = "" (CHAR)

SQL> CREATE OR REPLACE PROCEDURE P1 &AUTHID IS BEGIN NULL;END;

  2  /

old   1: CREATE OR REPLACE PROCEDURE P1 &AUTHID IS BEGIN NULL;END;

new   1: CREATE OR REPLACE PROCEDURE P1  IS BEGIN NULL;END;

Procedure created.

SQL> SHOW ERRORS PROCEDURE P1

No errors.

SQL> DROP PROCEDURE P1;

Procedure dropped.

March's Tip of the Month

Exception Handling and Rolling Back DML

Have you ever written an application that uses a database table as an error log? You run your application. It hits an error, writes information to the log, and then the exception goes unhandled. You see the error stack in SQL*Plus and you immediately run a query against the log to see what went wrong...and the log is empty! You scratch your head and wonder what happened to the log information. Well, be puzzled no longer -- after you read this tip, anyway.

DML stands for Data Manipulation Language. There are three DML statements in SQL: INSERT, UPDATE and DELETE.

There is often lots of confusion in the minds of PL/SQL developers about the interaction between exception handling and DML. Specifically, the question arises: if I perform an INSERT in my procedure and then an exception is raised, is my INSERT rolled back?

Here's the scoop:

The state of your transaction (pending, committed, rolled back) is not affected by the raising of an exception in a block of PL/SQL code. From within PL/SQL, your changes are committed only when you issue a COMMIT, and your changes are rolled back only when you issue a ROLLBACK statement.

Suppose, in other words, that you execute an UPDATE of 1,000 rows of data and then following that perform a SELECT INTO that raises a NO_DATA_FOUND exception. You have an exception section that traps the error and displays a message to the user. When you run your program, it will terminate due to the exception. Control will then pass to the outer block (if there is one). But the 1,000 updated rows are still waiting to be committed or rolled back.

There is one important nuance to this general rule: if your exception propagates all the way out of the outermost PL/SQL block (the exception, in other words, goes unhandled), then you will find that most host environments (SQL*Plus, for example) will automatically ROLLBACK any uncommitted DML.

And that is why the error log is empty after running a test that results in an unhandled exception.

April's Tip of the Month

Answer the Question Asked!

Being a good listener is a crucial skill for human beings, whether it's a matter of being responsive to your significant other or understanding what your users need you to build. If you don't listen to what's being asked of you, the chance of you giving the right answer is small at best.

Consider the following requirement:

"If Steven has more than one favorite flavor of ice cream, give him a 20% raise so that he can afford to buy all the cones he'll want this summer."

Here is one possible implementation of that task:

DECLARE

   numflavs PLS_INTEGER;

BEGIN

   SELECT COUNT(*) INTO numflavs

     FROM fav_flavors

    WHERE name = 'STEVEN'

      AND item = 'ICE CREAM';

   IF numflavs > 1

   THEN

      UPDATE compensation

         SET salary = salary * 1.2

       WHERE name = 'STEVEN';

   END IF;

END;

1. "Does Steven have more than one favorite flavor of ice cream?"
    
2. "How many favorite flavors of ice cream does Steven have?"

Here is a rewrite of my code that provides an answer to the question asked and is, as a rule, much more efficient:

DECLARE

   atleastone BOOLEAN := FALSE;

   CURSOR mmmm_cur IS

      SELECT 1 /* Don't really need to get anything */

        FROM fav_flavors

       WHERE name = 'STEVEN'

         AND item = 'ICE CREAM';

   mmmm_rec mmmm_cur%ROWTYPE;

BEGIN

   OOPEN mmmm_cur;

   FETCH mmmm_cur INTO mmmm_rec;

   IF mmmm_cur%FOUND

   THEN

      FETCH mmmm_cur INTO mmmm_rec;

      atleastone := mmmm_cur%FOUND;

   END IF;

   CLOSE mmmm_cur;

   IF atleastone

   THEN

      UPDATE compensation

         SET salary = salary * 1.2

       WHERE name = 'STEVEN';

   END IF;

END;

CREATE OR REPLACE FUNCTION lotsa_favflavs (

   Name_in IN VARCHAR2, item_in IN VARCHAR2)

   RETURN BOOLEAN

IS

   atleastone BOOLEAN := FALSE;


   CURSOR mmmm_cur IS

      SELECT 1 /* Don't really need to get anything */

        FROM fav_flavors

       WHERE name = name_in

         AND item = item_in;

   mmmm_rec mmmm_cur%ROWTYPE;

BEGIN

   OOPEN mmmm_cur;

   FETCH mmmm_cur INTO mmmm_rec;

   IF mmmm_cur%FOUND

   THEN

      FETCH mmmm_cur INTO mmmm_rec;

      atleastone := mmmm_cur%FOUND;

   END IF;

   CLOSE mmmm_cur;

   RETURN atleastone;

END;

/

BEGIN

   IF lotsa_favflavs ('STEVEN', 'ICE CREAM')

   THEN

      UPDATE compensation

         SET salary = salary * 1.2

       WHERE name = 'STEVEN';

   END IF;

END;

May's Tip of the Month

Safe Trapping of Failed DBMS_JOB Job
From the compute-intensive brain of John Beresniewicz, Savant Corporation via the PL/SQL Pipeline:

A while back, I gave my DBMS_JOB talk at the EOUG (European Oracle User Group) and cooked up this example of how to safely trap the error message from a failing job (in which you might not have access to source code and must simply work with the program call only.) Note the following aspects:

1. Use an anonymous PL/SQL block for job definition.
2. Rollback any pending transactions for failing job.
3. Capture and write Oracle exception error message to table.
4. Re-raise exception (optional) allowing job queue to apply its failure logic.

Here is the job submission call, where "failingproc" is the stub for the name of the program and any parameters that are failing:

DBMS_JOB.SUBMIT (

   job=>jobno

   ,what=>'DECLARE errcode NUMBER;'|| 

          'BEGIN failingproc;' || 

          'EXCEPTION WHEN OTHERS ||

               'THEN errcode := SQLCODE; ROLLBACK;'||

                   ' log_error(SQLERRM(errcode));'||

                   ' COMMIT; RAISE; END;'

   ,next_date=> SYSDATE

   ,interval=>'SYSDATE+1');

PROCEDURE log_error(errmsg_IN IN VARCHAR2) 

IS

BEGIN

   INSERT INTO error_log(errmsg)

   VALUES (errmsg_IN);

END log_error;

June's Tip of the Month

Achieving Aligned Decimals

Steve Cosner (stevec@csufresno.edu), a very devoted PL/SQL Pipeliner offering some really great Oracle Forms utilities on his site, had this to say a few weeks ago: "You have to be an HTML jockey to get things to line up on this WebBoard. And you have to be a character function jockey to get text to line up using PL/SQL. Here's a SQL Plus script I put together to achieve aligned decimals. Have fun reading the code!"

/* Formatted by PL/Formatter v3.0.5.0 on 2000/05/30 09:36 */

SET SERVEROUTPUT ON SIZE 20000 FORMAT WRAPPED

DECLARE

   PROCEDURE prnt (n NUMBER)
   IS
      txt0 VARCHAR2 (20);
      txt1 VARCHAR2 (20);
      txt2 VARCHAR2 (20);
      txt3 VARCHAR2 (20);
      txt4 VARCHAR2 (20);
   BEGIN
      txt0 := RPAD (TO_CHAR (n), 8);
      txt1 := TO_CHAR (n, '9999990.9999');
      txt2 :=
            RPAD (RTRIM (TO_CHAR (n, '99990.9999'), '0'), 12);
      txt3 :=
        RPAD (
           RTRIM (RTRIM (TO_CHAR (n, '99990.9999'), '0'), '.'),
           12
        );
      txt4 :=
        REPLACE (
           RPAD (RTRIM (TO_CHAR (n, '99990.9999'), '0'), 12),
           '. ',
           '.0'
        );
      DBMS_OUTPUT.put_line (
         '   ' || txt0 || txt1 || txt2 || txt3 || txt4
      );
   END prnt;
BEGIN
   DBMS_OUTPUT.put_line (
      '    Input          Txt1      Txt2  ' ||
         '      Txt3        Txt4  '
   );
   DBMS_OUTPUT.put_line (
      '  ---------     --------   --------' ||
         '    --------    --------'
   );
   prnt (1.24);
   prnt (2.356);
   prnt (12.1248);
   prnt (0);
   prnt (.123);
   prnt (456);
END;

     Input          Txt1      Txt2        Txt3        Txt4

  ---------     --------   --------    --------    --------

   1.24           1.2400     1.24        1.24        1.24

   2.356          2.3560     2.356       2.356       2.356

   12.1248       12.1248    12.1248     12.1248     12.1248

   0              0.0000     0.          0           0.0

   .123           0.1230     0.123       0.123       0.123

   456          456.0000   456.        456         456.0

July's Tip of the Month

Wildcard Characters and the LIKE Operator

Let's explore a nuance of the LIKE operator...

expression LIKE 'pattern' [ESCAPE 'escape-character']

_ Match to any single character in this position

% Match to any number of characters in this position

(As you can see, "*" is not a wildcard character in SQL.)

These wildcard characters come in handy when using the LIKE operator. If, for example, I want to display the names of everyone who likes any variety of chocolate ice cream, I could write this query:

SELECT name
  FROM general_preference
 WHERE flavor LIKE 'CHOC%'
   AND topic = 'ICE CREAM';

SELECT company_id
  FROM order
 WHERE order_id LIKE RPAD ('_', '_', 15) || '3%';

How do I answer the question "Show me all database objects that contain an underscore?" I'd like to write the query as follows:

SELECT object_name
  FROM user_objects
 WHERE object_name LIKE '%_%';

SELECT object_name
  FROM user_objects
 WHERE object_name LIKE '%^_%' ESCAPE '^';

August's Tip of the Month

Using LIKE Instead of SUBSTR

Do you LIKE to SUBSTR?

I need to implement the following requirement:

"If the first three characters of the pet type is CAT, then buy catnip. If the first three characters of the pet type is DOG, then buy milkbones."

What's the best way to satisfy this requirement? Well, I know how to identify the first three characters of a string...use SUBSTR. So my first inclination would be to code as follows:

v_first_three := SUBSTR (pet_type, 1, 3);

IF v_first_three = 'CAT'

THEN

   buy_catnip;

ELSIF v_first_three = 'DOG'

THEN

   buy_milkbones;

END IF;

IF pet_type LIKE 'CAT%'

THEN

   buy_catnip;

ELSIF pet_type LIKE 'DOG%'

THEN

   buy_milkbones;

END IF;

My conclusion: when checking the contents of a beginning of a string, use LIKE instead of SUBSTR.

September's Tip of the Month

Identifying Valid UTL_FILE Directories
Compliments of Dan Clamage

The UTL_FILE package can read and write only with directories specified by the UTL_FILE_DIR initialization parameter. Unlike many other parameters, you can actually have more than one UTL_FILE_DIR entry. How can a developer determine whether or not a directory is supported by UTL_FILE? You can run the following query:

select value from v$parameter
where name='utl_file_dir';

CREATE OR REPLACE FUNCTION isvaliddir (dir_in IN VARCHAR2)
   RETURN BOOLEAN
/* Formatted by PL/Formatter v3.1.2.1 on 2000/08/30 09:25 */
-- SELECT priv on v$parameter is required!

IS
   v_value v$parameter.VALUE%TYPE;
BEGIN
   SELECT VALUE
     INTO v_value
     FROM v$parameter
    WHERE name = 'utl_file_dir'
      AND (   UPPER (VALUE) LIKE
                 UPPER (dir_in)
           OR VALUE = '*'); 
-- Any directory is OK!

   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.put_line (
         'Multiple directories fit criteria. Please narrow.'
      );
      RETURN TRUE;
END;
/

October's Tip of the Month

Indexing Nulls
Compliments of James Padfield (jpadfiel@cellops.com)

While null values are not part of a usual index, it is possible to index them using a function-based index, as follows...

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

CREATE INDEX index_name ON 
   table_name (DECODE (column_name, NULL, '#', NULL));

By using a hint we can encourage the use of our new index and return null values without the need for a full table scan.

SELECT /*+ INDEX(t,index_name) */ 
       column_name 
  FROM table_name t 
 WHERE DECODE (column_name, NULL, '#', NULL) = '#';

November's Tip of the Month

Invoker Rights Method Leads to Lots of Parsing

A new feature in Oracle8i, called Invoker Rights Method, allows you to ask the PL/SQL engine to execute a stored function or procedure under the authority of the "invoker" of the program. Prior to Oracle8i, stored code always executed under the authority of the "definer" or owner of the program. To define a program with invoker rights, you only need to add the AUTHID clause to the header of the program:

CREATE OR REPLACE PROCEDURE show_books 
  AUTHID CURRENT_USER 
AS
BEGIN
  FOR rec IN (SELECT * FROM book)  
  LOOP     
     DBMS_OUTPUT.PUT_LINE (rec.title);
  END LOOP;
END;

There is one big problem, however, with invoker rights: every time you run the program, any and all SQL within the program will be parsed -- again and again and again, even if the SQL statement doesn't change at all. This behavior makes sense, since the whole point of invoker rights is that all SQL references are resolved according to the rights of the invoker, and the invoker could change each time the program is run.

So if you decide to use AUTHID CURRENT_USER (and it is an absolute necessity when sharing programs that rely on dynamic SQL, for example), watch out for scenarios in which that program will be run with a very high frequency.

December's Tip of the Month

Encapsulate Dynamic SQL Parsing to Improve Error Detection and Clean Up.

This month's tip is drawn from the unfinished manuscript of Steven Feuerstein's latest book-in-progress: Oracle PL/SQL Best Practices. This book offers succinct, practical advice for improving the quality of your PL/SQL code. It will be published by O'Reilly and Associates in the first half of 2001. If you would like to do a technical review of some or all of this book, drop Steven a note at steven@stevenfeuerstein.com.

Dynamic SQL is tricky; you generally glom together different chunks of text to form what you hope is a valid SQL or PL/SQL statement. Either through programmer error or user error, you can end up with a bad chunk of SQL, resulting in a parse error. To identify and fix these errors, you should create your own parsing "engine" on top of DBMS_SQL.PARSE and the NDS (native dynamic SQL, an Oracle8i feature) statements. This program will trap and display error information, as well as clean up cursors.

Example

This technique is most crucial for DBMS_SQL. Don't ever call DBMS_SQL.PARSE directly in your program. Instead call your own parse. Here is a very simple example:

CREATE OR REPLACE FUNCTION open_and_parse ( 
   dynsql_in IN VARCHAR2, 
   dbms_mode_in IN INTEGER := NULL) 
RETURN INTEGER
IS
   dyncur INTEGER;
BEGIN
   dyncur := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE (dyncur, dynsql_in, 
      NVL (dbms_mode_in, DBMS_SQL.NATIVE));

   RETURN dyncur;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_SQL.CLOSE_CURSOR (dyncur);
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
      DBMS_OUTPUT.PUT_LINE (dynsql_in);
      RETURN NULL;
END;
/

CREATE OR REPLACE PROCEDURE exec_immed ( 
   dynsql_in IN VARCHAR2) 
   AUTHID CURRENT_USER
IS
BEGIN
   EXECUTE IMMEDIATE dynsql_in;
EXCEPTION
   WHEN OTHERS
   THEN
      pl (SQLERRM)
      pl (dynsql_in);
END;
/

• You will be able to identify and fix errors in your program, or train your users to use the interface to your dynamic SQL, much more effectively.
   
• You will not inadvertently leave DBMS_SQL cursors open (and impossible to close in your session) .

• With DBMS_SQL (prior to Oracle8i), any SQL statement passed to open_and_parse will be parsed under the privileges of the owner of open_and_parse. You should, therefore, install this program in every schema that wants to use it. Or, if you are running Oracle8i and still using DBMS_SQL, use the AUTHID CURRENT_USER clause to ensure that the program runs under the invoker's authority.
   
• With NDS, you cannot separate the parse and execute phases – it is all done by EXECUTE IMMEDIATE. That makes it hard to write a truly generic program to handle any SQL string (you have to account for the USING and INTO clauses). The general principle still applies: trap, handle and display dynamic SQL errors!

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