Tuesday, September 20, 2016

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks.

The three ways are:
  1. SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely to run into: the error stack will be truncated at 512 bytes, and you might lose some error information.
  2. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will.
  3. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace. 
Note: check out this LiveSQL script if you'd like to explore more of the nuances between SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK.

Other helpful resources regarding error management in PL/SQL:

Nine Good to Knows for PL/SQL Error Management
PL/SQL Error Handling (doc)

Finally, here's the code from our quiz that you can copy/paste into your editor to check out these alternatives.
CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'ORA-'
         || TO_CHAR (UTL_CALL_STACK.error_number (1), 'fm00000')
         || ': '
         || UTL_CALL_STACK.error_msg (1));
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

DROP PROCEDURE plch_check_balance
/

Friday, September 16, 2016

How to get compiler settings for PL/SQL program units

This question was posted today on the OTN SQL-PL/SQL Forum:
Is there a way, after compilation, to detect what level of optimization was used? Perhaps you have something already in production, and you just want to find some candidates for recompile (but not necessarily any changes in the source) with increased optimization level.
The answer is: Yes! Just run a query against the ALL_PLSQL_OBJECT_SETTINGS data dictionary view (or the USER_PLSQL_OBJECT_SETTINGS, to see information only about program units you own).

For a given schema and object name, the following information is provided through this view:



So suppose you need to identify any program units that may have been accidentally compiled with an optimization level below 2 (the default, aggressive optimization, which can only be improved upon by level 3, which turns on inlining of subprograms globally).

No problem!

SELECT *
  FROM user_plsql_object_settings p
 WHERE p.plsql_optimize_level < 2

Or how about: for which program units has identifier information been gathered for PL/Sscope analysis?

SELECT *
  FROM user_plsql_object_settings p
 WHERE plscope_settings = 'IDENTIFIERS:ALL'

I offer a LiveSQL script to help you take full, quick advantage of this data dictionary view.

Thursday, September 15, 2016

Maintaining transaction integrity with FORALL and multiple DML statements

FORALL is used to avoid row-by-row execution of the same DML statement (differing only in the values bound into it), thereby reducing context switching between the PL/SQL and SQL engines.

I will assume in this post that you have a basic working knowledge of BULK COLLECT and FORALL. If not, check out:

A Checklist for Conversion to Bulk Processing

As noted in the checklist, you need to document your current transaction behavior, and then make sure that the same behavior manifests in your bulk implementation.

I published a video that takes you through the key steps in the conversion from row-by-row to bulk. The code for this video is available on LiveSQL.

A viewer asked:
Hi Steven - @minute 21.56 in the video, function update_employee. in case of any update failures, you are handling an exception "bulk_error" but how do you ensure that the corresponding insert gets rolled back? You showed a way to communicate the failed employee records from insert_history function to update_employee, but it needs to be done the other way round too ? to ensure "INSERT+ADJUST+UPDATE" is one transaction like in the case of cursor for loop.
I decided to answer this question by writing a post on my blog - this one. So let's dive in. The original code looked like this:

CREATE OR REPLACE PROCEDURE upd_for_dept (
   dept_in     IN employees.department_id%TYPE
 , newsal_in   IN employees.salary%TYPE)
IS
   CURSOR emp_cur
   IS
      SELECT employee_id, salary, hire_date
        FROM employees
       WHERE department_id = dept_in
         FOR UPDATE;
BEGIN
   FOR rec IN emp_cur
   LOOP
      BEGIN
         INSERT INTO employee_history (employee_id, salary, hire_date)
              VALUES (rec.employee_id, rec.salary, rec.hire_date);

         rec.salary := newsal_in;


         adjust_compensation (rec.employee_id, rec.salary);


         UPDATE employees

            SET salary = rec.salary
          WHERE employee_id = rec.employee_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            log_error;
      END;
   END LOOP;
END upd_for_dept;

[Note: I have added the "FOR UPDATE" clause in bold above; that was not in the video and original code, but it should be to ensure that while I am doing my updates, no one else can come along and modify a row in my "target" dataset. thanks to Martin Rose for pointing this out in his acerbic comments on my video. :-) ]

And I noted in the video that if an insert fails, the update does not take place, so we need to avoid this in the bulk processing. I did so by removing the employee ID of any failed insert from the bind array that is used in the second FORALL for the update:


