Oracle Tips and Tricks — David Fitzjarrell

May 18, 2015

Perception Is Everything

Filed under: General,pl/sql — dfitzjarrell @ 08:22

"There is much worth noticing that often escapes the eye."
- Norton Juster, The Phantom Tollbooth

Using BULK COLLECT in PL/SQL blocks and procedures can dramatically speed array processing, but it can, if the DBA isn’t prepared, ‘hide’ any errors that occur in the bulk processing list. A ‘plain vanilla’ EXCEPTION handler may not report all errors that are thrown. Let’s look at an example intentionally set up to fail inserts based on data from the EMP table. Table M is created with the EMP columns slightly re-ordered so the data types don’t match up to the source:


SQL> CREATE TABLE M
  2  	    (EMPNO NUMBER(4) NOT NULL,
  3  	     MGR NUMBER(4),
  4  	     JOB VARCHAR2(9),
  5  	     ENAME VARCHAR2(10),
  6  	     HIREDATE DATE,
  7  	     SAL NUMBER(7, 2),
  8  	     COMM NUMBER(7, 2),
  9  	     DEPTNO NUMBER(2));

Table created.

SQL>

The ENAME column is now fourth in the list, rather than second, If an attempt is made to simply ‘shove’ the EMP data into M it’s certain to generate a number of ‘invalid number’ errors, but if the PL/SQL loop and exception handler aren’t coded to take advantage of the BULK COLLECT error you’ll be trapping at most one of the many errors generated and that error alone will be reported:


SQL> DECLARE
  2  	type emp_tbl is table of emp%rowtype;
  3  	emp_data emp_tbl;
  4  	cursor EMPINFO is
  5  	select * from emp;
  6
  7
  8  BEGIN
  9  OPEN EMPINFO;
 10  LOOP
 11
 12  	FETCH EMPINFO BULK COLLECT INTO emp_data LIMIT 200;
 13  	EXIT WHEN emp_data.count = 0;
 14  	BEGIN
 15  	     DBMS_OUTPUT.PUT_LINE('Request rows ' || emp_data.COUNT);
 16  	     FORALL i IN 1..emp_data.COUNT
 17  		 INSERT INTO m VALUES emp_data(i);
 18
 19  	EXCEPTION
 20  	       WHEN others THEN -- Now we figure out what failed and why.
 21  		     -- Output desired error message
 22  		     dbms_output.put_line('-20999: Sumthin'' bad happened -- error stack follows');
 23  		     -- Output actual line number of error source
 24  		     dbms_output.put(dbms_utility.format_error_backtrace);
 25  		     -- Output the actual error number and message
 26  		     dbms_output.put_line(dbms_utility.format_error_stack);
 27  	END;
 28
 29  END LOOP;
 30  END;
 31  /
Request rows 18
-20999: Sumthin' bad happened -- error stack follows
ORA-06512: at line 16
ORA-01722: invalid number


PL/SQL procedure successfully completed.

SQL>

Taking another route the PL/SQL code is changed to provide a user-defined exception and variables to hold the error messages and numbers generated for each insert error. BULK COLLECT is also instrumented to save all of the exceptions generated. Running the modified code provides the following output:


SQL>
SQL> DECLARE
  2  	type emp_tbl is table of emp%rowtype;
  3  	emp_data emp_tbl;
  4  	cursor EMPINFO is
  5  	select * from emp;
  6  	errorCnt     number;
  7  	errString    varchar2(4000);
  8  	errCode      number;
  9  	dml_errors   exception;
 10  	pragma exception_init(dml_errors, -24381);
 11
 12
 13  BEGIN
 14  OPEN EMPINFO;
 15  LOOP
 16
 17  	FETCH EMPINFO BULK COLLECT INTO emp_data LIMIT 200;
 18  	EXIT WHEN emp_data.count = 0;
 19  	BEGIN
 20  	     DBMS_OUTPUT.PUT_LINE('Request rows ' || emp_data.COUNT);
 21  	     FORALL i IN 1..emp_data.COUNT SAVE EXCEPTIONS
 22  		 INSERT INTO m VALUES emp_data(i);
 23
 24  	EXCEPTION
 25  	       WHEN dml_errors THEN -- Now we figure out what failed and why.
 26  		     errorCnt := SQL%BULK_EXCEPTIONS.COUNT;
 27  		     errString := 'Number of statements that failed: ' || TO_CHAR(errorCnt);
 28  		     dbms_output.put_line(errString);
 29
 30  		     FOR i IN 1..errorCnt LOOP
 31  			 IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE > 0
 32  			 THEN
 33  			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 34  			     dbms_output.put_line(errString);
 35  			 ELSE
 36  			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 37  			     dbms_output.put_line(errString);
 38  			     RAISE;
 39  			 END IF;
 40  		     END LOOP;
 41  	END;
 42
 43  END LOOP;
 44  END;
 45  /
Request rows 18
Number of statements that failed: 18

Error #1
Error message is ORA-01722: invalid number

Error #2
Error message is ORA-01722: invalid number

Error #3
Error message is ORA-01722: invalid number

Error #4
Error message is ORA-01722: invalid number

Error #5
Error message is ORA-01722: invalid number

Error #6
Error message is ORA-01722: invalid number

Error #7
Error message is ORA-01722: invalid number

Error #8
Error message is ORA-01722: invalid number

Error #9
Error message is ORA-01722: invalid number

Error #10
Error message is ORA-01722: invalid number

Error #11
Error message is ORA-01722: invalid number

Error #12
Error message is ORA-01722: invalid number

Error #13
Error message is ORA-01722: invalid number

Error #14
Error message is ORA-01722: invalid number

Error #15
Error message is ORA-01722: invalid number

Error #16
Error message is ORA-01722: invalid number

Error #17
Error message is ORA-01722: invalid number

Error #18
Error message is ORA-01722: invalid number

PL/SQL procedure successfully completed.

SQL>

There are 18 rows in this slightly modified data set so 18 exceptions were generated. All of the exceptions were reported by the modified exception handler. Listing the changes made to the original code shows that three variables were declared (errorCnt, errString and errCode) and a user-defined exception was provided (dml_errors, initialized to error code -24381 (invalid number). The real ‘magic’ lies in the exception handler itself:


   	EXCEPTION
   	       WHEN dml_errors THEN -- Now we figure out what failed and why.
   		     errorCnt := SQL%BULK_EXCEPTIONS.COUNT;
   		     errString := 'Number of statements that failed: ' || TO_CHAR(errorCnt);
   		     dbms_output.put_line(errString);
   
   		     FOR i IN 1..errorCnt LOOP
   			 IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE > 0
   			 THEN
   			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
   			     dbms_output.put_line(errString);
   			 ELSE
   			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
   			     dbms_output.put_line(errString);
   			     RAISE;
   			 END IF;
   		     END LOOP;
   	END;

Each time the error is thrown in the loop the exception handler goes to work reporting the record number generating the error, the error number and the error text. Using SQL%BULK_EXCEPTIONS() array it’s possible to extract the error code and, by a call to SQLERRM, the associated error message. The handler can process both positive and negative error codes, and on negative error codes makes a call to RAISE to stop normal execution. Since no negative errors were thrown in this example the loop execution continued until all rows were processed, reporting every error encountered during the run.

Coding exception handlers correctly (to provide useful, rather than confusing, messages) is key in being able to let users report errors they encounter. Being able to see which records in a bulk collection are generating errors makes it even easier to diagnose and correct problems in bulk data processing, and knowing how to write such exception handlers provides that information to the end users. Using this template makes that task easier (hopefully) so the development team won’t be ‘shooting in the dark’ when a bulk loop process fails to process all of the desired records.

Using bulk processing when it’s appropriate can save time and effort; writing a proper exception handler can help tremendously in troubleshooting any errors that may arise.

It all depends upon how you look at things.

June 26, 2012

Nullified Remains

Filed under: pl/sql — dfitzjarrell @ 18:58
Tags: , ,

Just recently a service request was opened with Oracle Support regarding the “when others then null” exception handler when it was found in an Oracle-supplied form for the Oracle Inventory application from the E-Business suite. It appears the person who opened the SR believes (and rightly so) this is not the most informative of exception handlers nor is it proper coding practice; the request was opened in hopes of disallowing such constructs. I originally posted:

“Sadly Oracle Support may not do anything about this for two reasons:

1) It’s valid code.
2) It’s not causing another error to surface.

Amending my post to reflect the actual nature of the SR it’s now clear why this was opened — Oracle should never issue forms or production code using this exception ‘non-handler’ so it’s understandable why Oracle Support filed a bug report (bug number 14237626) for it. Why is coding “when others then null” not the best idea? Let’s look at some examples and see what could be hidden behind that glorious construct. Creating a table used in a previous post:

SQL> --
SQL> -- Create a test table
SQL> --
SQL> create table pktest(
2       pk number,
3       val1 number,
4       val2 number,
5       val3 number
6 );

Table created.

SQL>
SQL> --
SQL> -- Add the primary key
SQL> --
SQL> alter table pktest add constraint pktestpk primary key (pk);

Table altered.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> begin
2       for i in 1..25 loop
3             insert into pktest
4             values(i,i+1,i+2,i+3);
5       end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Commit the data
SQL> --
SQL> commit;

Commit complete.

SQL>

Using this table/key combination let’s try some transactions that will generate errors; we’ll hide them by using “when others then null”:

SQL> --
SQL> -- Try and insert an existing key
SQL> -- value into the test table
SQL> --
SQL> -- Intentionally obscure the original
SQL> -- error
SQL> --
SQL> begin
2       insert into pktest values (1,2, 3, 4);
3 exception
4       when others then
5             null;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>

