Thursday, July 13, 2017

COUNT Method Works Like COUNT in SQL

You are writing PL/SQL code to provide secure, high performance access to your data and implement business rules. [reference: Why Use PL/SQL?]

Right? Good.

And you use collections (associative arrays, nested tables, arrays) because they offer all sorts of great functionality. [reference: Collections in PL/SQL YouTube playlist]

Right? Good.

So here's a quick reminder about COUNT, one of many methods available for collections (others include DELETE, FIRST, LAST, NEXT, PRIOR, TRIM, EXTEND):

It works pretty much like COUNT in SQL.

If the collection is empty, COUNT returns 0, not NULL.

If you try to "read" an element at an undefined index value, Oracle Database raises NO_DATA_FOUND. Just like a SELECT INTO that identifies no rows.

If you check to see if a collection is empty with a call to COUNT, it doesn't raise NO_DATA_FOUND.

To verify what I've said, and to have a bit of fun while doing it, you can take a quiz on this topic at the Oracle Dev Gym.


Thursday, July 6, 2017

Three tips for getting started right with Oracle Database development

By "Oracle Database development", I mean, more or less, writing SQL and PL/SQL. I assume in this post that you have access to Oracle Database (which you can get via Cloud services, Docker, GitHub and OTN).

A. Use a powerful IDE, designed with database programming in mind.

There are lots of editors out there, and many IDEs that work with Oracle Database. Sure, you could use Notepad, but OMG the productivity loss. You could also use a popular editor like Sublime, and then it get it working with Oracle.

I suggest, however, that you download and install Oracle's own own, free, powerful IDE: SQL Developer.

B. Enable compile-time warnings and PL/Scope.

The database has tons of useful functionality burned right into it, ready for you to use. For example, when PL/SQL program units are compiled, Oracle can give you feedback (aka, "compile-time warnings) to improve the quality and performance of your code.

In addition, PL/Scope - when enabled - will gather information about your identifiers and (in 12.2) SQL statements. This will allow you to do some very impressive impact analysis of your code.

Most developers are not aware of these features and so leave them turned off. Here's my suggestion for SQL Developer users:

Open up Preferences, type "compile" in the search field. Then change your settings to match these:


In other words:

1. Enable all warnings. 

This way, whenever you compile a program unit, Oracle will give you advice about ways to improve your code.

2. Treat all "severe" warnings as compile-time errors

If the PL/SQL team thinks these warnings are critical in some way, then I want to make my production code is free of such warnings. By setting this caregory to ERROR, I ensure that the code will not compile unless it is "clean". 

3. Tweak your optimization level up to 3 (all the good stuff plus subprogram inlining).

And even more important, take whatever steps are appropriate in your development environment to ensure that production code is compiled at this level of optimization as well. Check out this guidance from the PL/SQL dev team for more details.

4. Turn on PL/Scope.

You can then execute queries against your code to get information regarding naming conventions, sub-optimal code, and opportunities for performance improvements. 

Resources to help you with PL/Scope may be found on LiveSQL and GitHub.

C. Decide RIGHT NOW on logging and instrumentation.

Before you start writing you next program, accept this reality: your code will be full of bugs. You will need to trace execution as well as log those bugs, in order to get your code ready for production and then keep it running smoothly in production.

You need a logging utility for this, and I suggest you use the open-source, widely-used Logger utility available from GitHub.



Wednesday, July 5, 2017

What happens when a package fails to initialize? New behavior as of 12.1!

The best way to build applications on top of Oracle Database is to build lots of APIs (application programmatic interfaces) to your data with PL/SQL packages.

And that means you end up with lots of packages in your application. That's just great!

Now, when a user selects a feature of your application that in turn references an element in a package (invokes a procedure or function, or reads the value of a constant), that package must be instantiated and initialized for that user's session. As described in the documentation:
When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package. 
When Oracle Database instantiates a package, it initializes it. Initialization includes whichever of the following are applicable:
  • Assigning initial values to public constants
  • Assigning initial values to public variables whose declarations specify them
  • Executing the initialization part of the package body
Ah, but what happens when any of these steps fail? That, dear reader, is the focus of this post.

Suppose I have a procedure that raises an exception when executed:

CREATE OR REPLACE PROCEDURE always_fails
IS
BEGIN
   RAISE PROGRAM_ERROR;
END;
/

Then no matter how many times I try to run this procedure, it terminates with that same exception:

BEGIN
   always_fails;
END;
/

ORA-06501: PL/SQL: program error
ORA-06512: at "STEVEN.ALWAYS_FAILS", line 4

BEGIN
   always_fails;
END;
/

ORA-06501: PL/SQL: program error
ORA-06512: at "STEVEN.ALWAYS_FAILS", line 4

At which point you must now be saying: "Well, duh, Steven. Of course you are going to see the same exception each time you try to run the procedure."

Exactly. Just so. OK, now let's try it again, with the following package. When the package is initialized, it assigns (or tries to assign) a value of "Lu" to g_name. But that assignment fails, since "Lu" is too big to fit into a VARCHAR2(1) variable.

Thus, the PL/SQL engine raises the VALUE_ERROR exception (ORA-06502).

CREATE OR REPLACE PACKAGE valerr
IS
   FUNCTION little_name RETURN VARCHAR2;
END valerr;
/

Package compiled

CREATE OR REPLACE PACKAGE BODY valerr
IS
   g_name   VARCHAR2 (1) := 'Lu';

   FUNCTION little_name RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_name;
   END little_name;
BEGIN
   DBMS_OUTPUT.put_line ('Before I show you the name...');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'Trapped the error: ' || DBMS_UTILITY.format_error_stack ());