PROCEDURE insert_history
IS
BEGIN
  FORALL indx IN 1 .. l_employees.COUNT SAVE EXCEPTIONS
     INSERT
       INTO employee_history (employee_id
                            ,  salary
                            ,  hire_date)
     VALUES (
               l_employees (indx).employee_id
             ,  l_employees (indx).salary
             ,  l_employees (indx).hire_date);
EXCEPTION
  WHEN bulk_errors
  THEN
     FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
     LOOP
        /* Log the error then ... Communicate this failure to update:
        Delete this row so that the update will not take place.
        */
        l_employees.delete (
           SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX);
     END LOOP;


END insert_history;

But Jinay correctly points out that in my bulkified code, if the update fails, I do not reverse the insert. 

Well, I can justify that omission easily: it is not an omission. The original code has this same flaw in it!

So really what Jinay has identified is a possible/likely bug in my original code. This is a very common experience when developers perform code reviews and is a primary motivator for pair programming (kill those bugs before/as they are written).

To achieve the "effect" described by Jinay, I can add SAVEPOINTs:

BEGIN
   FOR rec IN emp_cur
   LOOP
      BEGIN
         SAVEPOINT before_insert;

         INSERT
           INTO employee_history (employee_id, salary, hire_date)
         VALUES (rec.employee_id, rec.salary, rec.hire_date);

         rec.salary := newsal_in;

         adjust_compensation (rec.employee_id, rec.salary);

         UPDATE employees
            SET salary = rec.salary
          WHERE employee_id = rec.employee_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            ROLLBACK TO before_insert;
            log_error;
      END;
   END LOOP;

END upd_for_dept;

Now if the insert succeeds and the update fails, the insert will be rolled back.

OK, so then the question is: how can I achieve the same effect when I convert to bulk processing? Easily...with more bulk processing and the helpful RETURNING clause.

PROCEDURE insert_history
IS
BEGIN
  FORALL indx IN 1 .. l_employees.COUNT SAVE EXCEPTIONS
     INSERT
       INTO employee_history (employee_id
                            ,  salary
                            ,  hire_date)
     VALUES (
               l_employees (indx).employee_id
             ,  l_employees (indx).salary
             ,  l_employees (indx).hire_date)
     RETURNING id, employee_id BULK COLLECT INTO l_inserted;
EXCEPTION
  WHEN bulk_errors
  THEN
     FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
     LOOP
        /* Log the error then ... Communicate this failure to update:
        Delete this row so that the update will not take place.
        */
        l_employees.delete (
           SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX);
     END LOOP;


END insert_history;

where l_inserted is declared as follows:

   TYPE inserted_rt IS RECORD
   (
      id            employee_history.id%TYPE,
      employee_id   employee_history.employee_id%TYPE
   );


   TYPE inserted_t IS TABLE OF inserted_rt;

   l_inserted inserted_t := inserted_t();

I can then modify the exception handler in the update "phase" of this bulk-ified procedure as follows:

First, add another nested subprogram:

   PROCEDURE remove_history_row (
      employee_id_in   IN employees.employee_id%TYPE)
   IS
      l_found_index   INTEGER;
      l_index         INTEGER := l_inserted.FIRST;
   BEGIN
      /* Find matching element in l_inserted, and remove */

      WHILE l_found_index IS NULL AND l_index IS NOT NULL
      LOOP
         IF l_inserted (l_index).employee_id = employee_id_in
         THEN
            l_found_index := l_index;
         ELSE
            l_index := l_inserted.NEXT (l_index);
         END IF;
      END LOOP;

      IF l_found_index IS NOT NULL
      THEN
         DELETE FROM employee_history
               WHERE id = l_inserted (l_found_index).id;
      END IF;
   END;