An insert was intentionally attempted that violates the primary key constraint yet no error was thrown — how lovely. The worst part of this is the code is declared to have run successfully; it appears that the insert was executed without error (which we know is NOT true) so the end user who ran this glorious piece of code has no idea his or her insert failed. These, of course, are not the only errors that can be hidden from view as other, more insidious errors can be ignored:

SQL>
SQL> --
SQL> -- Artificially generate some rather severe
SQL> -- errors
SQL> --
SQL> -- Ignore them in the exception handler
SQL> --
SQL> declare
  2          bad             exception;
  3          reallybad       exception;
  4          trulyreallybad  exception;
  5
  6          pragma exception_init(bad, -43); -- remote operation failed
  7          pragma exception_init(reallybad, -1019); -- unable to allocate memory on user side
  8          pragma exception_init(trulyreallybad, -1034); -- Oracle not available
  9  begin
 10          begin
 11                  raise bad;
 12          exception
 13                  when others then null;
 14          end;
 15          begin
 16                  raise reallybad;
 17          exception
 18                  when others then null;
 19          end;
 20          begin
 21                  raise trulyreallybad;
 22          exception
 23                  when others then null;
 24          end;
 25  end;
 26  /

PL/SQL procedure successfully completed.

SQL>

Notice that even these errors were not reported, including the ‘Oracle not available’ error indicating connection to the database failed resulting in nothing being executed. Oracle reported that the PL/SQL ran successfully (meaning without error) all because of the ‘when others then null’ error mis-handler.

All of this transpires due to a lack of knowledge of what errors could be expected, a bout of laziness on the part of the developer or both — “Gee, I don’t know what other errors to expect but they can’t be serious so I’ll ignore them.” Any error is serious to the end user as it prevents work from completing, and if those errors are ignored and the code block execution looks successful then, when problems arise because of missing data, the issue is harder to troubleshoot. Absent an error message, no matter how trivial that error may seem to the developer, the end user has no indication that the insert/update/delete failed and has nothing to report to the help desk. Codng such mis-handlers also is a disservice to the developer/programmer as they won’t know what caused the problem any more than the user who ran the code. Not knowing the cause makes the solution that much more difficult to find. In that case everybody loses.

This is the third post on error handling and error reporting I’ve written, two on properly reporting error text so the end user can talk intelligently to the help desk on what went wrong, and this one, on making sure there IS an error message to report. All three should be read as a set (in my opinion) so that error messages are reported and the text that the end user sees provides useful information to the service desk personnel. This falls, really, on the developers and programmers as the error handlers they code directly affect what the end users see when errors arise, and the error messages should be clear enough for a non-technical Oracle user to describe to the help desk representatives with whom they speak. A little effort on the development side goes a long way in making troubleshooting easier if, or when, the time comes.

Third time is a charm.

June 15, 2012

Compound Interest

Filed under: pl/sql — dfitzjarrell @ 17:34
Tags: ,

Oracle 11g offers a new twist on triggers, the compound trigger, a trigger than can act both before and after an update, insert or delete has occurred. This makes possible the abilty in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is in order.

Compound triggers can have up to four sections:

the BEFORE section
the BEFORE EACH ROW section
the AFTER EACH ROW section
the AFTER section

At least two of the sections must be included (including only one of the four would result in traditional trigger) and it does not matter which two of the sections are used. For example such a trigger can include a BEFORE EACH ROW section and an AFTER section; the two sections need not be ‘matched’ (BEFORE, BEFORE EACH ROW, for instance). Also the COMPOUND TRIGGER STATEMENT must be included so Oracle will recognize the above four constructs and treat them accordingly. The general syntax is:


create or replace trigger <trigger name>
for <insert|update|delete> <of column_name> on <tablename>
      COMPOUND TRIGGER
      <declare section>
      BEFORE
      <before section>
      BEFORE EACH ROW
      <before each row section>
      AFTER EACH ROW
      <after each row section>
      AFTER
      <after section>
END;
/

Since compound triggers are relatively new and many may not have had the opportunity to write or use them I have provided a working example. Setting the stage for this trigger HR has set a restriction on the size of a raise to be given; based on the department the raise cannot exceed 12 percent of the department average salary. A compound trigger can be used to process the raise amounts assigned. Such a compound trigger is shown below, along with several ways of executing the raises:


SQL> create or replace trigger check_raise_on_avg
  2  for update of sal on emp
  3  COMPOUND TRIGGER
  4    Twelve_Percent        constant number:=0.12;
  5
  6    -- Declare collection type and variable:
  7
  8    TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
  9                                  INDEX BY VARCHAR2(80);
 10    Department_Avg_Salaries     Department_Salaries_t;
 11    TYPE Sal_t             IS TABLE OF Emp.Sal%TYPE;
 12    Avg_Salaries                Sal_t;
 13    TYPE Deptno_t       IS TABLE OF Emp.Deptno%TYPE;
 14    Department_IDs              Deptno_t;
 15
 16    BEFORE STATEMENT IS
 17    BEGIN
 18      SELECT               AVG(e.Sal), NVL(e.Deptno, -1)
 19        BULK COLLECT INTO  Avg_Salaries, Department_IDs
 20        FROM               Emp e
 21        GROUP BY           e.Deptno;
 22      FOR j IN 1..Department_IDs.COUNT() LOOP
 23        Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
 24      END LOOP;
 25    END BEFORE STATEMENT;
 26
 27    AFTER EACH ROW IS
 28    BEGIN
 29      IF :NEW.Sal - :Old.Sal >
 30        Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno)
 31      THEN
 32        Raise_Application_Error(-20000, 'Raise too large');
 33      END IF;
 34    END AFTER EACH ROW;
 35  END Check_Raise_On_Avg;
 36  /

Trigger created.

SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> update emp set sal=sal*1.10 where empno = 7369;

1 row updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        880
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> update emp set sal=sal*1.08 where deptno = 20;

5 rows updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        864
      7499       1600
      7521       1250
      7566       3213
      7654       1250
      7698       2850
      7782       2450
      7788       3240
      7839       5000
      7844       1500
      7876       1188
      7900        950
      7902       3240
      7934       1300

14 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>

Does the trigger reject raises? It certainly does:


SQL> update emp set sal=sal*1.10 where deptno = 30;
update emp set sal=sal*1.10 where deptno = 30
       *
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL> update emp set sal=sal*1.10 where empno = 7698;
update emp set sal=sal*1.10 where empno = 7698
       *
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>

The first rejected update unfortunately disallowed all of the raises based on the failure of a few; the second update shows one employee where a 10 percent raise would be greater than 12 percent of the departmental average salary. Of course it is usually rare to see such a large raise given throughout an entire department so such occurrences would be few as raises are usually processed (outside of cost-of-living adjustments) on an individual basis.

Please note that doing the above in a traditional trigger would have resulted in a mutating table error since the table being updated cannot be queried during the update; all successful raises were processed and no such error was thrown.

Compound triggers are a nice addition to an already robust database system; they may not be commonplace but having them available certainly makes application development simpler as business rules that may be unenforceable using a regular trigger can be successfully implemented. They may be considered as ‘specialty tools’ in the database realm but remember that plumbers, builders and mechanics also have tools they only use once in a while and when the situation arises where a compound trigger can be useful it’s good to have them around.

So when do I get my raise?

February 27, 2012

Collecting Thoughts

Filed under: pl/sql — dfitzjarrell @ 20:21
Tags: , ,

Collections are an interesting lot. They can be one of the most useful tools in the Oracle arsenal, yet they can also be very frustrating to implement. For those unfamiliar with them a collection/varray is defined as “an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.” The definition seems simple enough but it may be deceptively so. To make matters even more confusing to use a collection you must create a database type for it to reference; a varray requires a type as well but that type can be declared in the PL/SQL block. To clear the air a bit let’s go through some examples of defining and using collections and varrays: The first example uses a collection to store vendor ids and then process them for a report. The code builds two ‘tables’ and compares the contents of them by loading collections and comparing one collection to the other; output is displayed for the conditions of the two tables being equal and the two tables not being equal:


SQL> 
SQL> set serveroutput on size 1000000
SQL> 
SQL> CREATE OR REPLACE type integer_table is table of integer;
  2  /

Type created.