END valerr;
/

Package body compiled


So what happens when I try to execute the little_name function, after compiling the package?

I see an unhandled exception:

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name);
END;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
ORA-06512: at "VALERR", line 3

Before going any further, let's make sure you understand why the exception went unhandled. After all, the package body has a "catch-all" exception handler:

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'Trapped the error: ' || DBMS_UTILITY.format_error_stack ());
END valerr;

So why did the exception go unhandled? Because the error occurred in the "declaration section" of the package (not within the initialization section of the package or the executable section of a subprogram of the package). Exception sections only handle errors raised in the executable section of code (see my video for more details).

OK, so now we know:
  1. The package failed to finish initializing.
  2. An exception raised when assigning a default value to a package-level variable or constant cannot be handled within the package.
So if I try to execute this function again, I will see the same error, right? Well, maybe - depending on your version of Oracle Database. Prior to 12.1, you will see this:

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name);
END;
/

Name = 

No exception. Instead, the valerr.little_name function returns a NULL value. Huh?

Yes, I know. That seems counter-intuitive, but here's the thing: prior to Oracle Database 12c Release 1, even if a package failed to initialize, it would be marked as initialized in that session. And any variables or constants that had already successfully been assigned a value would have those values. Which can make it tough to track down the error.

But as of 12.1, when a package fails to initialize, then that package is marked as uninitialized. And any subsequent effort to use that package will grow the same exception. So in 12.1 (and 12.2 and 18.1 and....) you will see:

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name);
END;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small  
ORA-06512: at line 2

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name);
END;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small  
ORA-06512: at line 2

Conclusion

I expect you will all agree that the 12.1 behavior is preferred to the earlier "Oh, that package has a problem? Not to worry!" approach.

And ideally this change would not result in changed behavior for your application.

As in: hopefully, your testing is good enough so that you would have noticed a package initialization failure.

Finally, if you'd like to test your knowledge on this topic, try our Oracle Dev Gym quiz.

Friday, June 16, 2017

So you want to write a technical book?


I received this question today:
If I wanted to write a tech book, where/how would I start?
Rather than provide an individual answer, I thought I'd answer on my blog. Here goes.

First, how I answer this question for myself (the variation being: "Do you want to write another book?"):

No, don't do it.

:-)

I decided a few years ago that I would not write new books and instead keep my core set of books on PL/SQL up to date (for anyone who's wondering, that means essentially 3 out my 10 books on PL/SQL).

It takes a lot of time to write a book, any sort of book. And certainly with a technical book you need to be concerned about technical accuracy (slightly less critical with fiction :-) ).

In addition, people aren't buying books like they used to. Gee, thanks, Google (and people publishing ripped-off e-copies of books, and all the free content published on blogs and...).

So you definitely should not go into such a project thinking you are going to make much, if any, money on the book.