Then invoke the subprogram inside the exception section of the update procedure:

   EXCEPTION
      WHEN bulk_errors
      THEN
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            remove_history_row (
               l_employees (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX).employee_id);

            log_error (
                  'Unable to update salary for employee '
               || l_employees (
                     SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX).employee_id,
               SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
         END LOOP;
   END update_employees;

I have now carried over the SAVEPOINT-based behavior to my bulk-ified version. Note that I am performing row-by-row deletes in remove_history_row. Perhaps you, my dear reader, would like to take on a little exercise of enhancing the solution I offer above to use FORALL to delete all the inserted rows for which the update failed!

Monday, September 12, 2016

PL/SQL 101: Save your source code to files

PL/SQL is a database programming language. This means that your source code is compiled into and executed from within the Oracle Database. There are many fantastic consequences of this fact, many of which are explored in Bryn Llewellyn's Why Use PL/SQL? whitepaper.

But this also can mean that developers see the database as the natural repository for the original source code, and this is a bad mistake to make. It's not the sort of mistake any JavaScript or Java or php developer would ever make, because that code is not compiled into the database (well, you can compile Java into the database, but that's not where 99.99% of all Java code lives).

But it's a mistake that apparently too many Oracle Database developers make.

So here's the bottom line:
Store each PL/SQL program unit in its own file. Use a source code control system to manage those files. Compile them into the database as needed for development and testing.
In other words: you should never keep the original version of your code in the database, especially if you are part of a development team, with multiple people needing to work on a given program unit, sometimes at the same time. Otherwise, chaos might ensure, resulting in "lost updates" to your code.

Hmm, that might sound kind of odd to some of you.
"Steven, who works for Oracle Corporation, who thinks that Oracle Database is a really great database, is telling us to avoid the database?"
Well, I do think Oracle Database is a really great database. And I do think that you can trust it with your data, your applications, the care and feeding of your users. :-)

But it was not designed to be a development environment per se. The requirements developers have for writing code is certainly very different from requirements users have for accessing and changing their data.

So Do This

Suppose I need to create a package. I type the following in my worksheet in SQL Developer (a free, powerful IDE for Oracle Database):


Then the very next thing action I take, because I hate to lose any of my work, is to save this text to a file:


Notice I used the ".pks" extension for my package specification. You could just use the default ".sql" for all your database code files, but I prefer to use specialized prefixes for my program units, views and more.





Then I compile my code, and start working on it. And any time I need to go back and work on the package specification some more, I open the file.


I never, never edit a program unit directly from the database. Sure, you can do that. It is "allowed." It's just not recommended. Seriously. Not. Recommended.

Lost Updates - Lost CODE Updates, That Is

You can double-click on your program unit name in the Schema Browser. That code will appear in an editor. You can make changes and compile it back into the database.

But what if another developer on your team is doing the same thing? A minute after you double-clicked on the program name, Sandra did, too. You make changes, which Sandra doesn't see. Sandra makes changes, which you can't see.

You compile your changes into the database. You give yourself a pat on the back: Job well done.


And then you go to lunch.


At which point, Sandra compiles her changes into the database. She eats lunch at her desk, editing and compiling changes to ten more program units. A busy, productive programmer.

And when you come back from lunch? Your changes are gone.


There are several ways in which database programming is really different from Java, JavaScript, etc. programming. Some of these ways are super cool, such as: there is no need for a make utility or process. The database takes care of all of that for you.

But in other ways, writing PL/SQL program units is - or should be - no different from writing a Java class or a JavaScript...um...script. 

You figure out the algorithm you need to implement the requirements.

You translate that algorithm into code. You type that code in an editor.

You save it to a file, and then you compile that file into the database.

You check in that file to your source code control system.

Need to make more changes? Check it out (or not, depending on how your tool works), open the file, and work on that file. And so on, and so forth.

By taking this approach, there is a much smaller chance that code changes will be lost. 

And a much greater chance that a whole team of developers can work together efficiently to developer a highly secure, very efficient, easy to maintain base of code in PL/SQL that can be utilized by UI developers.

Note: Yalim Gerger (@yalimgerger) of Formspider offers a very interesting tool, Gitora, the offers a PL/SQL API to manage your database objects directly in Git. So if you really, really don't like my advice and really, really want to work on code directly in the database and avoid files entirely, check out Gitora.


Friday, September 9, 2016

Fine-tuning the Quiz-taking Experience on the Oracle Dev Gym

Back in June 2016, we announced "early adaptor" access to the Oracle Dev Gym, a new skin on top of the PL/SQL Challenge quiz platform.