SQL> 
SQL> DECLARE
  2  
  3  
  4   vendor_key_table   integer_table;
  5   vendor_key_table2   integer_table;
  6   CURSOR tst
  7   IS
  8      SELECT   purch_order, SUM (dollars),
  9        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 10          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 11           FROM DUAL
 12         UNION ALL
 13         SELECT 1 purch_order, 8 dollars, 123452 vendor_key
 14           FROM DUAL
 15         UNION ALL
 16         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 17           FROM DUAL
 18         UNION ALL
 19         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 20           FROM DUAL
 21         UNION ALL
 22         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 23           FROM DUAL)
 24      GROUP BY purch_order;
 25  
 26  
 27    CURSOR tst2
 28   IS
 29    SELECT purch_order, SUM (dollars),
 30        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 31          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 32           FROM DUAL
 33         UNION ALL
 34         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 35           FROM DUAL
 36         UNION ALL
 37         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 38           FROM DUAL
 39         UNION ALL
 40         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 41           FROM DUAL)
 42      GROUP BY purch_order;
 43   v_purch_order    NUMBER;
 44   v_dollars    NUMBER;
 45  
 46  
 47   mystr     VARCHAR2 (4000);
 48  
 49  
 50   v_purch_order2     NUMBER;
 51   v_dollars2     NUMBER;
 52  
 53  
 54   mystr2      VARCHAR2 (4000);
 55  BEGIN
 56   OPEN tst;
 57   open tst2;
 58  
 59  
 60   LOOP
 61      mystr := NULL;
 62      mystr2 := NULL;
 63  
 64  
 65      FETCH tst
 66       INTO v_purch_order, v_dollars, vendor_key_table;
 67  
 68  
 69      FETCH tst2
 70       INTO v_purch_order2, v_dollars2, vendor_key_table2;
 71  
 72  
 73      IF tst%NOTFOUND
 74      THEN
 75         EXIT;
 76      END IF;
 77  
 78  
 79      if vendor_key_table = vendor_key_table2 then
 80          dbms_output.put_line('equal');
 81      else
 82          dbms_output.put_line(' not equal');
 83      end if;
 84  
 85  
 86      -- loop through the collection and build a string so that
 87      -- we can display it and prove that it works
 88      FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
 89       FROM TABLE (vendor_key_table))
 90      LOOP
 91         mystr := mystr || ',' || cur1.vendor_key;
 92         -- /* based on the value of the sum, you can do something with each detail*/
 93         -- if v_dollars > 12 then
 94         --   UPDATE VENDOR SET paid_status = 'P' where vendor_key = cur1.vendor_key;
 95         -- end if;
 96      END LOOP;
 97  
 98  
 99      DBMS_OUTPUT.put_line (   'Purchase Order-> '
100       || TO_CHAR (v_purch_order)
101       || ' dollar total-> '
102       || TO_CHAR (v_dollars)
103       || ' vendorkey list-> '
104       || SUBSTR (mystr, 2));
105  
106  
107      -- loop throught the collection and build a string so that
108      -- we can display it and prove that it works
109      FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
110       FROM TABLE (vendor_key_table2))
111      LOOP
112         mystr2 := mystr2 || ',' || cur2.vendor_key;
113      END LOOP;
114  
115  
116      DBMS_OUTPUT.put_line (   'Purchase Order-> '
117       || TO_CHAR (v_purch_order2)
118       || ' dollar total-> '
119       || TO_CHAR (v_dollars2)
120       || ' vendorkey list-> '
121       || SUBSTR (mystr2, 2));
122  
123  
124   END LOOP;
125   CLOSE tst;
126   CLOSE tst2;
127  END;
128  /
not equal
Purchase Order-> 1   dollar total-> 11   vendorkey list-> 435235,123452
Purchase Order-> 1   dollar total-> 3   vendorkey list-> 435235
equal
Purchase Order-> 2   dollar total-> 16   vendorkey list-> 433738,387118,383738
Purchase Order-> 2   dollar total-> 16   vendorkey list-> 383738,387118,433738

PL/SQL procedure successfully completed.

SQL> 

Comparing the collections rather than looping through each table makes the work much easier to complete. Notice we only needed one type created; the same type satisfied the conditions for both collection tables.

The next example shows how things can go astray with the bulk collect operation when the limit does not evenly divide the result set. In the first part of the example we use the well-known ‘exit when cursor%notfound;’ directive with less than stellar results (we miss inserting 5 records into the second table); the second part of the example shows how to properly implement an exit from a bulk collect operation; this one uses a varray:

 
SQL> 
SQL> set echo on linesize 150 trimspool on
SQL> 
SQL> create table emp_test as select * From emp where 0=1;

Table created.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when get_emp_data%notfound;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000

9 rows selected.

SQL> 
SQL> truncate table emp_test;

Table truncated.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when emp_c.count = 0;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000
      7844 TURNER                                          1500
      7876 ADAMS                                           1100

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES                                            950
      7902 FORD                                            3000
      7934 MILLER                                          1300

14 rows selected.

SQL> 

What happened in the first part? Since the limit was more than the number of remaining records the NOTFOUND indicator was set at the end of the fetch. We had 5 records left to process in the varray but the ‘exit when cursor%notfound;’ statement terminated the loop BEFORE we could get the remaining 5 records inserted into our table, thus they were lost. Using the ‘exit when collection.count = 0;’ construct prevents us from missing records since the count was greater than 0 even when the NOTFOUND indicator was set. This allows us to process the remaining records in the varray before exiting the loop. [Yes, the exit could be coded at the end of the loop rather than the beginning and the ‘exit when cursor%NOTFOUND;’ would process the remaining records but that, to me, defeats the purpose of the conditional exit. As I see it we want to exit the loop when no more work is to be done, not look for partial sets of data to apply then exit before the next (unsuccessful) fetch.]

Our next example does two things: loads data using bulk collect into a varray then uses the FORALL loop construct to quickly process the collection and insert the data into a staging table (I believe this originally appeared in ‘Morgan’s Library’ on the psoug.org website). The second part is a bit contrived as it uses a collection to process deletes from a table — deletes that could have easily been executed with a single SQL statement — but it does show the power of using collections and varrays:


SQL> 
SQL> set echo on timing on
SQL> 
SQL> create table temp_stg(
  2        usrname varchar2(30),
  3        usrjob varchar2(20),
  4        usrsal number
  5  );

Table created.

SQL> 
SQL> begin
  2        for i in 1..1000000 loop
  3        insert into temp_stg
  4        values ('Blorp'||i, 'Job'||i, 1200*(mod(i,3)));
  5        end loop;
  6  
  7        commit;
  8  
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> create table temp_ld(
  2        usrname varchar2(30),
  3        usrjob varchar2(20),
  4        usrsal number
  5  );

Table created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE bulk_load IS
  2  
  3  TYPE dfarray IS TABLE OF temp_stg%ROWTYPE;
  4  ld_dat dfarray;
  5  
  6  CURSOR stg IS
  7  SELECT *
  8  FROM temp_stg;
  9  
 10  BEGIN
 11   OPEN stg;
 12   LOOP
 13     FETCH stg BULK COLLECT INTO ld_dat LIMIT 1000;
 14  
 15     FORALL i IN 1..ld_dat.COUNT
 16        INSERT INTO temp_ld VALUES ld_dat(i);
 17  
 18     EXIT WHEN ld_dat.count=0;
 19    END LOOP;
 20    COMMIT;
 21    CLOSE r;
 22  END bulk_load;
 23  /

Procedure created.

SQL> 
SQL> exec bulk_load;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from temp_stg where usrname like '%99999%';

USRNAME                        USRJOB                   USRSAL
------------------------------ -------------------- ----------
Blorp99999                     Job99999                      0
Blorp199999                    Job199999                  1200
Blorp299999                    Job299999                  2400
Blorp399999                    Job399999                     0
Blorp499999                    Job499999                  1200
Blorp599999                    Job599999                  2400
Blorp699999                    Job699999                     0
Blorp799999                    Job799999                  1200
Blorp899999                    Job899999                  2400
Blorp999990                    Job999990                     0
Blorp999991                    Job999991                  1200

USRNAME                        USRJOB                   USRSAL
------------------------------ -------------------- ----------
Blorp999992                    Job999992                  2400
Blorp999993                    Job999993                     0
Blorp999994                    Job999994                  1200
Blorp999995                    Job999995                  2400
Blorp999996                    Job999996                     0
Blorp999997                    Job999997                  1200
Blorp999998                    Job999998                  2400
Blorp999999                    Job999999                     0

19 rows selected.

SQL> 

Trust me that the data loads took very little time to process. Here is the contrived part, but it is still a good example of the power of using collections:


SQL> CREATE OR REPLACE PROCEDURE data_del IS
  2  
  3  TYPE dfarray IS TABLE OF temp_stg.usrname%TYPE;
  4  ld_dat dfarray;
  5  
  6  CURSOR stg IS
  7  SELECT usrname
  8  FROM temp_stg
  9  where usrname like '%9999%';
 10  
 11  BEGIN
 12   OPEN stg;
 13   LOOP
 14     FETCH stg BULK COLLECT INTO ld_dat LIMIT 1000;
 15  
 16     FORALL i IN 1..ld_dat.COUNT
 17        delete from temp_ld where usrname = ld_dat(i);
 18  
 19     EXIT WHEN ld_dat.count=0;
 20    END LOOP;
 21    COMMIT;
 22    CLOSE stg;
 23  END data_del;
 24  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> exec data_del;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from temp_ld where usrname like '%99999%';

no rows selected

SQL> 

Collections can be a real timesaver for bulk processing of data; they may not be applicable in every situation but when the conditions are right they can make your job so much easier. Master collections and varrays and you may be able to amaze your friends.

Collections, anyone?

September 26, 2008

How Dynamic

Filed under: pl/sql — dfitzjarrell @ 18:58

Passing a list of values to a function or procedure should be, well, simple, and it is, really, unless you have a dynamic list of unknown length. Simply trying to use the supplied string, as-is, can be disappointing:

SQL> --
SQL> -- Let's try this the simple way
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2)
  2  return number
  3  is
  4        lv_ct number;
  5  
  6        cursor get_empinfo is
  7        select count(*)
  8        from emp
  9        where deptno in subtype_list;
 10  
 11  begin
 12  
 13    open get_empinfo;
 14    fetch get_empinfo into lv_ct;
 15    close get_empinfo;
 16  
 17    return lv_ct;
 18  end;
 19  /

Function created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- The function created without error
SQL> --
SQL> -- Let's see if it works
SQL> --
SQL> select instring_list_test('10,20,30') from dual;
select instring_list_test('10,20,30') from dual
       *
ERROR at line 1:
ORA-01722: invalid number 
ORA-06512: at "BING.INSTRING_LIST_TEST", line 14 


