Wednesday, February 15, 2017

Enhanced Whitelist Management in 12.2

Way back in Oracle Database 12c Release 1, the PL/SQL team added whitelisting to the language. This means you can use the ACCESSIBLE BY clause to specify the "white list" of program units that are allowed to invoke another program unit (schema-level procedure, function, package).

For details on the 12.1 ACCESSIBLE BY feature set, check out my Oracle Magazine article, When Packages Need to Lose Weight. In that article, I step through the process of breaking up a large package body into "sub" packages whose access is restricted through use of the ACCESSIBLE BY feature.

I'll wait while you read the article.

Tick, tock, tick, tock....

OK, all caught up now? Great! In 12.2, there are just one enhancement, but a really nice one:

You can now specify whitelisting for a subprogram within a package. 

This is a very nice fine-tuning and is sure to come in handy.

In this post, I also show how you can specify the "unit kind" (program unit type) of the whitelisted program unit. This feature was available in 12.1, but I have not previously demonstrated it. So I will include it here. [Thanks to Iudith for pointing out that this is not a new 12.2 feature!]

The ability to specify the "unit kind" is useful when you have a trigger with the same name as a function, procedure or package (they do not, unfortunately, share the same namespace) and you need to distinguish which you want to include in the white list. Chances are, this will not be an issue for you, assuming you follow some common-sense naming conventions for your program unit.

Let's go exploring with code - all of which can be executed at LiveSQL.

First, I create a package spec and body that demonstrate the new functionality: I use ACCESSIBLE BY not at the package level, but with individual subprograms. Notice that the first two usages include the unit kind (PROCEDURE and TRIGGER).

The third usage does not include a unit kind. And the fourth usage tries to specify a packaged subprogram for whitelisting. I say "tries" because as you will soon see, that's not supported.

CREATE TABLE my_data (n NUMBER);

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE do_this;

   PROCEDURE this_for_proc_only
      ACCESSIBLE BY (PROCEDURE generic_name);

   PROCEDURE this_for_trigger_only
      ACCESSIBLE BY (TRIGGER generic_name);

   PROCEDURE this_for_any_generic_name
      ACCESSIBLE BY (generic_name);

   PROCEDURE this_for_pkgd_proc1_only
      ACCESSIBLE BY (PROCEDURE pkg1.myproc1);
END;
/

Package created.

CREATE OR REPLACE PACKAGE BODY pkg
IS
   PROCEDURE do_this
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE this_for_proc_only
      ACCESSIBLE BY (PROCEDURE generic_name)
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE this_for_trigger_only
      ACCESSIBLE BY (TRIGGER generic_name)
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE this_for_any_generic_name
      ACCESSIBLE BY (generic_name)
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE this_for_pkgd_proc1_only
      ACCESSIBLE BY (PROCEDURE pkg1.myproc1)
   IS
   BEGIN
      NULL;
   END;
END;
/

Package Body created.

So I now try to compile a trigger that calls the "trigger-only" procedure, and that works just fine. But if I try to use the "procedure-only" procedure, I get a compilation error.


CREATE OR REPLACE TRIGGER generic_name
   BEFORE INSERT
   ON my_data
   FOR EACH ROW
DECLARE
BEGIN
   pkg.this_for_trigger_only;
END;
/

Trigger created.

CREATE OR REPLACE TRIGGER generic_name
   BEFORE INSERT
   ON my_data
   FOR EACH ROW
DECLARE
BEGIN
   pkg.this_for_proc_only;
END;
/

PLS-00904: insufficient privilege to access object THIS_FOR_PROC_ONLY


Now I show the same thing for a procedure: it can't call the trigger-only version, but it can invoke the procedure-only subprogram.


CREATE OR REPLACE PROCEDURE generic_name
   AUTHID DEFINER
IS
BEGIN
   pkg.this_for_proc_only;
END;
/

Procedure created.

CREATE OR REPLACE PROCEDURE generic_name
   AUTHID DEFINER
IS
BEGIN
   pkg.this_for_trigger_only;