Since then, we've gotten lots of great feedback, and lots of usage on the site. Players have set up 543 monthly goals, with over 2,200 workout exercises (with 1,200+ actually completed :-) ). Plus more than 4,500 quizzes have been answered at the Dev Gym, vs. the "traditional" PL/SQL Challenge.

All good news.

When I held our first live Dev Gym workout with the Chicago Oracle User Group two weeks ago, however, I did get some very critical reviews of the quiz-taking experience.

Basically, we are using too much real estate for players to easily see the code in the question and the various multiple choices, some of which can be very long in and of themselves.

So we went back to the drawing board and made a number of changes, which we rolled into production today.

To summarize:
  • The left sidebar is closed by default to minimize distraction and usage of page real estate.
  • Each choice now uses up less space. We've reduced white space and padding wherever possible.
  • There is now a "scratchpad" in the right sidebar, so that you can write notes to yourself (comparisons between choices, for example) that remain visible even as you scroll up and down the question.
  • Choices can be expanded or compressed - individually or as a group. With just a click or two of your mouse, you can zoom in on a single choice, or two, allowing you to focus attention more tightly.
Note: there is one known issue with the collapsing process. When you collapse all the direction icon on the left is not changing. So you need to click twice on it to expand a particular choice. We should have that fixed soon.

We explored other possibilities, like using sliders to allow you to have the question remain on the left, while you scroll up and down through the choices. We decided not to pursue this approach because it does not work well on mobile devices, and we are aiming for a smooth, responsive website regardless of your device.

I offer some screenshots below to give you an idea of what we've done. Please do take a quiz on the Dev Gym (accessed through the PL/SQL Challenge) and let us know what think.

Oh, and that's not all: we've also added a dashboard on the home page.

And if you have other suggestions for improving the quiz-taking experience or the dashboard or anything else, don't hesitate to click on the Feedback link on the top right of the site!

Scratchpad

See something on Choice 1 you want to remember when looking at the other choices? Jot it down!



Collapsed Choices

You can Expand All or Collapse All and then fine-tune from there.













Just One Choice Uncollapsed

Open up one or more choices selectively. The banner on each choice displays green if you've selected it as correct. This is visible even when collapsed.












Dashboard

Let us know if you'd like to see different information or see this data differently (visually).


Thursday, September 8, 2016

PL/SQL 101: Raising exceptions in PL/SQL

Most of the time that an exception is raised in your application, Oracle Database will do the raising. That is, some kind of problem has occurred during the execution of your code. You have no control over that; once the exception has been raised, all you can do is handle the exception - or let it "escape" unhandled to the host environment.

You can, however, raise exceptions yourself in your own code. Why would you want to do this? Because not every error in an application is the result of a failure of internal processing in the Oracle database. It is also possible that a certain data condition constitutes an error in your application, in which case you need to stop the processing of your algorithms and, quite likely, notify the user that something is wrong.

PL/SQL offers two ways for you to raise an exception:

  1. The RAISE statement
  2. The RAISE_APPLICATION_ERROR built-in procedure

Of course, you could also force the raising of an exception by Oracle, with code like this:

BEGIN
   my_variable := 1/0;
END;

But I am going to show you how to terminate block execution with lots more control and information than that will do for you.

RAISE

Use RAISE to raise a previously-defined exception. It could be a system exception, provided by PL/SQL itself, such as NO_DATA_FOUND. Or it could be an exception that you declare yourself.

Suppose I have a procedure that accepts a new salary to be assigned to an employee. The rule you want to enforce is that the new salary cannot be negative. This is a sort of "value error", so you could simply raise that pre-defined exception.

[Note: all code in this post may found in my LiveSQL script.]

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF salary_in < 0 
   THEN
      RAISE VALUE_ERROR;
   END IF;

   ... rest of procedure
END;

When you run this procedure as follows:

BEGIN
   use_salary (salary_in => -1);
END;

You will see this error:

ORA-06502: PL/SQL: numeric or value error 

So you stopped the program from continuing, and that's great. It can, however, be confusing to people maintaining your code and users seeing your error message to fall back on the generic PL/SQL exception. So you could declare your own exception in exactly the same way that PL/SQL defines exceptions like VALUE_ERROR.