Some reasons to go ahead with such a project anyway:

  • You always wanted to publish a book, see your name listed as an author. 
  • You want to build your reputation in a given technology.
  • Along with (or through) that, you want to increase the revenue you can generate around that technology (speaking fees, hourly consulting rates).
Assuming you have decided to take the plunge, you need to:
  • Decide on a topic
  • Do lots of writing.
  • Find a publisher.
Mostly in the order. But I suggest that you do not write a whole book and then look for a publisher. That is likely necessary if you are writing a work of fiction. But with a technical book, it's a bit different.

Here's my suggestion, after you decide on a topic:

1. Come up with a table of contents for your book.

2. Start blogging about your topic. You don't even have to create your own blog. Publish on LinkedIn or Medium or any number of other channels.

Pick a chapter (maybe start at the beginning, maybe not) and do some writing. Publish it. See how people respond - to your writing, to the topic, etc.

If you get a strong response, then it is time to approach publishers. This where getting a technical book published can be so much easier than a work of fiction. 

You can offer your TOC, some samples of writing, and overall summary of a book, and from that alone, secure a contract with a publisher. 

I have a long, happy history with O'Reilly Media. But there are lots of technical publishers out there. And certainly an editor I very much respect and encourage you to seek out is Jonathan Gennick. I am sure he'd be happy to talk to you, and give you even more and better advice.

Friday, June 9, 2017

PL/Scope 12.2: Find all commits and rollbacks in your code

Yes, another post on PL/Scope, that awesome code analysis feature of PL/SQL (first added in 11., and then given a major upgrade in 12.2 with the analysis of SQL statements in PL/SQL code)!

A question on StackOverflow included this comment:
But there can be scenarios where it is difficult to identify where the ROLLBACK statement are executed in a complex PL SQL program (if you have to do only a modification to the existing code).
As of 12.2, it is super-duper easy to find all commits and rollbacks in your code.

Find all commits:

SELECT st.object_name,
       st.object_type,
       st.line,
       src.text
  FROM all_statements st, all_source src
 WHERE     st.TYPE = 'COMMIT'
       AND st.object_name = src.name
       AND st.owner = src.owner
       AND st.line = src.line
ORDER BY st.object_name,
         st.object_type   
/

Find all rollbacks:

SELECT st.object_name,
       st.object_type,
       st.line,
       src.text
  FROM all_statements st, all_source src
 WHERE     st.TYPE = 'ROLLBACK'
       AND st.object_name = src.name
       AND st.owner = src.owner
       AND st.line = src.line
ORDER BY st.object_name,
         st.object_type   
/

Reminder: these data dictionary views are populated only when your session or program unit has these settings enabled:

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

Friday, June 2, 2017

More 12.2 PL/Scope Magic: Find SQL statements that call user-defined functions

When a SQL statement executes a user-defined function, your users pay the price of a context switch, which can be expensive, especially if the function is called in the WHERE clause. Even worse, if that function itself contains a SQL statement, you can run into data consistency issues.

Fortunately, you can use PL/Scope in Oracle Database 12c Release 2 to find all the SQL statements in your PL/SQL code that call a user-defined function, and then analyze from there.

I go through the steps below. You can run and download all the code on LiveSQL.

First, I turn on the gathering of PL/Scope data in my session:

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

Then I create a table, two functions and a procedure, so I can demonstrate this great application of PL/Scope:

CREATE TABLE my_data (n NUMBER)
/

CREATE OR REPLACE FUNCTION my_function1
   RETURN NUMBER
   AUTHID DEFINER
IS
BEGIN
   RETURN 1;
END;
/

CREATE OR REPLACE FUNCTION my_function2
   RETURN NUMBER
   AUTHID DEFINER
IS
BEGIN
   RETURN 1;
END;
/

CREATE OR REPLACE PROCEDURE my_procedure (n_in IN NUMBER)
   AUTHID DEFINER
IS
   l_my_data   my_data%ROWTYPE;
BEGIN
   SELECT my_function1 ()
     INTO l_my_data
     FROM my_data
    WHERE     n = n_in
          AND my_function2 () = 0
          AND n = (SELECT my_function1 () FROM DUAL);

   SELECT COUNT (*)
     INTO l_my_data
     FROM my_data
    WHERE n = n_in;

   UPDATE my_data
      SET n = my_function2 ()
    WHERE n = n_in;