END;
/

PLS-00904: insufficient privilege to access object THIS_FOR_TRIGGER_ONLY


And now you can see that both the trigger and procedure can invoke the subprogram that did not include a "unit kind."


CREATE OR REPLACE TRIGGER generic_name
   BEFORE INSERT
   ON my_data
   FOR EACH ROW
DECLARE
BEGIN
   pkg.this_for_any_generic_name;
END;
/

Trigger created.

CREATE OR REPLACE PROCEDURE generic_name
   AUTHID DEFINER
IS
BEGIN
   pkg.this_for_any_generic_name;
END;
/

Procedure created.

Finally, I try to invoke the subprogram whose ACCESSIBLE BY clause specified "(PROCEDURE pkg1.myproc1)". Unfortunately, this is not yet supported. You can only list program units, not subprograms, in the list. So while the package named "pkg" compiles, you will it impossible to execute that subprogram from anywhere.

And as Iudith points out in her comments below, what's really going on is that the PL/SQL engine is trying to interpret "pkg1.myproc1" as a program unit named "MYPROC1" owned by a schema named "PKG1".


CREATE OR REPLACE PACKAGE pkg1
   AUTHID DEFINER
IS
   PROCEDURE myproc1;
END;
/

Package created.

CREATE OR REPLACE PACKAGE BODY pkg1
IS
   PROCEDURE myproc1
   IS
   BEGIN
      pkg.this_for_pkgd_proc1_only;
   END;
END;
/

PLS-00904: insufficient privilege to access object THIS_FOR_PKGD_PROC1_ONLY

Monday, February 6, 2017

How NOT to Handle Exceptions

Oracle Database raises an exception when something goes wrong (examples: divide by zero, duplicate value on unique index, value too large to fit in variable, etc.). You can also raise exceptions when an application error occurs (examples: balance too low, person not young enough, department ID is null).

If that exception occurs in your code, you have to make a decision:
Should I handle the exception and let it propagate out unhandled?
You should let it propagate unhandled (that is, not even trap it and re-raise) if handling it in that subprogram or block will not "add value" - there are no local variables whose values you need to log, for example.

The reason I say this is that at any point up the stack of subprogram invocations, you can always call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it will trace back to the line number on which the error was originally raised.

If you are going to handle the exception, you have to make several decisions:
  • Should I log the error? Yes, because you want to be able to go back later (or very soon) and see if you can diagnose the problem, then fix it.
  • Should I notify anyone of the error? This depends on your support infrastructure. Maybe depositing a row in the log table is enough. 
  • What should I log along with the error information? Generally you the developer should think about the application state - local variables, packaged/global variables, table changes - for which a "snapshot" (logged values) would help you understand what caused the error.
  • Should I then re-raise the same exception or another after logging? Almost always, yes. Sometimes, it is true, you can safely "hide" an error - very common for a NO_DATA_FOUND on a SELECT-INTO, when a lack of data simply indicates need for a different action, not an error - but for the most part you should always plan to re-raise the exception back out to the enclosing block. 
So that's a high-level Q&A. Now let's dive into some anti-patterns (common patterns one finds in PL/SQL code that are "anti" - not good things to do) to drive these points home.

You can see and run all of this code on LiveSQL.

I also put together a YouTube playlist if you prefer video.

OK, here we go.

1. Worst Exception Handler Ever

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   /* Lots of code executing and then... */ 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      NULL; 
END;

Completely swallows up and ignores just about any error the Oracle Database engine will raise. DO NOT DO THIS.

Want to ignore the error? Make it explicit and log it anyway:

CREATE OR REPLACE PROCEDURE my_procedure (name_in IN VARCHAR2) 
   AUTHID DEFINER 
IS 
   e_table_already_exists EXCEPTION; 
   PRAGMA EXCEPTION_INIT (e_table_already_exists, -955); 
BEGIN 
   EXECUTE IMMEDIATE 'CREATE TABLE ' || name_in || ' (n number)'; 