Which probably has you thinking: how does PL/SQL define these exceptions? They are, for the most part, found in a special package named STANDARD. You probably haven't heard of it, and that's OK. It is one of the two default packages of PL/SQL: STANDARD and DBMS_STANDARD.

By default, I mean that you can reference elements in these packages without including their package name. For example, I could reference VALUE_ERROR in the use_salary procedure as follows:

PROCEDURE use_salary (salary_in IN NUMBER) 
   AUTHID DEFINER
IS
BEGIN
   IF salary_in < 0 
   THEN
      RAISE STANDARD.VALUE_ERROR;
   END IF;

But I don't have to. If the compiler cannot otherwise resolve the reference to VALUE_ERROR (without the package name), it will then see if that identifier can be resolved in STANDARD or DBMS_STANDARD.

And if we look inside the STANDARD package, we find code like this:

  VALUE_ERROR exception;
  pragma EXCEPTION_INIT(VALUE_ERROR, '-6502');

Let's apply that same technique in use_salary:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
   AUTHID DEFINER
IS
   negative_salary EXCEPTION;
   PRAGMA EXCEPTION_INIT (negative_salary, '-6502');
BEGIN
   IF salary_in < 0 
   THEN
      RAISE negative_salary;
   END IF;

   ... rest of procedure
END;

And when I execute the procedure and pass it -1, I see the same error information:

ORA-06502: PL/SQL: numeric or value error

So what's the advantage of switching to own exception? In this case (so far), very minor: your code raises an exception which by name tells you and anyone maintaining the code later what the problem is.The information presented to the user, however, is no more informative than before. If you want to change that message to something more understandable to users, you will want to "switch" to RAISE_APPLICATION_ERROR, which I cover below.

Before we move on, though, here are some thoughts on why you might want to define your own exceptions: Suppose I have several error conditions, and for each error condition, I need to take different actions. I could write code like this in my executable section:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
BEGIN
   CASE
      WHEN salary_in < 0 
      THEN 
         notify_support (
            'Negative salary submitted ' || salary_in); 
         RAISE VALUE_ERROR;
      WHEN salary_in > 10000 
      THEN 
         notify_support (
            'Too large salary submitted ' || salary_in); 
         RAISE VALUE_ERROR;
      WHEN salary_in < 100 
      THEN 
         notify_hr (
            'No one should be treated so shabbily! ' || 
            salary_in);  
         RAISE VALUE_ERROR;
      ELSE
         /* No problems, proceed with normal execution*/
         NULL;
   END CASE;

   /* Rest of procedure */
END;

But then I have filled up the first part of executable section with error handling code. That makes it harder to see through the "noise" and focus on the actual algorithm I wrote to implement use_salary. A better approach is as follows:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
   negative_salary EXCEPTION;
   too_large_salary EXCEPTION;
   too_small_salary EXCEPTION;
BEGIN
   CASE
      WHEN salary_in < 0 THEN RAISE negative_salary;
      WHEN salary_in > 10000 THEN RAISE too_large_salary;
      WHEN salary_in < 100 THEN RAISE too_small_salary;
      ELSE NULL;
   END CASE;

   /* Rest of procedure */

EXCEPTION
   WHEN negative_salary
   THEN
      notify_support (
         'Negative salary submitted ' || salary_in); 
      RAISE VALUE_ERROR;

   WHEN too_large_salary
   THEN
      notify_support (
         'Too large salary submitted ' || salary_in); 
      RAISE VALUE_ERROR;

   WHEN too_small_salary
   THEN
      notify_hr (
         'No one should be treated so shabbily! ' || salary_in);  
      RAISE VALUE_ERROR;
END;

Now all my exception handling code is in the exception section where it belongs.

Notice that I do not use the pragma to assign an error code to my exceptions. That means that the value returned by SQLCODE will be 1. All the time. Which is not too helpful in terms of distinguishing between the errors. In this case, it does not matter, because we are logging the specifics with support or HR and then causing the procedure to fail with a generic message via VALUE_ERROR (often a good idea from the standpoint of security).

RAISE_APPLICATION_ERROR

This built-in, actually defined in the DBMS_STANDARD package, should be used when you need to communicate an application-specific error message to your users.

With RAISE_APPLICATION_ERROR, you provide both the error code and error message as follows:

BEGIN
   RAISE_APPLICATION_ERROR (-20000, 'Say whatever you want');