END;
/

Note that only two of the three DML statements in MY_PROCEDURE contain a function call (the first query and the update).

Now I UNION ALL rows from ALL_STATEMENTS and ALL_IDENTIFIERS to get a full picture:

WITH one_obj_name AS (SELECT 'MY_PROCEDURE' object_name FROM DUAL)
    SELECT plscope_type,
           usage_id,
           usage_context_id,
           LPAD (' ', 2 * (LEVEL - 1)) || usage || ' ' || name usages
      FROM (SELECT 'ID' plscope_type,
                   ai.object_name,
                   ai.usage usage,
                   ai.usage_id,
                   ai.usage_context_id,
                   ai.TYPE || ' ' || ai.name name
              FROM all_identifiers ai, one_obj_name
             WHERE ai.object_name = one_obj_name.object_name
            UNION ALL
            SELECT 'ST',
                   st.object_name,
                   st.TYPE,
                   st.usage_id,
                   st.usage_context_id,
                   'STATEMENT'
              FROM all_statements st, one_obj_name
             WHERE st.object_name = one_obj_name.object_name)
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
/

And I see these results:

PLSCOPE_TYPE    USAGE_ID    USAGE_CONTEXT_ID    USAGES
ID    1    0    DECLARATION PROCEDURE MY_PROCEDURE
ID    2    1      DEFINITION PROCEDURE MY_PROCEDURE
ID    3    2        DECLARATION FORMAL IN N_IN
ID    4    3          REFERENCE NUMBER DATATYPE NUMBER
ID    5    2        DECLARATION VARIABLE L_MY_DATA
ID    6    5          REFERENCE TABLE MY_DATA
ST    7    2        SELECT STATEMENT
ID    8    7          REFERENCE TABLE MY_DATA
ID    9    7          REFERENCE COLUMN N
ID    10    7          REFERENCE FORMAL IN N_IN
ID    11    7          REFERENCE COLUMN N
ID    13    7          CALL FUNCTION MY_FUNCTION1
ID    14    7          CALL FUNCTION MY_FUNCTION2
ID    15    7          ASSIGNMENT VARIABLE L_MY_DATA
ID    16    15            CALL FUNCTION MY_FUNCTION1
ST    17    2        SELECT STATEMENT
ID    18    17          REFERENCE TABLE MY_DATA
ID    19    17          REFERENCE FORMAL IN N_IN
ID    20    17          REFERENCE COLUMN N
ID    21    17          ASSIGNMENT VARIABLE L_MY_DATA
ST    22    2        UPDATE STATEMENT
ID    23    22          REFERENCE TABLE MY_DATA
ID    24    22          REFERENCE FORMAL IN N_IN
ID    25    22          REFERENCE COLUMN N
ID    26    22          REFERENCE COLUMN N
ID    27    22          CALL FUNCTION MY_FUNCTION2


OK. Now let's get to the substance of this blog post. I use subquery refactoring (WITH clause) to create and then use some data sets: my_prog_unit - specify the program unit of interest just once; full_set - the full set of statements and identifiers; dml_statements - the SQL DML statements in the program unit. Then I find all the DML statements whose full_set tree below it contain a call to a function.

WITH my_prog_unit AS (SELECT USER owner, 'MY_PROCEDURE' object_name FROM DUAL),
     full_set
     AS (SELECT ai.usage,
                ai.usage_id,
                ai.usage_context_id,
                ai.TYPE,
                ai.name
           FROM all_identifiers ai, my_prog_unit
          WHERE ai.object_name = my_prog_unit.object_name
            AND ai.owner = my_prog_unit.owner
         UNION ALL
         SELECT st.TYPE,
                st.usage_id,
                st.usage_context_id,
                'type',
                'name'
           FROM all_statements st, my_prog_unit
          WHERE st.object_name = my_prog_unit.object_name
            AND st.owner = my_prog_unit.owner),
     dml_statements
     AS (SELECT st.owner, st.object_name, st.line, st.usage_id, st.type
           FROM all_statements st, my_prog_unit
          WHERE     st.object_name = my_prog_unit.object_name
                AND st.owner = my_prog_unit.owner
                AND st.TYPE IN ('SELECT', 'UPDATE', 'DELETE'))