SQL> 
SQL> --
SQL> -- That's silly, it should work ...
SQL> --

Since ‘10,20,30’ isn’t a number, and Oracle can’t magically separate the individual values the function call fails. It would succeed were there one value in this dynamic list, but real-life situations usually aren’t that simple and straightforward. Whatever shall we do? We need to ‘get dirty’ and actually code a way for Oracle to separate the values, make them numbers and populate a table, dynamically, so we can select from that table and generate a usable list. So, let’s try this again and see if we can get this to do what we want:

SQL> --
SQL> -- Let's try this again
SQL> --
SQL> -- We'll create a table type first
SQL> --
SQL> create or replace type InNumTab is table of number;
  2  /

Type created.

SQL> 
SQL> --
SQL> -- Now we'll use that table type to massage
SQL> -- the supplied string into a usable list
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2)
  2  return number
  3  is
  4        --
  5        -- The parsed value
  6        --
  7        lv_subtyp number;
  8        --
  9        -- The table we'll populate
 10        --
 11        lv_sublist InNumTab := InNumTab();
 12        --
 13        -- A place for the result
 14        --
 15        lv_ct number;
 16        --
 17        -- A variable so we can 'walk' the string
 18        --
 19        startpos number:=1;
 20        --
 21        -- Record counter to extend the table
 22        --
 23        rec     number:=1;
 24  
 25        --
 26        -- Query using the dynamic IN list
 27        --
 28        cursor get_empinfo (enums InNumTab) is
 29        select count(*)
 30        from emp
 31        where deptno in (select column_value from table(cast(enums as InNumTab)));
 32  
 33  begin
 34    --
 35    -- Extend the table so we can start populating it
 36    --
 37    lv_sublist.extend(rec);
 38  
 39    --
 40    -- 'Walk' the provided string
 41    -- The loop exits when no value separator is found
 42    -- We expect the value separator to be a comma
 43    --
 44    loop
 45        exit when instr(subtype_list, ',', startpos) = 0;
 46        lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,',', 1)-1);
 47        lv_sublist(rec) := lv_subtyp;
 48        startpos := instr(subtype_list, ',', startpos)+1;
 49        rec := rec+1;
 50        --
 51        -- After each addition we extend the table
 52        --
 53        lv_sublist.extend(rec);
 54    end loop;
 55  
 56    --
 57    -- We extend the table one more time to hold our last value
 58    --
 59    rec := rec+1;
 60    lv_sublist.extend(rec);
 61    lv_subtyp := substr(subtype_list, startpos);
 62    lv_sublist(rec) := lv_subtyp;
 63  
 64    --
 65    -- Get the count
 66    --
 67    open get_empinfo(lv_sublist);
 68    fetch get_empinfo into lv_ct;
 69    close get_empinfo;
 70  
 71    --
 72    -- Return the value to the caller
 73    --
 74    return lv_ct;
 75  end;
 76  /

Function created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30') from dual;

INSTRING_LIST_TEST('10,20,30')
------------------------------
                            27

SQL> select instring_list_test('10,20') from dual;

INSTRING_LIST_TEST('10,20')
---------------------------
                         12

SQL> select instring_list_test('10') from dual;

INSTRING_LIST_TEST('10')
------------------------
                       3

SQL> select instring_list_test('10,20,30,40') from dual;

INSTRING_LIST_TEST('10,20,30,40')
---------------------------------
                               42

SQL> select instring_list_test('10,20,30,40,50,60') from dual;

INSTRING_LIST_TEST('10,20,30,40,50,60')
---------------------------------------
                                     60

SQL> select instring_list_test('10,20,30,40,50') from dual;

INSTRING_LIST_TEST('10,20,30,40,50')
------------------------------------
                                  54

SQL> 

Notice that the length of the list is immaterial as the loop keeps running until the desired record separator is no longer present; we need to code one additional parse of the supplied string to extract the last value and place it in our dynamic table. We then use the CAST and TABLE functions when we query this ‘table’; the result is a list of values, rather than the original string, which makes IN very happy indeed.

So what if you don’t want to always use a comma to separate your data values? Don’t panic, we can re-write the function to accept a second parameter, the record separator:

SQL> --
SQL> -- We'll try this another way, passing in the desired record separator value
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2)
  2  return number
  3  is
  4        --
  5        -- The parsed value
  6        --
  7        lv_subtyp number;
  8        --
  9        -- The table we'll populate
 10        --
 11        lv_sublist InNumTab := InNumTab();
 12        --
 13        -- A place for the result
 14        --
 15        lv_ct number;
 16        --
 17        -- A variable so we can 'walk' the string
 18        --
 19        startpos number:=1;
 20        --
 21        -- Record counter to extend the table
 22        --
 23        rec     number:=1;
 24
 25        --
 26        -- Query using the dynamic IN list
 27        --
 28        cursor get_empinfo (enums InNumTab) is
 29        select count(*)
 30        from emp
 31        where deptno in (select column_value from table(cast(enums as InNumTab)));
 32
 33  begin
 34    --
 35    -- Extend the table so we can start populating it
 36    --
 37    lv_sublist.extend(rec);
 38
 39    --
 40    -- 'Walk' the provided string
 41    --
 42    -- We provide the desired record separator
 43    --
 44    loop
 45        exit when instr(subtype_list, recsep, startpos) = 0;
 46        lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1);
 47        lv_sublist(rec) := lv_subtyp;
 48        startpos := instr(subtype_list, recsep, startpos)+1;
 49        rec := rec+1;
 50        --
 51        -- After each addition we extend the table
 52        --
 53        lv_sublist.extend(rec);
 54    end loop;
 55
 56    --
 57    -- We extend the table one more time to hold our last value
 58    --
 59    rec := rec+1;
 60    lv_sublist.extend(rec);
 61    lv_subtyp := substr(subtype_list, startpos);
 62    lv_sublist(rec) := lv_subtyp;
 63
 64    --
 65    -- Get the count
 66    --
 67    open get_empinfo(lv_sublist);
 68    fetch get_empinfo into lv_ct;
 69    close get_empinfo;
 70
 71    --
 72    -- Return the value to the caller
 73    --
 74    return lv_ct;
 75  end;
 76  /

Function created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30',',') from dual;

INSTRING_LIST_TEST('10,20,30',',')
----------------------------------
                                27

SQL> select instring_list_test('10;20',';') from dual;

INSTRING_LIST_TEST('10;20',';')
-------------------------------
                             12

SQL> select instring_list_test('10',',') from dual;

INSTRING_LIST_TEST('10',',')
----------------------------
                           3

SQL> select instring_list_test('10:20:30:40',':') from dual;

INSTRING_LIST_TEST('10:20:30:40',':')
-------------------------------------
                                   42

SQL> select instring_list_test('10-20-30-40-50-60','-') from dual;

INSTRING_LIST_TEST('10-20-30-40-50-60','-')
-------------------------------------------
                                         60

SQL> select instring_list_test('10,20,30,40,50',',') from dual;

INSTRING_LIST_TEST('10,20,30,40,50',',')
----------------------------------------
                                      54

SQL>

And it works like a charm, returning correct values for the counts requested. Is that cool, or what?

In Oracle 10g and later releases the MEMBER OF operator can be used in place of the table function used in the prior two examples:

SQL>
SQL> --
SQL> -- We'll try this yet another way
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2)
  2  return number
  3  is
  4          --
  5          -- The parsed value
  6          --
  7          lv_subtyp number;
  8          --
  9          -- The table we'll populate
 10          --
 11          lv_sublist InNumTab := InNumTab();
 12          --
 13          -- A place for the result
 14          --
 15          lv_ct number;
 16          --
 17          -- A variable so we can 'walk' the string
 18          --
 19          startpos number:=1;
 20          --
 21          -- Record counter to extend the table
 22          --
 23          rec     number:=1;
 24
 25          --
 26          -- Query using the dynamic IN list
 27          --
 28          cursor get_empinfo (enums InNumTab) is
 29          select count(*)
 30          from emp
 31          where deptno member of enums;
 32
 33  begin
 34      --
 35      -- Extend the table so we can start populating it
 36      --
 37      lv_sublist.extend(rec);
 38
 39      --
 40      -- 'Walk' the provided string
 41      --
 42      -- We expect the value separator to be a comma
 43      --
 44      loop
 45          exit when instr(subtype_list, recsep, startpos) = 0;
 46          lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1);
 47          lv_sublist(rec) := lv_subtyp;
 48          startpos := instr(subtype_list, recsep, startpos)+1;
 49          rec := rec+1;
 50          --
 51          -- After each addition we extend the table
 52          --
 53          lv_sublist.extend(rec);
 54      end loop;
 55
 56      --
 57      -- We extend the table one more time to hold our last value
 58      --
 59      rec := rec+1;
 60      lv_sublist.extend(rec);
 61      lv_subtyp := substr(subtype_list, startpos);
 62      lv_sublist(rec) := lv_subtyp;
 63
 64      --
 65      -- Get the count
 66      --
 67      open get_empinfo(lv_sublist);
 68      fetch get_empinfo into lv_ct;
 69      close get_empinfo;
 70
 71      --
 72      -- Return the value to the caller
 73      --
 74      return lv_ct;
 75  end;
 76  /

Function created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30',',') from dual;

INSTRING_LIST_TEST('10,20,30',',')
----------------------------------
                                27

SQL> select instring_list_test('10;20',';') from dual;

INSTRING_LIST_TEST('10;20',';')
-------------------------------
                             12

SQL> select instring_list_test('10',',') from dual;