END;

And when you run the above block it will fail with an unhandled exception, displaying this information:

ORA-20000: Say whatever you want 

So if I do want to communicate a context-sensitive and user-informative message when there is a failure in my use_salary procedure, I would  do something like this:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
   negative_salary EXCEPTION;
   too_large_salary EXCEPTION;
   too_small_salary EXCEPTION;
BEGIN
   CASE
      WHEN salary_in < 0 THEN RAISE negative_salary;
      WHEN salary_in > 10000 THEN RAISE too_large_salary;
      WHEN salary_in < 100 THEN RAISE too_small_salary;
      ELSE NULL;
   END CASE;

   /* Rest of procedure */

EXCEPTION
   WHEN negative_salary
   THEN
      notify_support (
         'Negative salary submitted ' || salary_in); 
      RAISE_APPLICATION_ERROR (-20001,
         'Negative salaries are not allowed. Please re-enter.');

   WHEN too_large_salary
   THEN
      notify_support (
         'Too large salary submitted ' || salary_in); 
      RAISE_APPLICATION_ERROR (-20002,
         'We are not nearly that generous. Please re-enter.');

   WHEN too_small_salary
   THEN
      notify_hr (
         'No one should be treated so shabbily! ' || salary_in);
      RAISE_APPLICATION_ERROR (-20003,
         'C''mon, a person''s gotta eat! Please re-enter.');
END;

And then when I execute the procedure and pass in -1, I will see:

ORA-20001: Negative salaries are not allowed. Please re-enter.

Some things to remember about RAISE_APPLICATION_ERROR:

  • You must provide an error code between -20999 and -20000.
  • If you do not pass TRUE for the third parameter, then the full error stack will not be kept, and only the most recent error message will be shown.
  • You can pass a string of up to 32767 characters to the built-in, but that string will be truncated to either 512 or 1899 bytes when calling SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK respectively.

To Conclude

Usually we leave it to the PL/SQL runtime engine to raise exceptions when an error occurs. Sometimes, however, you will need to raise your own exceptions. To do this, you can use RAISE or RAISE_APPLICATION_ERROR. The latter is especially helpful when you need to communicate an application-specific error message to your users.

Take advantage of PL/SQL's separate exception handling section to centralize as much of your error-related logic as possible. Keep your executable section clean and focused on executing the "positive" statements - what should be happening when there are not any errors.

Resources

You can run all of the above code via my LiveSQL script.

PL/SQL Documentation: Raising Exceptions Explicitly

YouTube Playlist: Practically Perfect PL/SQL Nine Good to Knows for Error Management

Wednesday, August 24, 2016

PL/SQL at Oracle Open World 2016

If you work with PL/SQL, you will not want to miss any of these sessions:

Sunday, Sep 18, 8:00 a.m. | Moscone West—2010
The Best Oracle Database 12c New Features for Developers and DBAs [UGF2028]

Alex Zaballa,  Senior Oracle Database Administrator,  Accenture Enkitec Group

This presentation looks at which new features of Oracle Database 12c are the most interesting to DBAs and developers. If you want to get up to speed with the most current release of Oracle Database, this session is for you. The session includes examples to show the functionality of the new features: • Multitenant • In-memory • SQL query row limits and offsets • Invisible columns • Approximate count distinct • PL/SQL from SQL • Session-level sequences • Session private statistics for GTT • Temporary undo • Multiple indexes on the same set of columns • SQL*Loader Express • Limit the PGA • Statistics during loads • Partial indexes for partitioned tables • Full database caching • Oracle Recovery Manager table recovery • Identity columns • In-database archiving

Sunday, Sep 18, 10:30 a.m. | Moscone West—3024
The Hidden Face of the Cost Based Optimizer: PL/SQL-Specific Statistics [UGF2781]

Michael Rosenblum,  Software Architect/Senior DBA,  Dulcian Inc

Database statistics are not limited to tables, columns, and indexes. PL/SQL functions also have a number of associated statistics, namely costs (CPU, I/O, network), selectivity, and cardinality (for functions that return collections). These statistics have default values that only somewhat represent reality. However, these values are always used by Oracle's cost-based optimizer to build execution plans. This session uses real-life examples to illustrate how properly managed PL/SQL statistics can significantly improve executions plans. It also demonstrates that Oracle's extensible optimizer is flexible enough to support packaged functions.