EXCEPTION 
   /* A named handler */ 
   WHEN e_table_already_exists 
   THEN 
      /* Even better: write a message to log. */ 
      NULL; 
    
   /* CHecking SQLCODE inside WHEN OTHERS */ 
   WHEN OTHERS 
   THEN 
      IF SQLCODE = -955 
      THEN 
         /* ORA-00955: name is already used by an existing object */ 
         NULL; 
      ELSE 
         RAISE; 
      END IF; 
END;

If you can anticipate a certain being raised and "That's OK", then handle it explicitly, either with an "IF SQLCODE = " inside WHEN OTHERS or by declaring an exception, associating it with the error code and then handling by name.

2. A Handler That Only Re-Raises: Why Bother?
CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   /* Lots of code executing and then... */ 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      RAISE; 
END;

This handler doesn't hide the error - it immediately passes it along to the outer block. Why would you do this? Not only is there no "added value", but by re-raising, any calls to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE will trace back to that later RAISE; and not to the line on which the error was *originally* raised.

Takeaway: don't handle unless you want to do something inside the handler, such as log the error information, raise a different exception or perform some corrective action.

3. "Log" Error with DBMS_OUTPUT? No Way!
CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   /* Lots of code executing and then... */ 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line ('Program failed!'); 
END;

OK, so I don't do *nothing* (NULL) in this handler, but I come awfully close. I do not re-raise, so the error is swallowed up. But I also simply write non-useful information out to the screen.

TO THE SCREEN.

If this code is running in production (the most important place from which to gather useful error-related diagnostics), can you see output to the screen via DBMS_OUTPUT? I bet not. And even if you could, surely you'd like to show more than a totally useful static piece of text?

4. Display Error and Re-Raise - Better But Still Pathetic

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   /* Lots of code executing and then... */ 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line (SQLERRM); 
      RAISE;
END;
OK, now I display the current error message, but still stuck with output to the screen, and anyway (a) we recommend you call DBMS_UTILITY.format_error_stack instead, since it avoids some possible truncation issues with SQLERRM (for very long error stacks) and (b) you really do need to see more than that! At least, though, I do re-raise the error.

5. Do Not Convert Exceptions to Status Codes

CREATE OR REPLACE PROCEDURE my_procedure (value_in     IN     INTEGER, 
                                          status_out      OUT INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   IF value_in > 100 
   THEN 
      /* All is fine */ 
      /* Execute some code */ 
      /* Set status to "ok" */ 
      status_out := 0; 
   ELSE 
      RAISE PROGRAM_ERROR; 
   END IF; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); 
      status_out := SQLCODE; 
END; 

This is a common technique in some other programming languages. For example, in C, many people only write functions and the function's return value is the status. If the status is not 0 or some other magic value indicating success, then you must abort.

But, oh, the resulting code!

DECLARE 
   l_status INTEGER; 
BEGIN  
   my_procedure (100, l_status);  
 
   IF l_status <> 0 
   THEN 
      /* That didn't go well. Need to stop or do *something*! */ 
      RAISE program_error; 
   END IF; 
    
   my_procedure (110, l_status); 
    
   IF l_status <> 0 
   THEN 
      /* That didn't go well. Need to stop or do *something*! */ 
      RAISE program_error; 
   END IF; 
END; 

6. Write to a Log Table, But Not This Way
CREATE TABLE error_log 
( 
   id               NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
   title            VARCHAR2 (200), 
   info             CLOB, 
   created_on       DATE DEFAULT SYSDATE, 
   created_by       VARCHAR2 (100), 
   callstack        CLOB, 
   errorstack       CLOB, 
   errorbacktrace   CLOB 
)
/

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      INSERT INTO error_log (title, 
                             info, 
                             created_by, 
                             callstack, 
                             errorstack, 
                             errorbacktrace) 
           VALUES ('Program failed', 
                   'value_in = ' || value_in, 
                   USER, 
                   DBMS_UTILITY.format_call_stack, 
                   DBMS_UTILITY.format_error_stack, 
                   DBMS_UTILITY.format_error_backtrace); 
 
      RAISE; 