INSTRING_LIST_TEST('10',',')
----------------------------
                           3

SQL> select instring_list_test('10:20:30:40',':') from dual;

INSTRING_LIST_TEST('10:20:30:40',':')
-------------------------------------
                                   42

SQL> select instring_list_test('10-20-30-40-50-60','-') from dual;

INSTRING_LIST_TEST('10-20-30-40-50-60','-')
-------------------------------------------
                                         60

SQL> select instring_list_test('10,20,30,40,50',',') from dual;

INSTRING_LIST_TEST('10,20,30,40,50',',')
----------------------------------------
                                      54

SQL>

The problem, though simple to state, isn’t quite as simple to solve, as proven above. That’s because Oracle is a database, it isn’t your brain, so it can’t draw on prior experience and know that ‘10,20,30’ is glorified shorthand for 10,20,30. And because it’s software (really, it is) running on a computer, it does what you tell it to do, whether or not those instructions provide the output you’d intended. Since it can’t think like you do you’ll have to start ‘thinking’ like it does, and that may throw a wrench into your logical picture of the situation. Knowing what to do with that wrench is half of the battle.

Passing dynamic lists to procedures and functions is possible, it just isn’t as easy as you’d like to think, especially the first time through. But, knowing how to navigate such a situation makes life a bit easier in the IT realm, which may make you pretty nifty.

Of course such knowledge is no substitute for the ultimate cool of driving a Lamborghini to the dollar store …

September 16, 2008

Execute This!

Filed under: General,pl/sql — dfitzjarrell @ 17:54

A while back I wrote on the causes and solutions for apparently missing tables and views, but I didn’t address the problem of ‘missing’ packages/procedures/functions. Silly me. Let’s correct that.

Oracle newsgroups and blogs are filled with suggestions, tips, techniques and scripts intended to help the DBA with his or her chores, and many of these offerings utilize ‘standard’ packages and procedures installed by Oracle at database creation. Of course some of these packages/procedures/functions aren’t meant for the common, every-day user to utilize, and the privileges on those objects are limited to specific types of accounts. Some of these are even restricted to use by SYS as SYSDBA and no one else. There are many, though, that are suitable for any user to execute, provided that user has the requisite privileges. And, unfortunately, such privileges may not have been granted to the user desiring access; calling or attempting to describe such procedures/packages/functions then results in the following undesired output:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

And, from a PL/SQL block you can get the following unnerving message:

PLS-00201: identifier 'dbms_lock' must be declared

But, hey, you KNOW it’s there, because all of these wonderful scripts can’t be wrong. And they’re not; the user account in use simply has not been granted execute privilege on that package. And the same rules apply here that I listed in my prior post:

* the user has no execute privilege on the package/procedure/function
* a synonym is missing and the user is attempting to access the object by name

How to fix this glaring omission? Either grant execute on the desired object to the requesting user, or create a synonym to allow access by name. How can you tell which is required? If this:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

and this:

SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist


SQL>

are the end results then the user has no execute privilege on the package/procedure/function. If, however, access by name fails:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

but access by owner.name succeeds:

SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKNAME                       VARCHAR2                IN
 LOCKHANDLE                     VARCHAR2                OUT
 EXPIRATION_SECS                NUMBER(38)              IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
PROCEDURE SLEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

SQL>

then the issue is a missing synonym. Knowing the corrective action required (and, of course, taking that action) will allow the user to access the desired package/procedure/function.

Knowing what packages/procedures/functions you CAN access is information which is fairly easy to obtain:

select owner, object_name
from all_objects
where object_type in ('PACKAGE','FUNCTION','PROCEDURE');

You’ll get a list (possibly a LONG list) of packages, procedures and functions (and the assiciated owners) which you’re allowed to execute:

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            STANDARD
SYS                            DBMS_STANDARD
SYS                            DBMS_REGISTRY
SYS                            DBMS_REGISTRY_SERVER
SYS                            XML_SCHEMA_NAME_PRESENT
SYS                            UTL_RAW
SYS                            PLITBLM
SYS                            SYS_STUB_FOR_PURITY_ANALYSIS
SYS                            PIDL
SYS                            DIANA
SYS                            DIUTIL


OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            SUBPTXT2
SYS                            SUBPTXT
SYS                            DBMS_PICKLER
SYS                            DBMS_JAVA_TEST
SYS                            DBMS_SPACE_ADMIN
SYS                            DBMS_LOB
SYS                            UTL_SYS_COMPRESS
SYS                            UTL_TCP
SYS                            UTL_HTTP
SYS                            DBMS_TRANSACTION_INTERNAL_SYS
SYS                            DBMS_SQL


OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            DBMS_SYS_SQL
SYS                            DBMS_OUTPUT
SYS                            DBMS_LOGSTDBY
SYS                            DBMS_SESSION
SYS                            DBMS_LOCK
SYS                            UTL_FILE
SYS                            DBMS_TYPES
SYS                            GETTVOID
SYS                            XMLSEQUENCEFROMXMLTYPE
SYS                            XQSEQUENCEFROMXMLTYPE
SYS                            XMLSEQUENCEFROMREFCURSOR
...

If the package/procedure/function is in that list, but you still can’t access it by name you’re simply missing a synonym. And, if it’s not in that list you have no access to that object so you’ll need to discuss that issue with your DBA.

[ A special case can exist through PL/SQL where a user has access to a table/view/package/procedure/function by virtue of a role but PL/SQL can’t ‘see’ it. Many packages/procedures/functions are compiled with AUTHID DEFINER (the default) and, as such, won’t use privileges granted through a role. Modifying the procedure to be AUTHID CURRENT_USER can fix that problem and allow PL/SQL to traverse the entire privilege tree. If the procedure cannot be modified (such as Oracle-supplied packages, procedures and functions) the only way to ‘fix’ that is to have privileges directly on the object in question. If you find yourself in this boat talk with your DBA to see if he/she can accomodate you.]

I’ll state again in this post that not all Oracle users are destined to access or use all of the installed packages/procedures/functions Oracle supplies. There may be very good reasons in your organization for not having access to a specific package, procedure or function, so don’t be surprised if your request is met with a glorious

“Nope, sorry, can’t do that.”

Security is the watchword of late, and some organizations may frown upon just any user having privilege to execute certain code, because granting such access may open security holes in the database. Pete Finnigan has an excellent website listing the security issues with Oracle releases; it’s worth the time to peruse his site to get a feel for what could disrupt an Oracle installation and give you a ‘heads up’ on why, possibly, you can’t use a certain package or procedure.

It never hurts to ask. Just don’t be surprised if the answer is “No” because there is probably a very good reason for that response.

September 3, 2008

To Err Is Human

Filed under: General,pl/sql — dfitzjarrell @ 14:23

Someone asked

“how oracle is keeping track of last few ora- errors”

and the answer is a fairly simple “it isn’t”. (Granted, Oracle does record the severe errors in the database alert log, but those aren’t all of the errors that can be generated by any stretch of the imagination.) It can, though, if you ask it to.

Since Oracle 8.1.6 system event triggers have been available, and one of those triggers is an AFTER SERVERERROR trigger, which can log most errors you encounter. Setting up your database to do that is a fairly simple task (the following works in 10.2 and later releases):

SQL> CREATE TABLE stats$error_log (
  2          err_dt          TIMESTAMP,
  3          db_user         VARCHAR2(30),
  4          msg_stack       VARCHAR2(2000),
  5          sqltxt          VARCHAR2(1000))
  6  tablespace tools;

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER log_server_errors
  2    AFTER SERVERERROR
  3    ON DATABASE
  4  DECLARE
  5            v_sqltext VARCHAR2(1000);
  6            nl_sqltext ora_name_list_t;
  7    BEGIN
  8            -- Capture entire error text
  9            FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
 10              v_sqltext := v_sqltext || nl_sqltext(i);
 11            END LOOP;
 12
 13            INSERT INTO STATS$ERROR_LOG
 14            (err_dt, db_user, msg_stack, sqltxt)
 15            VALUES
 16            (systimestamp,
 17             sys.login_user,
 18             dbms_utility.format_error_stack, v_sqltext);
 19
 20    END log_server_errors;
 21  /

Trigger created.

SQL>

Now you’re set to log most errors in the STATS$ERROR_LOG table:

SQL> select a.object_name
  2  from dba_objects a, dba_objects b, dba_objects c
  3  order by 1;
from dba_objects a, dba_objects b, dba_objects c
     *
ERROR at line 2:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP


SQL>

Let’s query that table and see what it reports:

SQL> select * from stats$error_log;

ERR_DT
---------------------------------------------------------------------------
DB_USER
------------------------------
MSG_STACK
--------------------------------------------------------------------------------
SQLTXT
--------------------------------------------------------------------------------
01-SEP-08 09.35.15.159854 AM
******
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
select a.object_name from dba_objects a, dba_objects b, dba_objects c order by 1


SQL>

We’ve preserved the error stack and the ‘offending’ SQL along with the executing USER (obscured for security reasons) and the timestamp when the error was logged (which is approximately when the error occurred). Nice.

How many errors can you preserve in this log table? It all depends upon how much disk space you have to use. It should go without saying (but, hey, I’ll say it anyway) that this table needs to be monitored so it doesn’t become unwieldy; such data needs to be examined on a regular and fairly frequent basis so that problem areas can be addressed quickly and hopefully resolved.

Which errors won’t you capture? It appears to be a short list:

ORA-01403: no data found 
ORA-01422: exact fetch returns more than requested number of rows
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-01034: ORACLE not available
ORA-04030: out of process memory when trying to allocate string bytes (string, string)