SELECT st.owner,
       st.object_name,
       st.line,
       st.TYPE,
       s.text
  FROM dml_statements st, all_source s
 WHERE     ('CALL', 'FUNCTION') IN (    SELECT fs.usage, fs.TYPE
                                          FROM full_set fs
                                    CONNECT BY PRIOR fs.usage_id =
                                                  fs.usage_context_id
                                    START WITH fs.usage_id = st.usage_id)
       AND st.line = s.line
       AND st.object_name = s.name
       AND st.owner = s.owner
/

And I see these results:

STEVEN    MY_PROCEDURE    6    SELECT       SELECT my_function1 ()
STEVEN    MY_PROCEDURE    18    UPDATE"   UPDATE my_data

Is that cool or what?

Tuesday, May 9, 2017

Use records to improve readability and flexibility of your code

Suppose I've created a table to keep track of hominids:

CREATE TABLE hominids
(
   hominid_name     VARCHAR2 (100),
   home_territory   VARCHAR2 (100),
   brain_size_cm    INTEGER
)
/

I might then write code like this:

DECLARE
   l_b_hominid_name    VARCHAR2 (100) := 'Bonobo';
   l_b_brain_size_cm   INTEGER := 500;
   l_g_hominid_name    VARCHAR2 (100) := 'Gorilla';
   l_g_brain_size_cm   INTEGER := 750;
   l_n_hominid_name    VARCHAR2 (100) := 'Neanderthal';
   l_n_brain_size_cm   INTEGER := 1800;

What do you think?

I find the little voice of Relational Theory inside my head rebelling.

"All that repetition! All that denormalization! All that typing (or copy-pasting, which is even worse)!"

Surely if I should avoid having redundant data in rows of my tables, I should avoid redundant code, too?

Yes, I should.  I don't like to see long lists of declarations, especially when the names are very similar and follow a pattern. 

A fine way to avoid this kind of code is to use record types to group related variables together within a named context: the record variable. So I could rewrite the declaration section above to:


DECLARE
   l_bonobo        hominids%ROWTYPE;
   l_gorilla       hominids%ROWTYPE;
   l_neanderthal   hominids%ROWTYPE;
BEGIN
   l_bonobo.hominid_name := 'Bonobo';
   l_bonobo.brain_size_cm := 500;
   l_gorilla.hominid_name := 'Gorilla';
   l_gorilla.brain_size_cm := 750;
   l_neanderthal.hominid_name := 'Neanderthal';
   l_neanderthal.brain_size_cm := 1800;

Notice that I now move the initializations of the variable (well, record.field) values to the executable section. That's because PL/SQL does not yet offer a built-in function (in object-oriented lingo, a constructor method) for record types.

So I no longer have six declarations - just three. And, of course, if my table had 15 columns and I had declared a separate variable for each of those, I would have been able to shrink down my declarations from 45 to 3!

Still, I don't like putting all that initialization code in the main body of my block. How about if I create my own "record constructor" function, and then call that:

CREATE OR REPLACE FUNCTION new_hominid (
   name_in IN hominids.hominid_name%TYPE,
   home_territory_in IN hominids.home_territory%TYPE,
   brain_size_cm_in IN hominids.brain_size_cm%TYPE)
   RETURN hominids%ROWTYPE
IS
   l_return hominids%ROWTYPE;
BEGIN
   l_return.hominid_name := name_in;
   l_return.home_territory := home_territory_in;
   l_return.brain_size_cm := brain_size_cm_in;
   RETURN l_return;
END;
/

DECLARE
   l_bonobo        hominids%ROWTYPE := new_hominid ('Bonobo', NULL, 500);
   l_gorilla       hominids%ROWTYPE := new_hominid ('Gorilla', NULL, 750);
   l_neanderthal   hominids%ROWTYPE := new_hominid ('Neanderthal', NULL, 1800);
BEGIN
   DBMS_OUTPUT.put_line (l_neanderthal.brain_size_cm);
END;
/

Ahhhhh. Just three declarations. Default values assigned in the declaration section. All the details of the assignments hidden away behind the function header.

And when I add a new column to the table (or generally a field to a record), I can add a parameter to my new_hominid function, along with a default value of NULL, and none of my existing code needs to change (unless that new column or field is needed).

Yes, I like that better.

How about you?