END;
/

You should write error information to a log table. Here's a very simple example. Even better is to download and use the open source and popular Logger.

It's good that I write my errors to a table, but terrible that I do it this way. You should never "hard code" the inserts right inside the handler.

First, that's bad because if you ever need to change the table structure you (might) have to go back to each handler and change it.

Second, in this way, the error log entry becomes a part of your session's transaction.

If the process ends with an unhandled exception, your log entry is rolled back along with the "bad" transaction. Error information lost!

7. Get Fancy with Savepoints?

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER) 
   AUTHID DEFINER 
IS 
BEGIN 
   SAVEPOINT my_transaction; 
 
   UPDATE employees 
      SET salary = value_in; 
 
   RAISE PROGRAM_ERROR; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      ROLLBACK TO my_transaction; 
 
      INSERT INTO error_log (title, 
                             info, 
                             created_by, 
                             callstack, 
                             errorstack, 
                             errorbacktrace) 
           VALUES ('Program failed', 
                   'value_in = ' || value_in, 
                   USER, 
                   DBMS_UTILITY.format_call_stack, 
                   DBMS_UTILITY.format_error_stack, 
                   DBMS_UTILITY.format_error_backtrace); 
 
      SAVEPOINT error_logged; 
 
      RAISE;  
END;

Um, no. Yes, you could use savepoints to make sure that the log entry is not rolled back - but then any rollbacks that occur "higher up" in the callstack have to know to rollback only to this savepoint. It's messy and hard to ensure consistency. The autonomous transaction feature is much to be preferred.

Best Approach: Reusable Error Logger


CREATE OR REPLACE PROCEDURE log_error (title_in   IN error_log.title%TYPE, 
                                       info_in    IN error_log.info%TYPE) 
   AUTHID DEFINER 
IS 
   PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN 
   INSERT INTO error_log (title, 
                          info, 
                          created_by, 
                          callstack, 
                          errorstack, 
                          errorbacktrace) 
        VALUES (title_in, 
                info_in, 
                USER, 
                DBMS_UTILITY.format_call_stack, 
                DBMS_UTILITY.format_error_stack, 
                DBMS_UTILITY.format_error_backtrace); 
 
   COMMIT; 
END;

This is a very simple example; again, the Logger project is a MUCH better example - and code you can use "out of the box". The key points are: (a) move the insert into the procedure so it appears just once and can be modified here as needed; (b) The autonomous transaction pragma to ensure that I can commit this insert without affecting the unsaved changes in my "outer" transaction/session.

CREATE OR REPLACE PROCEDURE my_procedure (value_in IN INTEGER)  
   AUTHID DEFINER  
IS  
   l_local_variable   DATE;  
BEGIN  
   l_local_variable :=  
      CASE WHEN value_in > 100 THEN SYSDATE - 10 ELSE SYSDATE + 10 END;  
  
   UPDATE employees  
      SET salary = value_in;  
  
   RAISE PROGRAM_ERROR;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      log_error (  
         'my_procedure failed',  
            'value_in = '  
         || value_in  
         || ' | l_local_variable = '  
         || TO_CHAR (l_local_variable, 'YYYY-MM-DD HH24:MI:SS'));  
  
      RAISE;  
END;

In my exception handler, I call the generic logging routine. In that call, I include the values of both my parameter and local variable, so that I can use this information later to debug my program. Note that the local variable's value is lost unless I handle and log here.

Finally, I re-raise the exception to ensure that the enclosing block is aware of the "inner" failure.

Lots of Ways To Go Wrong....

But it's also not that hard to do things right. Just remember:

  • Do not swallow up errors. Make sure you at least log the error, if you truly do not want or need to stop further processing.
  • Log all local application state before propagating the exception to the outer block.
  • Write all error log information to a table through a reusable logging procedure, which is defined as an autonomous transaction.
And why write one of these yourself? Logger does it all for you already!

Tuesday, January 31, 2017