Pretty much any other error should be logged in the table for examination at a later time, which is convenient for the DBA as sometimes he or she cannot address an error when it occurs, or the application doesn’t pass such error text through to the end-user. And you, as a DBA, can’t address errors of which you’re not informed.

An AFTER SERVERERROR trigger may not be something you want to have running all of the time, but it is nice to know you can fire one up and capture problem statements and the errors they create should you need to do so. Just as a carpenter doesn’t use a hammer for every task database tools like event triggers aren’t the solution to every problem. But it is nice to know they’re available when and if you need them, because having the right tools for the job makes the task so much easier.

August 18, 2008

If It Ain’t Fixed…

Filed under: General,pl/sql — dfitzjarrell @ 14:09

Occasionally certain objects in a database can fail to function, such as packages, procedures, triggers, functions, indexes, synonyms and views. Modifying any of the underlying dependent objects those items rely upon can change the status of such objects from ‘VALID’ to ‘INVALID’ or ‘UNUSABLE’. Short of attempting to access such objects and failing miserably how does one discover and correct such problems? The solution is fairly simple: ask the database.

Oracle provides, in the *_OBJECTS views, a column named STATUS which lists the status (obviously) of the object in question. When everything is right and proper that status should be ‘VALID’; of course no database is problem-free or immune to code changes so there are times when various objects may no longer be usable. Finding these objects is half of the battle; let’s look at a query to do that:

SQL> select object_name, object_type, status
  2  from user_objects
  3  where status  'VALID'
  4  order by created;


OBJECT_NAME                         OBJECT_TYPE         STATUS
----------------------------------- ------------------- -------
OWA_SYLK                            PACKAGE             INVALID
SCHEMA_ACCESS                       PROCEDURE           INVALID
CHECK_SAL                           FUNCTION            INVALID
RAISE_SAL                           PROCEDURE           INVALID
STRINGC                             FUNCTION            INVALID
JOB_PKG                             PACKAGE             INVALID
DATES_PKG                           PACKAGE             INVALID
EMP_VW                              VIEW                INVALID
VIEW_EMP_DEPT                       VIEW                INVALID
GET_EMPNAME                         FUNCTION            INVALID
GET_SAL                             PROCEDURE           INVALID
PROJECT                             SYNONYM             INVALID

12 rows selected.

SQL>

Notice the objects are ordered by their creation date; this allows the query to be used to write a dynamic script to recompile the objects and avoid dependency invalidations in the process:

SQL> select 'alter '||object_type||' '||object_name||' compile;'
  2  from user_objects
  3  where status  'VALID'
  4  and object_type in  ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM')
  5  union
  6  select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;'
  7  from user_objects
  8  where status  'VALID'
  9  and instr(object_type, ' BODY') > 0
 10  /

'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter FUNCTION CHECK_SAL compile;
alter FUNCTION GET_EMPNAME compile;
alter FUNCTION STRINGC compile;
alter PACKAGE DATES_PKG compile;
alter PACKAGE JOB_PKG compile;
alter PACKAGE OWA_SYLK compile;
alter PROCEDURE GET_SAL compile;
alter PROCEDURE RAISE_SAL compile;
alter PROCEDURE SCHEMA_ACCESS compile;
alter SYNONYM PrOJECT compile;
alter VIEW EMP_VW compile;
alter VIEW VIEW_EMP_DEPT compile;

12 rows selected.

SQL>

But, wait, there’s a neat little script that Oracle has provided to do the same job: utlrp.sql, located in the $ORACLE_HOME/rdbms/admin directory. It calls the UTL_RECOMP package and recompiles all invalid objects in the database (or tries to). It also reports how many of the recompiled objects generated errors and, if this number is larger than you might expect (yes, you may have invalid objects which cannot be ‘fixed’) then you run the first query listed and see which objects are affected. You can then use the second query to generate a dynamic list, modify that script to include a ‘SHOW ERRORS [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION| JAVA SOURCE | JAVA CLASS} [schema.]name]’ command after each compile statement and discover why each remaining invalid object would not successfully compile (full syntax is used for the illustrated ‘show errors’ invocations):

SQL> select 'alter '||object_type||' '||object_name||' compile;'
  2  from user_objects
  3  where status  'VALID'
  4  and object_type in  ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM')
  5  union
  6  select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;'
  7  from user_objects
  8  where status  'VALID'
  9  and instr(object_type, ' BODY') > 0
 10  /

'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter FUNCTION CHECK_SAL compile;
alter PROCEDURE RAISE_SAL compile;

SQL>

Generating a more detailed error message for each compile:

SQL> alter FUNCTION CHECK_SAL compile;

Warning: Function altered with compilation errors.

SQL> show errors function check_sal
Errors for FUNCTION CHECK_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/30     PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared
SQL> alter PROCEDURE RAISE_SAL compile;

Warning: Procedure altered with compilation errors.

SQL> show errors procedure raise_sal
Errors for PROCEDURE RAISE_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/20     PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared
SQL>

So we’re missing a table these objects depend upon, and until that table is replaced they will remain invalid, so we can stop trying to compile them.

In most cases views won’t need to be recompiled as select access to invalid views automatically performs that action; of course if the situation is like that shown above nothing will make the invalid view usable.

Unusable indexes are treated a bit differently, as they can’t be recompiled; they need to be rebuilt. Normally the database would be shutdown and then started in restricted mode to allow the rebuild to commence unhindered (rebuilding indexes really shouldn’t be done when users are actively accessing the database as it consumes resources and can cause exceptional delays for other processes while the rebuild of each index is taking place). A similar query to the invalid objects SQL can find the unusable indexes:

SQL> select index_name, status
  2  from user_indexes
  3  where status  'VALID';

no rows selected

SQL>

Had there been any unusable indexes the following query will generate the necessary executable statements:

select 'alter index '||index_name||' rebuild tablespace '||tablespace_name||';'
from user_indexes
where status  'VALID';

Spool that output to a file, verify it wrote correctly (the default line size may be a bit short for some resulting lines, so you need to check that each alter statement is, indeed, on a single line) then prepare to execute the script after the database is in restricted mode. Log the execution of the script so any resource-related errors can be addressed before it’s run again (usually, though, one run is sufficient).

Fixed views are a different story, as they’re based upon memory and internal disk structures. If any of these are declared INVALID the only recommended action to be taken is to contact Oracle Support as you cannot recompile such views. It’s likely that you’ll be told to shutdown and startup the database, but do NOT proceed with that action until told to do so by, you guessed it, Oracle Support.

So, finding and correcting invalid database objects is fairly straightforward; it does require attention to detail, however, to ensure that all objects which can be successfuly recompiled/rebuilt are again in a usable state. Practice on a test database is recommended so that if and when this process is required on a production system it’s been tested and re-tested and the method is properly defined and documented.

Of course, if it ain’t broke …

June 2, 2008

That’s A Wrap

Filed under: General,pl/sql — dfitzjarrell @ 16:10

Security is at the top of most management To-Do lists these days, and keeping application code obscured from prying eyes can be a priority in some organizations. Usually that’s solved by the nature of the compiler-based application (where one writes source code, compiles and links that into an executable and then distributes that executable and its ancillary files to end users), but PL/SQL isn’t a compiled language, so how does one obfuscate the source code so it can’t be read or modified by those not authorized to do so? Oracle comes to the rescue with the wrap utility.

The wrap utility (an external program installed with the Oracle software) has been around for years and uses a proprietary algorithm to ‘scramble’ the source code so as to make it essentially unreadable by the human eye. To illustrate what the wrap utility can accomplish let’s look at a pair of files declaring a package specification and a package body. First, the package specification:

CREATE PACKAGE dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE )
        RETURN NUMBER;

    FUNCTION minutes_since_midnight
        ( timevalue DATE )
        RETURN NUMBER;

    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER;

END dates_pkg;
/

Nothing here the everyday user can’t see, so we’ll leave this one unscrambled. Now let’s look at the package body:

CREATE PACKAGE BODY dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE)
        RETURN NUMBER
    IS
        varch_value VARCHAR (10);
        num_value NUMBER (20);
    BEGIN
 --
 -- First, we take a date and convert it to a date by converting it
 -- to a character string using the same format we will use to
 -- convert it BACK to a date again
 --
 -- Oh, then we convert it back to a character string
 --
 -- In Julian format, which is a number
 --
        SELECT TO_CHAR
               ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
               , 'J')
        INTO   varch_value
        FROM   dual;

 --
 -- Okay, so we had a Julian date as a number but we changed it to
 -- a character string so we could go back and make it a ...
 -- NUMBER ... again
 --
        SELECT TO_NUMBER (varch_value)
        INTO   num_value
        FROM   dual;

 --
 -- So, we finally make up our mind and keep it a number and
 -- return it from the function
 --
        RETURN (num_value);
    END julian_date;


    FUNCTION minutes_since_midnight (
        timevalue DATE)
        RETURN NUMBER
    IS
        secs_elapsed NUMBER (20);
        mins_elapsed NUMBER (20);
    BEGIN
 --
 -- So now we take a date and extract the time portion of it,
 -- convert that BACK to a date, then convert THAT to a string
 -- of seconds and convert THAT to a number
 --
 -- Is it me, or are we essentially driving across town just to
 -- go next door?
 --
        SELECT TO_NUMBER
               ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
               , 'SSSSS') )
        INTO   secs_elapsed
        FROM   dual;

 --
 -- Oooo, now we divide that total number of seconds by ...
 -- wait for it ...
 -- any second now ...
 -- 60!  Who would have thought that 60 seconds equals
 -- one minute?
 --
        SELECT (secs_elapsed / 60)
        INTO   mins_elapsed
        FROM   dual;

 --
 -- Before we rest on our laurels we return the minutes since midnight
 --
        RETURN (mins_elapsed);
    END minutes_since_midnight;


    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER
    IS
        final_number NUMBER (20);
        low_julian NUMBER (20);
        high_julian NUMBER (20);
        num_days NUMBER (20);
        num_minutes NUMBER (20);
        temp_mins NUMBER (20);
        min_low NUMBER (20);
        min_high NUMBER (20);
    BEGIN
 --
 -- Now, why didn't we use this julian_date function in the
 -- last installment of Julian conversions?
 --
 -- Oh, yeah, because we just WROTE that wonderful function
 --
 -- So, okay, we take our date values and return the Julian
 -- representations of them using all of the mathematical
 -- aerobics from earlier
 --
 -- I guess this is so much easier than simply subtracting
 -- them
 --
        SELECT julian_date (lowdate)
        INTO   low_julian
        FROM   dual;

        SELECT julian_date (highdate)
        INTO   high_julian
        FROM   dual;

 --
 -- Woo-hoo! Higher math time!  Subtract the Julian dates
 -- and get the number of days
 --
 -- Isn't that what we'd get if we just subtracted the
 -- submitted dates as-is?
 --
 -- Of course it is
 --
        SELECT (high_julian - low_julian)
        INTO   num_days
        FROM   dual;

 --
 -- Now we calculate the total minutes elapsed
 -- using our values generated by our extreme
 -- gyrations
 --
 -- I'm out of breath just thinking about all of this work
 --
        SELECT (num_days * 1440)
        INTO   num_minutes
        FROM   dual;

 --
 -- And now we put those other mathematical moves
 -- to use
 --
 -- Tell me again why we think we're smarter than
 -- the average bear?
 --
        SELECT minutes_since_midnight (lowdate)
        INTO   min_low
        FROM   dual;

        SELECT minutes_since_midnight (highdate)
        INTO   min_high
        FROM   dual;

 --
 -- Now this is disgusting
 --
 -- Using a TEMP variable to aid in simple mathematical
 -- processing
 --
        SELECT (min_high - min_low)
        INTO   temp_mins
        FROM   dual;

 --
 -- And this is better than:
 -- select (end_date - start_date)*1440 because?
 --
        SELECT (num_minutes + temp_mins)
        INTO   final_number
        FROM   dual;

        RETURN (final_number);

    END minutes_elapsed;
END dates_pkg;
/

There are some areas here which would be good to obscure (such as the actual program logic and my dripping sarcasm). Let’s run this through wrap and see what results:

wrap iname=datepkg.pls

PL/SQL Wrapper: Release 10.2.0.3.0- Production on Mon Jun 02 12:04:26 2008

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing datepkg.pls to datepkg.plb

(Note that wrap requires only an input file name; it will supply the output file name from the input file and change the extension to plb. You can, of course, supply an output file name using the oname parameter:

wrap iname=myfile.sql oname=yourfile.plq

and the wrap utility will be perfectly happy. I prefer to accept the default behaviour.)

So now we look at the contents of datepkg.plb and see what wrap hath wrought:

CREATE PACKAGE BODY dates_pkg wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
8fd 2ea
HqtWzGgdR01NBtyeAlYcu2V4y4Awg5DDLtAFyi/NDfmUx4K5yY1+DfxrrfMT24qrdohuXNxN
CQocIyZ3+aqU08q0OcZMwOM4QHgsu3+AcbwGHTdMupDu+MSdJPlNmp0/yVSRDrG8wWMaYgec
vGt1Cp4MwH91At1+jX5cMZu2KV0BDzASgJGaUo8ABfe5wPICeEG46jz8RhNBcZ1SW33eXn7t
B7wXzeo2Z0+QQoDLiRcsSSS/XMhAidNZyauxLPg0Da8aGNgdqz0tl4i66j2S2OqHdL8LS5Rd
lq8+Q8KkK8Eu+OIBkmT8UgPXybefgTkWt+e82r+lgJHKqnJh8aXFFOqadtNyFrts8/Jph9OV
EZUpa6epeksaX3HekigzRD4CZrIingkuKSoMxpAI45nKPfpNptHb0AKPbZNDMnONzN1H548z
pGsPdY0ffGsWVpvinmY6jDWcPKUBgr3zn97f21Q57pVnvVJht1EEgGBQoCDoa3wEjtVNKl8k
BwJmMV5K/f4EUWazs3dcNFKYvdoecIeEAnJmeQ1j1KQUOICGqPGrK3uVoLNdxM9FbJHTNIdn
YbW2a2h7jcj6dk0LIKyr0r/33QtmKK0jdJtf3HpqPZhB8UmStnoAJCBRj6f8/o+w6JEYywpf
AXEa3begV95iTZFoSWSXgJyGNlVt+cvPX+H+7BxIFrbkpTGa3sgTye6NFYNqUsnBd9+CB+gB
5qQPKAUotaZBuVV7

/

It doesn’t look the same as the source code, does it. The wrap utility was provided to do just that, obscure PL/SQL code/logic so the end user can’t see it. Installing either of the package bodies (datepkg.pls or datepkg.plb) results in the same functional code in the database, it’s just that the datepkg.plb script can’t be decoded by anyone except Oracle (and, in any of the SOURCE views [DBA_SOURCE, USER_SOURCE] all that is revealed is the wrapped text). That, of course, means THERE IS NO ORACLE-SUPPLIED OR ORACLE SUPPORTED UNWRAP UTILITY; you’ll need to save your original source code in the event you need to modify it due to bug fixes (heaven forbid) and/or functionality changes since you won’t get it back from the wrapped file.

How do you execute a .plb file? Pretty much like you’d run any other .sql script:

SQL> @datepkg
SQL> CREATE OR REPLACE PACKAGE dates_pkg
  2  AS
  3      FUNCTION julian_date
  4          ( date_to_convert DATE )
  5          RETURN NUMBER;
  6
  7      FUNCTION minutes_since_midnight
  8          ( timevalue DATE )
  9          RETURN NUMBER;
 10
 11      FUNCTION minutes_elapsed
 12          ( lowdate DATE
 13          , highdate DATE )
 14          RETURN NUMBER;
 15
 16  END dates_pkg;
 17  /

Package created.

SQL>
SQL> @datepkg.plb
SQL> CREATE or replace PACKAGE BODY dates_pkg wrapped
  2  a000000
  3  b2
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  b
 20  8fd 2ea
 21  HqtWzGgdR01NBtyeAlYcu2V4y4Awg5DDLtAFyi/NDfmUx4K5yY1+DfxrrfMT24qrdohuXNxN
 22  CQocIyZ3+aqU08q0OcZMwOM4QHgsu3+AcbwGHTdMupDu+MSdJPlNmp0/yVSRDrG8wWMaYgec
 23  vGt1Cp4MwH91At1+jX5cMZu2KV0BDzASgJGaUo8ABfe5wPICeEG46jz8RhNBcZ1SW33eXn7t
 24  B7wXzeo2Z0+QQoDLiRcsSSS/XMhAidNZyauxLPg0Da8aGNgdqz0tl4i66j2S2OqHdL8LS5Rd
 25  lq8+Q8KkK8Eu+OIBkmT8UgPXybefgTkWt+e82r+lgJHKqnJh8aXFFOqadtNyFrts8/Jph9OV
 26  EZUpa6epeksaX3HekigzRD4CZrIingkuKSoMxpAI45nKPfpNptHb0AKPbZNDMnONzN1H548z
 27  pGsPdY0ffGsWVpvinmY6jDWcPKUBgr3zn97f21Q57pVnvVJht1EEgGBQoCDoa3wEjtVNKl8k
 28  BwJmMV5K/f4EUWazs3dcNFKYvdoecIeEAnJmeQ1j1KQUOICGqPGrK3uVoLNdxM9FbJHTNIdn
 29  YbW2a2h7jcj6dk0LIKyr0r/33QtmKK0jdJtf3HpqPZhB8UmStnoAJCBRj6f8/o+w6JEYywpf
 30  AXEa3begV95iTZFoSWSXgJyGNlVt+cvPX+H+7BxIFrbkpTGa3sgTye6NFYNqUsnBd9+CB+gB
 31  5qQPKAUotaZBuVV7
 32
 33  /

Package body created.

SQL>

In 10g and later releases of Oracle a second method of wrapping PL/SQL source code is available with the DBMS_DDL.WRAP function. This is designed for use with dynamic PL/SQL statements:

declare
     ddl varchar2(32767);
begin
     ddl := 'create or replace procedure ...';
     execute immediate dbms_ddl.wrap(ddl);  -- 'Wraps' the procedure then executes it
end;
/

[There is a known bug with dbms_ddl.wrap and multibyte character sets (like Japanese) in base release 10.2.0.1; bug 4577670 causes an ORA-22921 error to be generated. This is fixed in patchset 10.2.0.2 and in release 11.1.0.6.]

If you’re concerned with unauthorized persons viewing sensitive PL/SQL source code then the wrap utility is probably something you should investigate. Remember, though, that you CAN’T* ‘unwrap’ the wrapped code later, so save your original source code somewhere safe for when you next need it.

* There are third-party unwrap tools for 9iR2 and earlier releases of Oracle, but, again, these are not supported by Oracle Corporation. As a result of this the wrap mechanism in 10g and later releases has changed and these tools will not work on wrapped code in any release after 9.2.0.x. You’re welcome to search google.com for them; I’ll not supply any links to them here.