Sunday, Sep 18, 1:00 p.m. | Moscone South—302
A Holistic Approach to Database Security [UGF2210]

Robert Lockard,  President,  oraclewizard

This session features a discussion on defining the attack surface and what attack vectors are relevant to each node on the attack surface. Learn the concept of exploiting different nodes on the system, how attack vectors change for nodes, and mitigation changes for nodes. Discover the trusted path concept and how to apply mitigation to points along the trusted path to protect data. This session presents four things the DBA and developer can do now to secure the database environment. It also takes a piece of PL/SQL that is subject to SQL injection and moves through securing the code using Oracle Database 12cfeatures and separating the data from the application code.

Sunday, Sep 18, 3:30 p.m. | Moscone South—306
The Five Ways of Building Oracle Applications [UGF4313]

Sten Vesterli,  Principal,  More Than Code

This session examines all the most important development tools and technologies available from Oracle today, discussing the strong and weak points of Oracle Forms (the classical Oracle development tool, PL/SQL logic), Oracle Application Development Framework (declarative development with Oracle JDeveloper, Java business logic), Oracle Application Express (declarative development in web browser, PL/SQL business logic), JavaScript Extension Framework (brand-new JavaScript-based framework for building the user interface layer), and Oracle Mobile Application Framework (specialized Oracle Application Development Framework-like tool for mobile applications, generating native iOS, Android, and Windows applications). The presentation also includes recommendations for how to choose the right tool.

Sunday, Sep 18, 3:30 p.m. | Moscone South—310
Oracle Database 12c PL/SQL Basics and Tuning for DBAs [UGF1841]

Marcelle Kratochvil,  CIO / CTO,  Piction

It is tempting for DBAs to confine their knowledge set to database management and tuning while maintaining a belief that development of applications should be left only to developers. But this is a dangerous view; a DBA unskilled in PL/SQL is an ineffective one. This lack of skill helps foster a divisive culture likely resulting in poor development practices and security holes. This session is designed for the skilled DBA who has minimal knowledge on PL/SQL. It covers the basics of how PL/SQL is used, how to review code for security issues, and methods for efficiently tuning it.

Monday, Sep 19, 4:15 p.m. | Park Central—Metropolitan II
Querying Oracle Table from Hadoop/Hive and Querying Hadoop/HDFS from PL/SQL [CON6359]

Kuassi Mensah,  Director, Product Management,  Oracle
Nicholas Van Wyen,  Programmer / Developer,  Metasystems Technologies Inc.
This session explores the integration and offloading possibilities of an existing Oracle and Hadoop infrastructure, which are accessible today. It begins by describing Oracle Table Access for Hadoop, which turns Oracle Database tables into Hadoop data sources, allowing big data (facts) to be combined with master data (i.e., products, customers, in Oracle Database) using Hive SQL or Spark-SQL. Moving large data sets to Hadoop complicates applications, SQL queries, and PL/SQL. The session then explains how to move Terabytes of cold data over to Hadoop/Hive but continue to access them as native Oracle RDBMS objects that can be queried through your current applications without code changes.

Monday, Sep 19, 5:30 p.m. | Moscone South—302
What's New in PL/SQL and EBR in Oracle Database 12c Release 2 [CON6145]

Bryn Llewellyn,  Distinguished Product Manager, Database Division,  Oracle

Oracle Database 12c Release 2 brings important improvements to the PL/SQL language, the tools that support it, and edition-based redefinition. In this session learn how a PL/SQL static expression can be used where before a literal value was required, how built-in functions support the conversion of a PL/SQL variable to its JavaScript Object Notation representation and vice versa, and how a brand new code coverage tool helps developers prove that their tests are thorough. Also covered is how PL/Scope reports the identifiers used in static SQL and whether it selects, inserts, updates, or deletes; how one session can request that another connects to the GUI debugger; and how ad hoc SQL is allowed when halted at a breakpoint. Attendees also learn how the ease of use of EBR is improved.

Tuesday, Sep 20, 6:15 p.m. - 7:00 p.m. | Moscone South—307
Ask Tom Live with Chris Saxon and Connor McDonald [MTE7199]