Find duplicate SQL statements with PL/Scope in 12.2

PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in your PL/SQL code.

You can do all sorts of amazing deep-dive analysis of your code with PL/Scope, answering questions like:
  • Where is a variable assigned a value in a program?
  • What variables are declared inside a given program?
  • Which programs call another program (that is, you can get down to a subprogram in a package)?
  • Find the type of a variable from its declaration.
  • Show where specific columns are referenced
  • Find all program units performing specific DML operations on table (and help you consolidate such statements)
  • Locate all SQL statements containing hints
  • Find all dynamic SQL usages – ideal for getting rid of SQL injection vulnerabilities
  • Show all locations in your code where you commit or rollback
And my latest favorite: Locate multiple appearances of same "canonical" SQL statement.

What does this mean and why does it matter? One great feature of PL/SQL is that the PL/SQL compiler automatically "canonicalizes" all static SQL statements in your code. This means that it upper-cases all keywords, removes extraneous white space, and so on. It standardizes the format of your SQL statements.

This standardization is important because it reduces the number of times that Oracle Database will "hard parse" your SQL statement when it is executed. That's because standardization of format raises the likelihood that SQL statements which "look" different (different case, line breaks, spaces) but are actually the same "under the surface".

So canonicalization of SQL can improve performance.

Now on to another benefit gained from this process: PL/Scope compares all the canonicalized SQL statements and assigns the same SQL_ID to matching statements. Consider the following two statements.

I turn on PL/Scope to gather both identifier and statement information. Then I compile two procedures. Clearly, they were written by two different developers on my team, with their formatting and naming standards. Sigh....welcome to the real world, right?

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'
/

CREATE OR REPLACE PROCEDURE p1 (p_id NUMBER, p_name OUT VARCHAR2)
IS
BEGIN
   SELECT 
          last_name
     INTO 
          p_name
     FROM 
          employees
    WHERE    
          employee_id = p_id;
END;
/

CREATE OR REPLACE PROCEDURE p2 (id_in NUMBER, name_out OUT VARCHAR2)
IS
BEGIN
   SELECT last_name
     INTO name_out
     FROM EMPLOYEES
    WHERE employee_id = id_in;
END;
/

Now let's analyze the PL/Scope data:

  SELECT signature, sql_id, text
    FROM all_statements
   WHERE object_name IN ('P1', 'P2')
ORDER BY line, col
/

517C7D44CC74C7BD752899158B277868 641rpxvq1qu8n 
SELECT LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1 
      
DFD0209075761780F18552DE6661B4E7 641rpxvq1qu8n 
SELECT LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1 