And, that’s a wrap.

May 2, 2008

"I’ve never seen THAT error before …"

Filed under: General,pl/sql — dfitzjarrell @ 14:57

It would appear that the glorious error ORA-20999 appears quite frequently in application code, as evidenced by the history of searches on the Internet for this error number. Unfortunately for the person searching endlessly for this error it’s not a standard Oracle offering. Instead, it’s a user-defined exception/error number to catch and report any of a plethora of unnamed Oracle exceptions, which makes finding a definitive answer for what this error represents practically impossible, as it means what the application programmer intended, which can, and does, vary between application programmers and applications. Let’s look at the valid range of user-definable error numbers/exceptions and try to clear the clouded air a bit.

Oracle offers a range of error numbers which are not assigned any standard Oracle error text and are not associated with any fixed Oracle exceptions; this range starts at 20000 and ends at 20999. Looking at a basic PL/SQL block to define and use some of these available error numbers it can be seen that these can either be quite useful or quite frustrating:

SQL> --
SQL> -- User defined errors are numbered
SQL> -- from 20000 to 20999 inclusive
SQL> --
SQL> --
SQL> -- Any time you see an error number
SQL> -- in that range it's an exception/error
SQL> -- defined by the user
SQL> --
SQL>
SQL> declare
  2        ex20000 exception;
  3        ex20459 exception;
  4        ex20773 exception;
  5        ex20999 exception; -- a very popular error number
  6
  7        pragma exception_init(ex20000, -20000);
  8        pragma exception_init(ex20459, -20459);
  9        pragma exception_init(ex20773, -20773);
 10        pragma exception_init(ex20999, -20999);
 11
 12  begin
 13        begin
 14         begin
 15          begin
 16
 17           --
 18           -- Raising our first defined exception
 19           --
 20           raise ex20000;
 21
 22          exception
 23          when ex20000 then
 24
 25           --
 26           -- Return the first error code
 27           -- and where we generated it
 28           --
 29           dbms_output.put(dbms_utility.format_error_stack);
 30           dbms_output.put_line('   First error');
 31           dbms_output.put_line(dbms_utility.format_error_backtrace);
 32
 33          end;
 34
 35          --
 36          -- Raise the second defined error
 37          --
 38          raise ex20459;
 39
 40         exception
 41         when ex20459 then
 42
 43          --
 44          -- Return the error code
 45          -- and where we generated it
 46          --
 47          dbms_output.put(dbms_utility.format_error_stack);
 48          dbms_output.put_line('   Second error');
 49          dbms_output.put_line(dbms_utility.format_error_backtrace);
 50
 51         end;
 52
 53         --
 54         -- Raise third defined error
 55         --
 56         raise ex20773;
 57
 58        exception
 59        when ex20773 then
 60
 61         --
 62         -- Return the error code
 63         -- and where we generated it
 64         --
 65         dbms_output.put(dbms_utility.format_error_stack);
 66         dbms_output.put_line('   Third error');
 67         dbms_output.put_line(dbms_utility.format_error_backtrace);
 68
 69        end;
 70
 71        --
 72        -- Raise last defined error
 73        --
 74        raise ex20999;
 75
 76  exception
 77  when ex20999 then
 78
 79        --
 80        -- Return the error code
 81        -- and where we generated it
 82        --
 83        dbms_output.put(dbms_utility.format_error_stack);
 84        dbms_output.put_line('   Fourth error');
 85        dbms_output.put_line(dbms_utility.format_error_backtrace);
 86
 87  end;
 88  /
ORA-20000:
   First error
ORA-06512: at line 20

ORA-20459:
   Second error
ORA-06512: at line 38

ORA-20773:
   Third error
ORA-06512: at line 56

ORA-20999:
   Fourth error
ORA-06512: at line 74


PL/SQL procedure successfully completed.

SQL>

Not much useful information was presented here, so it’s uncertain what error or errors could have occurred to generate this progression of error messages. [The ORA-06512 error is an informative message as Oracle ‘unwinds’ the error stack and reports what it believes to be as the source of the actual error.] Such user-defined error numbers can be assigned to known Oracle errors, however:

SQL> --
SQL> -- Define error messages
SQL> -- which could be more descriptive
SQL> -- and exceptions which are
SQL> -- easier to handle
SQL> --
SQL>
SQL> declare
  2          ex20206 exception;
  3
  4          pragma exception_init(ex20206, -2060); -- select for update error
  5
  6  begin
  7
  8          raise ex20206;
  9
 10  exception
 11  when ex20206 then
 12          raise_application_error(-20206, 'Attempt to lock distributed tables', true);
 13
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-20206: Attempt to lock distributed tables
ORA-06512: at line 12
ORA-02060: select for update specified a join of distributed tables

SQL> declare
  2
  3          nolock exception;
  4          pragma exception_init(nolock, -69);
  5
  6  begin
  7          execute immediate 'alter table emp add myothercol number';
  8  exception
  9          when nolock then
 10                  raise_application_error(-20909, 'Thet ain''t allowed!!', true);
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-20909: Thet ain't allowed!!
ORA-06512: at line 10
ORA-00069: cannot acquire lock -- table locks disabled for EMP

SQL>

These examples are much clearer in what generated the exceptions and in the nature of the offending operations. [The ORA-00069 error mystically appears after someone has done this to a table:

SQL> alter table emp disable table lock;

Table altered.

SQL>

and someone else tries to lock that table with DDL or a call to ‘LOCK TABLE …’. The solution to that ‘problem’ is to do this:

SQL> alter table emp enable table lock;

Table altered.

SQL>

and then find out why someone else thought it necessary to disable locking on the affected table.]

Oracle does enforce the available error number range, as illustrated below, so existing, defined Oracle errors won’t be ‘stepped on’ inadvertently:

SQL> --
SQL> -- Attempt to raise
SQL> -- an exception using an error number
SQL> -- outside of the acceptable range
SQL> --
SQL>
SQL> begin
  2        raise_application_error(-1400, 'Something strange occurred ...');
  3
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -1400 is out of
range
ORA-06512: at line 2


SQL>

[An ORA-01400 error is generated when attempting to insert a NULL value into a column declared as NOT NULL.]

Apparently application programmers read their error messages and understand perfectly what has transpired, and that’s great for them:

“‘ORA-20618: Excessive flarpage’?!?!? What does THAT mean?!?!?”

“Oh, that means ‘don’t press the F8 key more than once on alternate Tuesdays’.”

“I never would have guessed …”

It isn’t good for the user community in general, however, as they are the ones seeing these ‘artificial’ error messages generated by the application code and, in many cases, have no idea what problems to report to Customer Service when they arise:

SQL>
SQL> --
SQL> -- This could possibly be a bit clearer ...
SQL> --
SQL>
SQL> declare
  2          ex20773 exception;
  3
  4          pragma exception_init(ex20773, -1002);  -- fetch out of sequence error
  5
  6  begin
  7
  8          raise ex20773;
  9
 10  exception
 11  when ex20773 then
 12          raise_application_error(-20773, 'Yew cain''t dew that!!!');
 13
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-20773: Yew cain't dew that!!!
ORA-06512: at line 12


SQL>

In cases where the users have no access to the developers (and the development team hasn’t obscured the package or procedure code with the wrap utility) it may be necessary to look at that code and see exactly what did generate the error. Of course this may ‘backfire’ as the actual error condition may be buried so deep in the code as to be nearly impossible to search for and the error message was generated by the ubiquitous catch-all ‘when others then …’ exception handler:

SQL>
SQL> --
SQL> -- This couldn't possibly be less informative
SQL> --
SQL>
SQL> declare
  2          ex20773 exception;
  3
  4          pragma exception_init(ex20773, -1002);  -- fetch out of sequence error
  5
  6  begin
  7
  8          raise ex20773;
  9
 10  exception
 11  when others then
 12          raise_application_error(-20773, 'Yew cain''t dew that!!!');
 13
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-20773: Yew cain't dew that!!!
ORA-06512: at line 12


SQL>

And, gee whiz, sometimes the developers decide to pass in the SQLCODE and SQLERRM values to RAISE_APPLICATION_ERROR, with disastrous results:

SQL>
SQL> --
SQL> -- Let's try this and see if it flies
SQL> --
SQL> -- we'll declare an exception then pass in the
SQL> -- generated SQLCODE to the
SQL> -- raise_application_error handler
SQL> --
SQL>
SQL> declare
  2          ex21000  exception;
  3
  4          pragma exception_init(ex21000, -19);
  5
  6  begin
  7          raise ex21000;
  8
  9  exception
 10          when ex21000 then
 11                  raise_application_error(SQLCODE, SQLERRM);
 12
 13  end;
 14  /
declare
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -19 is out of
range
ORA-06512: at line 11


SQL>

As mentioned earlier RAISE_APPLICATION_ERROR polices the error code values passed to it, and will unceremoniously complain when that value is out of range. [For those who are curious an ORA-00019 (which would generate the SQLCODE of -19) is a ‘maximum number of session licenses exceeded’ error.]

Possibly a ‘user-centered’ mindset on the part of the application programmers might better serve the end users, and maybe some testing should be done by people outside of the development community to verify that the error messages generated are truly useful to all parties involved.

I’ve blogged here about coding confusing text as error messages, so I won’t mention that topic again in this post. But maybe, just maybe, application programmers should read both posts and change their errant ways so the end users have something meaningful and useful as an error message and, as a result, their calls to the Help Desk aren’t exercises in futility.

Hope springs eternal.

Next Page »

Create a free website or blog at WordPress.com.