Connor Mcdonald, Developer Advocate for SQL, Oracle
Chris Saxon, Developer Advocate for SQL, Oracle

When it comes to Oracle Database, the most popular and highly regarded source of advice is Ask Tom (asktom.oracle.com). For years, the legendary Tom Kyte answered any and all questions, and hosted vigorous discussions on all aspects of development on Oracle Database. These days, Chris Saxon and Connor McDonald of the Oracle Database Developer Advocates team carry on the tradition. So if you've got questions or want to hear what's it like to be on the AskTom team, or just hear about some of the strange and funny things we've been asked, don't miss this session.

Wednesday, Sep 21, 1:30 p.m. | Moscone South—303
Optimizing PL/SQL for Performance and Maintainability [CON6556]

Steven Feuerstein,  Oracle Database Developer Advocate for PL/SQL, Oracle,  Oracle
Bryn Llewellyn,  Distinguished Product Manager, Database Division,  Oracle
Connor Mcdonald,  Developer Advocate for SQL,  Oracle
Michael Rosenblum,  Software Architect/Senior DBA,  Dulcian Inc
Chris Saxon,  Developer Advocate for SQL,  Oracle

After fully leveraging SQL in your Oracle Database applications, it’s time to completely utilize PL/SQL to implement business rules, create APIs on top of your data structures, and implement key processes within the database. Usually when developers think about optimization, they focus on performance, and this session does, too. But it’s also critical to think about how to write code today, so that tomorrow and years from now, future developers will be able to adapt that code to changing user requirements and database features. Join the panel for an hour of thought-provoking discussion, plus lots of Q&A.

Thursday, Sep 22, 10:45 a.m. | Moscone South—103
Thinking Clearly About Database Application Architecture [CON6491]

Gerald Venzl,  Principal Product Manager,  Oracle
Bryn Llewellyn,  Distinguished Product Manager, Database Division,  Oracle
Connor Mcdonald,  Developer Advocate for SQL,  Oracle
Toon Koppelaars,  Consulting Member of Technical Staff, Real World Performance Group,  Oracle
Cary Millsap,  Infrastructure Principal Director,  Accenture Enkitec Group

The panelists strive to deserve their “Database Scientist” informal job titles by drawing their understanding of the correctness, security, and performance properties of applications that use Oracle Database from the data that proactive, empirical investigation yields. Like all scientists, they appreciate that future experiments might prompt the modification of current understanding. With this ordinary caveat, they confidently promote the understanding born of the current corpus of evidence: that the optimal database architecture starts with a carefully designed data model, manipulated by humanly composed SQL, encapsulated in PL/SQL. Come to hear them present their evidence. Challenge them with your own. They love a feisty debate.

Thursday, Sep 22, 9:30 a.m. | Park Central—Concordia
Database-Centric APIs on the Cloud Using PL/SQL and Node.js [CON3153]

Lucas Jellema,  Unspecified,  AMIS Services BV
Paco van der Linden,  Principal Consultant,  AMIS

APIs are crucial in today's IT landscape. Enabling REST services to read and manipulate data in formats easily accessible by a wide range of consumers is a key objective. This session shows how Oracle Database can play a key role in offering such APIs. Using a combination of PL/SQL packages (using rich SQL, JavaScript Object Notation, and user-defined types) and a Node.js application with the Node Oracle Database driver, it's quite straightforward. This combination runs on-premises and just as easily on the Oracle's database-as-a-service and platform-as-a-service solutions. Demos illustrate the development and deployment on the cloud of such APIs. Expect some advanced SQL and PL/SQL and a quick introduction into Node.js.

Thursday, Sep 22, 12:00 p.m. | Marriott Marquis—Salon 10/11
PL/SQL Programming for .NET Developers: Tips, Tricks, and Debugging [CON6612]

Christian Shay,  Product Manager - .NET and Windows Technologies,  Oracle

.NET and Oracle programmers frequently work with PL/SQL, whether that means setting up a call to a stored procedure from a .NET program, executing a PL/SQL anonymous block, or writing and debugging PL/SQL stored procedure code. This session looks at leveraging PL/SQL from the point of view of a .NET developer and provides in-depth tips about how to configure and use the tightly integrated PL/SQL debugger in Visual Studio.