Brilliant! The signatures are different (no big surprise there; that's a value generated by PL/Scope that is guaranteed to be unique across all statements and identifiers). But notice that the SQL_IDs are the same - and the SQL statements are the same, too.

There you see the canonicalization at work.

Why I am so excited about this? One of the worst things you can do in your code is repeat stuff: copy/paste algorithms, magic values...and, most damaging of all, SQL statements.

If you repeat the same SQL statements across your application, it is much harder to optimize and maintain that code.

Now, with PL/Scope 12.2, we have an awesome and easy-to-use tool at our disposal to identify all duplicates of SQL. We can then decide which of those should be moved into functions (SELECTs) or procedures (non-query DML), so that the subprogram can be invoked in multiple places, and the SQL can be managed in one place.

Here's a query that tells you precisely where duplication of SQL occurs:

SELECT owner,
       object_name,
       line,
       text
  FROM all_statements
 WHERE sql_id IN (  SELECT sql_id
                      FROM all_statements
                     WHERE sql_id IS NOT NULL
                  GROUP BY sql_id
                    HAVING COUNT (*) > 1)
/

Cool stuff.

Try it out at LiveSQL, our free, 24x7 playground for Oracle Database 12c Release 2 SQL and PL/SQL (and a code library).

And check out the extensive doc on PL/Scope, with lots of examples and insights.

Thursday, January 26, 2017

Players for Logic Annual Championship for 2016

The following players will be invited to participate in the Logic Annual Championship for 2016, currently scheduled to take place on 4 April.

The number in parentheses after their names are the number of championships in which they have already participated.

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

Name Rank Qualification Country
Pavel Zeman (2) 1 Top 50 Czech Republic
SteliosVlasopoulos (3) 2 Top 50 Belgium
Marek Sobierajski (1) 3 Top 50 Poland
mentzel.iudith (3) 4 Top 50 Israel
Vyacheslav Stepanov (3) 5 Top 50 No Country Set
James Su (3) 6 Top 50 Canada
Rytis Budreika (3) 7 Top 50 Lithuania
JasonC (3) 8 Top 50 United Kingdom
Cor (2) 9 Top 50 Netherlands
Köteles Zsolt (2) 10 Top 50 Hungary
Kuvardin Evgeniy (2) 11 Top 50 Russia
NickL (2) 12 Top 50 United Kingdom
Chad Lee (3) 13 Top 50 United States
NeilC (0) 14 Top 50 United Kingdom
TZ (1) 15 Top 50 Lithuania
D. Kiser (2) 16 Top 50 United States
ted (3) 17 Top 50 United Kingdom
MarkM. (3) 18 Top 50 Germany
Elic (3) 19 Top 50 Belarus
mcelaya (1) 20 Top 50 Spain
Sandra99 (3) 21 Top 50 Italy
tonyC (2) 22 Top 50 United Kingdom
seanm95 (3) 23 Top 50 United States
Talebian (2) 24 Top 50 Netherlands
richdellheim (3) 25 Top 50 United States
Arūnas Antanaitis (1) 26 Top 50 Lithuania
ratte2k4 (1) 27 Top 50 Germany
umir (3) 28 Top 50 Italy
Kanellos (2) 29 Top 50 Greece
NielsHecker (3) 30 Top 50 Germany
Andrii Dorofeiev (2) 31 Top 50 Ukraine
Mehrab (3) 32 Top 50 United Kingdom
JustinCave (3) 33 Top 50 United States
krzysioh (2) 34 Top 50 Poland
Stanislovas (0) 35 Top 50 Lithuania
Vladimir13 (1) 36 Top 50 Russia
danad (3) 37 Top 50 Czech Republic
RalfK (2) 38 Top 50 Germany
YuanT (3) 39 Top 50 United States
Mike Tessier (1) 40 Top 50 Canada
Vijay Mahawar (3) 41 Top 50 No Country Set
Eric Levin (2) 42 Top 50 United States
whab@tele2.at (1) 43 Top 50 Austria
puzzle1fun (0) 44 Top 50 No Country Set
Sartograph (1) 45 Top 50 Germany
tonywinn (1) 46 Top 50 Australia
dovile (0) 47 Top 50 Lithuania
Jeff Stephenson (0) 48 Top 50 No Country Set
craig.mcfarlane (2) 49 Top 50 Norway
Paresh Patel (0) 50 Top 50 No Country Set

Tuesday, January 24, 2017

Confused by your error backtrace? Check the optimization level!

The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (and similar functionality in the UTL_CALL_STACK package) is a tremendously helpful function. It returns a formatted string that allows you to easily trace back to the line number on which an exception was raised.

You know what else is really helpful? The automatic optimization performed by the PL/SQL compiler. The default level is 2, which does an awful lot of optimizing for you. But if you want to get the most out of the optimizer, you can ratchet it up to level 3, which then added subprogram inlining.

Unfortunately, these two wonderful features don't mix all that well. Specifically, if you optimize at level 3, then the backtrace may not point all the way back to the line number in your "original" source code (without inlining, of course).

Run this LiveSQL script to see the following code below "in action."

ALTER SESSION SET plsql_optimize_level = 2
/

CREATE OR REPLACE PROCEDURE proc1
IS
   l_level   INTEGER;

   PROCEDURE inline_proc1
   IS
   BEGIN
      RAISE PROGRAM_ERROR;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('inline_proc1 handler');

         DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
         RAISE;
   END;
BEGIN
   SELECT plsql_optimize_level
     INTO l_level
     FROM user_plsql_object_settings
    WHERE name = 'PROC1';

   DBMS_OUTPUT.put_line ('Opt level = ' || l_level);

   inline_proc1;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('inline handler');

      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
      RAISE;
END;
/

BEGIN
   proc1;
END;
/

ALTER SESSION SET plsql_optimize_level = 3
/

ALTER PROCEDURE proc1
COMPILE
/

BEGIN
   proc1;
END;
/

Opt level = 2
inline_proc1 handler
ORA-06512: at "STEVEN.PROC1", line 8

inline handler
ORA-06512: at "STEVEN.PROC1", line 15
ORA-06512: at "STEVEN.PROC1", line 8
ORA-06512: at "STEVEN.PROC1", line 25


Opt level = 3
inline_proc1 handler
ORA-06512: at "STEVEN.PROC1", line 25

inline handler
ORA-06512: at "STEVEN.PROC1", line 25
ORA-06512: at "STEVEN.PROC1", line 25

I hope to have an update from the PL/SQL dev team on this topic soon, but I wanted to make you aware of this in case you get all confused and frustrated.

Check your optimization level! Oh, how do you do that? Here you go:

SELECT p.plsql_optimize_level
  FROM user_plsql_object_settings p
 WHERE name = 'PROC1'
/

Wednesday, January 18, 2017

Players for PL/SQL Challenge Championship for 2016

The following players will be invited to participate in the PL/SQL Challenge Championship for 2016, currently scheduled to take place on 23 March at 14:00 UTC.

The number in parentheses after their names are the number of championships in which they have already participated.

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

Name Rank Qualification Country
SteliosVlasopoulos (13) 1 Top 50 Belgium
siimkask (16) 2 Top 50 Estonia
mentzel.iudith (16) 3 Top 50 Israel
li_bao (4) 4 Top 50 Russia
James Su (11) 5 Top 50 Canada
ivan_blanarik (10) 6 Top 50 Slovakia
NielsHecker (17) 7 Top 50 Germany
Rakesh Dadhich (8) 8 Top 50 India
Karel_Prech (6) 9 Top 50 No Country Set
Marek Sobierajski (1) 10 Top 50 Poland
Rytis Budreika (4) 11 Top 50 Lithuania
_tiki_4_ (9) 12 Top 50 Germany
krzysioh (5) 13 Top 50 Poland
Chad Lee (13) 14 Top 50 United States
João Borges Barreto (6) 15 Top 50 Portugal
Andrey Zaytsev (5) 16 Top 50 Russia
coba (1) 17 Top 50 Netherlands
patch72 (3) 18 Top 50 Netherlands
Kuvardin Evgeniy (2) 19 Top 50 Russia
VictorD (3) 20 Top 50 Russia
Vyacheslav Stepanov (15) 21 Top 50 No Country Set
Maxim Borunov (3) 22 Top 50 Russia
tonyC (2) 23 Top 50 United Kingdom
JustinCave (13) 24 Top 50 United States
Chase (2) 25 Top 50 Canada
Joaquin_Gonzalez (10) 26 Top 50 Spain
Pavel_Noga (4) 27 Top 50 Czech Republic
seanm95 (3) 28 Top 50 United States
syukhno (0) 29 Top 50 Ukraine
tonywinn (5) 30 Top 50 Australia
JasonC (1) 31 Top 50 United Kingdom
Andrii Dorofeiev (0) 32 Top 50 Ukraine
Sachi (1) 33 Top 50 India
ratte2k4 (0) 34 Top 50 Germany
Alexey Ponomarenko (1) 35 Top 50 No Country Set
PZOL (2) 36 Top 50 Hungary
Otto Palenicek (0) 37 Top 50 Germany
Jānis Baiža (10) 38 Top 50 Latvia
JeroenR (10) 39 Top 50 Netherlands
Rimantas Adomauskas (3) 40 Top 50 Lithuania
Henry_A (3) 41 Top 50 Czech Republic
Sherry (2) 42 Top 50 Czech Republic
ted (0) 43 Top 50 United Kingdom
MarkM. (0) 44 Top 50 Germany
YuanT (11) 45 Top 50 United States
kbentley1 (1) 46 Top 50 United States
swesley_perth (2) 47 Top 50 Australia
Talebian (3) 48 Top 50 Netherlands
mcelaya (1) 49 Top 50 Spain
berkeso (0) 50 Top 50 Hungary

Emulating a finally clause in PL/SQL

PL/SQL does not support a finally clause, as many other languages do, including Java. Here's a description of the finally block from the Java SE doc:
The finally block always executes when the try block exits. This ensures that the finally block is executed even if an unexpected exception occurs. But finally is useful for more than just exception handling — it allows the programmer to avoid having cleanup code accidentally bypassed by a return, continue, or break. Putting cleanup code in a finally block is always a good practice, even when no exceptions are anticipated.
The first thing to say regarding PL/SQL and finally is that the need for it in PL/SQL is likely less critical than in other languages, precisely because the PL/SQL runtime engine (and the underlying Oracle Database engine) does most of the clean up for you.

Any variables you declare, cursors you open, types you define inside a block are automatically cleaned up (memory released) when that block terminates.

Still, there are exceptions to this rule, including:

>> Changes to tables are not automatically rolled back or committed when a block terminates.

If you include an autonomous transaction pragma in your block, PL/SQL will "insist" (raise an exception at runtime) if you do not  rollback or commit, but that's different.

>> Elements declared at the package level have session scope. They will not be automatically cleaned up when a block in which they are used terminates.

Here's a very simple demonstration of that fact. I declare a cursor at the package level, open it inside a block, "forget" to close it, and then try to open it again in another block:

CREATE OR REPLACE PACKAGE serial_package AUTHID DEFINER
AS
   CURSOR emps_cur
   IS
      SELECT *
        FROM employees;
END serial_package;
/

BEGIN
   OPEN serial_package.emps_cur;
END;
/

BEGIN
   OPEN serial_package.emps_cur;
END;
/

BEGIN
   OPEN serial_package.emps_cur;
END;
/

ORA-06511: PL/SQL: cursor already open
ORA-06512: at "STEVEN.SERIAL_PACKAGE", line 5
ORA-06512: at line 2

Try it out yourself in LiveSQL.

Since there is no finally clause, you have to take care of things yourself. The best way to do this - and I am not claiming it is optimal - is to create a nested cleanup procedure and invoke that as needed.

Here we go - no more error when I attempt to open the cursor the second time.

CREATE OR REPLACE PACKAGE serial_package AUTHID DEFINER
AS
   CURSOR emps_cur
   IS
      SELECT *
        FROM employees;
END serial_package;
/

CREATE OR REPLACE PROCEDURE use_packaged_cursor AUTHID DEFINER
IS
   PROCEDURE cleanup
   IS
   BEGIN
      /* If called from exception section log the error */
      IF SQLCODE <> 0
      THEN
         /* Uses open source Logger utility:
               https://github.com/OraOpenSource/Logger */
         logger.log_error ('use_packaged_cursor');
      END IF;
      
      IF serial_package.emps_cur%ISOPEN
      THEN
         CLOSE serial_package.emps_cur;
      END IF;
   END cleanup;
BEGIN
   OPEN serial_package.emps_cur;
   
   cleanup;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      /* Clean up but do not re-raise (just to show that you might want
         different behaviors for different exceptions). */
      cleanup;
      
   WHEN OTHERS
   THEN
      cleanup;
      RAISE;
END;
/

BEGIN
   use_packaged_cursor;
END;
/

PL/SQL procedure successfully completed.

BEGIN
   use_packaged_cursor;
END;
/

PL/SQL procedure successfully completed.

(also available in LiveSQL)

Now, I am not, repeat NOT, claiming that this is as good as having a finally clause. I am just saying: this is how you can (have to) achieve a similar effect.