Monday, September 18, 2017

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective of maintaibilty and readability of code.

Here's an example:

DECLARE
   SUBTYPE currency_t is NUMBER (10,2);
   l_salary currency_t;
BEGIN
   l_salary := 10.50607;
   dbms_output.put_line (l_salary);
END;

10.51

You can, however, certainly define your own composite types. The phrase "user-defined types" often refers to object-oriented structures, but as you will see below, you the user can define your own types of:
  • Records (kinda like a row in a table, though usually consisting of a subset of the columns of a row)
  • Collections (similar to arrays in other programming languages)
  • Object types (comparable to classes in object-oriented languages)
User-Defined Record Types

You can very easily define records based on existing structures, such as a table or cursor. Sometimes, though, you will want to define your own record type to group together related elements or return multiple pieces of information from a function.

You can do this by defining your own record. Here's an example of a record type used as the return type of a function:

CREATE OR REPLACE PACKAGE comp_pkg
   AUTHID DEFINER
IS
   TYPE compensation_rt IS RECORD
   (
      salcomp        employees.salary%TYPE,
      total_salary   NUMBER
   );

   FUNCTION comp_info (emp_in IN employees.employee_id%TYPE)
      RETURN compensation_rt;
END;
/

DECLARE
   l_comp   comp_pkg.compensation_rt;
BEGIN
   l_comp := comp_pkg.comp_info (7499);
END;
/

And here's an example of nesting one record type inside another. Normalizing my runtime data structures!

DECLARE
   TYPE phone_rectype IS RECORD
   (
      area_code    PLS_INTEGER,
      exchange     PLS_INTEGER,
      phn_number   PLS_INTEGER,
      extension    PLS_INTEGER
   );

   TYPE contact_rectype IS RECORD
   (
      day_phone#    phone_rectype,
      eve_phone#    phone_rectype,
      cell_phone#   phone_rectype
   );

   l_sales_rep   contact_rectype;
BEGIN
   /* Set the day phone # */
   l_sales_rep.day_phone#.area_code := 773;
   l_sales_rep.day_phone#.exchange := 426;
   l_sales_rep.day_phone#.phn_number := 9093;
   l_sales_rep.day_phone#.extension := NULL;

   /* Copy day phone to evening phone */
   l_sales_rep.eve_phone# := l_sales_rep.day_phone#;

   /* "Override" just phn_number field. */
   l_sales_rep.eve_phone#.phn_number := 2056;
END;

Since we can define records based on a table, view or cursor using the %ROWTYPE attribute, user-defined records do not proliferate in PL/SQL code.

When it comes to collections, though, we almost always declare our own types.

User-Defined Collection Types

Collections are the analogue of arrays in PL/SQL. There are three types of collections: associative arrays, nested tables, and arrays.

Collections come in very handy when you need temporary datasets in your program and do not want or need to rely on global temporary tables, SQL and the context switches between the PL/SQL and SQL engines. Collections are also the enabling technology for table functions, which are functions that are invoked in the FROM clause of a SELECT statement.

You can certainly take advantage of a variety of pre-defined collection types. In the following code fragment, for example, I grab the topics and difficulty levels of a set of Oracle Dev Gym workouts:

   l_topics         DBMS_SQL.number_table;
   l_difficulties   DBMS_SQL.number_table;
BEGIN
     SELECT topic_id, difficulty_id
       BULK COLLECT INTO l_topics, l_difficulties
       FROM dg_workouts
      WHERE goal_id = goal_id_in
   ORDER BY week_number;

The IDs are all integers, so why not use the DBMS_SQL collection? Actually, I suggest you do not do this. That's right. Don't do what I do. Do what I say. Why?

Because this is an example of "bad lazy." When someone comes along later to maintain my code, they will see my use of a DBMS_SQL element and wonder: "Where's the dynamic SQL?"

Our code should answer, not raise, questions. So what I should do is have my own table of numbers and use that. Funnily enough, I do. So I am going to fix my code right now to look this like:

   l_topics         qdb_numbers_nt;
   l_difficulties   qdb_numbers_nt;
BEGIN
     SELECT topic_id, difficulty_id
       BULK COLLECT INTO l_topics, l_difficulties
       FROM dg_workouts
      WHERE goal_id = goal_id_in
   ORDER BY week_number;

Ah....much better!

User-defined record types and collection types are often used together.

For example, in the code underlying the Oracle Dev Gym, the qdb_rankings package defines both a record type and collection type of those records:

/* The record type */

TYPE user_info_rt IS RECORD
(
   comp_event_id   INTEGER,
   total_seconds   INTEGER
);

/* Collection type; each element contains one of those records */
TYPE user_info_tt IS TABLE OF user_info_rt
   INDEX BY PLS_INTEGER;

/* A variable of that collection type */

l_user_timings  user_info_tt;

....

/* Now I populate the collection of records with a BULK COLLECT query */

SELECT comp_event_id, total_seconds
  BULK COLLECT INTO l_user_timings
  FROM mv_qdb_compev_answers eva
 WHERE     eva.competition_id = competition_id_in
       AND TRUNC (eva.start_date) BETWEEN start_in AND end_in
       AND eva.user_id = rec.user_id
       AND eva.pct_correct_answers = 100;

Collections are a lot of fun and very handy. Check out my Practically Perfect PL/SQL YouTube channel for hours of instruction on collections. And the PL/SQL doc offers extensive guidance on collections, as well.

User-Defined Object Types

Way back in Oracle8, object-oriented capabilities were added to Oracle Database, implemented in PL/SQL syntax. Instead of classes, they are called object types. And now, a number of big releases later, these object types support most of the features developers have come to expect from object-oriented languages including inheritance and dynamic polymorphism.

Here, for example, is a hierarchy of types to manage one of my all-time favorites things in the world: food.

CREATE TYPE food_t AS OBJECT
(
   name VARCHAR2 (100),
   food_group VARCHAR2 (100),
   grown_in VARCHAR2 (100),
   /* Generic foods cannot have a price, but we can
      insist that all subtypes DO implement a price
      function. */
   NOT INSTANTIABLE MEMBER FUNCTION price RETURN NUMBER
)
   NOT FINAL NOT INSTANTIABLE;
/

CREATE TYPE dessert_t UNDER food_t (
      contains_chocolate CHAR (1),
      year_created NUMBER (4),
      OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   )
   NOT FINAL;
/

CREATE OR REPLACE TYPE BODY dessert_t
IS
   OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   IS
      multiplier   NUMBER := 1;
   BEGIN
      DBMS_OUTPUT.put_line ('Dessert price!');

      IF self.contains_chocolate = 'Y'
      THEN
         multiplier := 2;
      END IF;

      IF self.year_created < 1900
      THEN
         multiplier := multiplier + 0.5;
      END IF;

      RETURN (10.00 * multiplier);
   END;
END;
/

CREATE TYPE cake_t UNDER dessert_t (
      diameter NUMBER,
      inscription VARCHAR2 (200),
      /* Inscription and diameter determine the price */
      OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   );
/

CREATE OR REPLACE TYPE BODY cake_t
IS
   OVERRIDING MEMBER FUNCTION price
      RETURN NUMBER
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Cake price!');
      RETURN (5.00 + 0.25 * (LENGTH (self.inscription)) + 0.50 * diameter);
   END;
END;
/

DECLARE
   my_favorite_vegetable   food_t
      := food_t ('Brussel Sprouts', 'VEGETABLE', 'farm');
BEGIN
   p.l (my_favorite_vegetable.price);
END;
/

DECLARE
   last_resort_dessert   dessert_t
                            := dessert_t ('Jello',
                                          'PROTEIN',
                                          'bowl',
                                          'N',
                                          1887);
   heavenly_cake         cake_t
                            := cake_t ('Marzepan Delight',
                                       'CARBOHYDRATE',
                                       'bakery',
                                       'N',
                                       1634,
                                       8,
                                       'Happy Birthday!');
BEGIN
   DBMS_OUTPUT.put_line (last_resort_dessert.price);
   DBMS_OUTPUT.put_line (heavenly_cake.price);
END;
/

/* Demonstration of dynamic polymorphism */

DECLARE
   TYPE foodstuffs_nt IS TABLE OF food_t;

   fridge_contents   foodstuffs_nt
                        := foodstuffs_nt (dessert_t ('Strawberries and cream',
                                                     'FRUIT',
                                                     'Backyard',
                                                     'N',
                                                     2001),
                                          cake_t ('Chocolate Supreme',
                                                  'CARBOHYDATE',
                                                  'Kitchen',
                                                  'Y',
                                                  2001,
                                                  8,
                                                  'Happy Birthday, Veva'));
BEGIN
   FOR indx IN fridge_contents.FIRST .. fridge_contents.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            'Price of '
         || fridge_contents (indx).name
         || ' = '
         || fridge_contents (indx).price);
   END LOOP;
END;
/

As you can see from the above example, Oracle Database supports substitutability with object types as well. The easiest way to understand this concept is:

Every cake is a dessert; every dessert is a food.
But of course not every food is a dessert, nor is every dessert a cake.

Take a look at that example of dynamic polymorphism. I declare a nested table of food_t, but I have no problem inserting a dessert and cake into the collection.

The same is true for relational tables. In the code below, I create a table whose single column is of type food_t. I then insert two rows into the table (a dessert and cake).

CREATE TABLE food_tab (food food_t)
/

DECLARE
   s_and_c    dessert_t
                 := dessert_t ('Strawberries and cream',
                               'FRUIT',
                               'Backyard',
                               'N',
                               2001);
   choc_sup   cake_t
                 := cake_t ('Chocolate Supreme',
                            'CARBOHYDATE',
                            'Kitchen',
                            'Y',
                            2001,
                            8,
                            'Happy Birthday, Veva');
BEGIN
   INSERT INTO food_tab
        VALUES (s_and_c);

   INSERT INTO food_tab
        VALUES (choc_sup);
END;
/

SELECT COUNT (*) FROM food_tab
/

2

I have heard from several developers who have used object types to implement applications within a comprehensive object-oriented framework. But it is rare. Most Oracle Database developers rely on the relational model of SQL and the procedural structure of PL/SQL to build their application backends.

There is, as I am sure you can imagine, lots more to learn about object types. If this approach intrigues you, check out the very useful Database Object-Relational Developer's Guide.

Well, there's your introduction to the different kinds of user-defined types in Oracle Database and PL/SQL. Did I leave anything important out? Let me know!

And here's a LiveSQL script that demonstrates all the object type-related functionality shown above.

Monday, September 11, 2017

Surgical strike on spaghetti code with CONTINUE statement

It can be incredibly painful to make changes to an existing program that has the markings of spaghetti code. And isn't it positively terrifying to make those changes when you don't have a regression test for the program that you can run afterward to ensure that no bugs were inadvertently introduced?

Yet that is what we are often called upon to do.

When faced with this situation, the smart thing to do is to make the smallest, most isolated change possible, thereby minimizing the ripple effect.

Suppose the code you have to modify looks like this:

PROCEDURE someone_elses_mess
/*
|| Author: Long-Gone Consultant 
|| Maintained by: Terrified Employee
*/
IS
BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here 

      ... more of the same here    
     
   END LOOP;
END;

and you need to add some code between "hard-to-understand logic here" and "more of the same here." If a certain condition is met, you want to execute some new code and then skip over the rest of the loop body and move on to the next iteration.

You can accomplish this in a few ways:
  • Adding an IF statement
  • Using a GOTO statement
  • Using a CONTINUE statement
Here's what the loop body might look like with an IF statement:

BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here

      IF new_condition
      THEN
         ... new code here
      ELSE
         ... more of the same here
      END IF;
   END LOOP;
END;

Here's the approach with GOTO:

BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here

      IF new_condition
      THEN
         ... new code here

         GOTO end_of_loop;
      END IF;

      ... more of the same here
      <<end_of_loop>>
      NULL; -- Placeholder
   END LOOP;
END;

And, finally, here's the approach with CONTINUE (new to Oracle Database 11g):

BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here

      IF new_condition
      THEN
         ... new code here

         CONTINUE;
      END IF;

      ... more of the same here

   END LOOP;
END;

Now, with code this simple, all three of these approaches look reasonable and get the job done. But if you are dealing with an extremely complex, convoluted program, the IF statement gets tricky. You have to make sure you set up the ELSE clause properly and enclose the correct logic. Which means that you have to find the END LOOP statement for this loop, which could be hundreds of lines later in the program, with many other END LOOPs in between.

The GOTO allows you to simply branch to the end of the loop, but, again, you must find the end of that loop and then add both the label and the placeholder "NULL;" statement so that the GOTO has someplace executable to go.

No, the best solution to this problem is to simply tell the PL/SQL runtime that you want to continue with the loop execution, skipping the rest of the body for this iteration. Clean, simple, and declarative.

And, by the way, just as with the EXIT statement, you can use CONTINUE in a WHEN clause and also specify an END label (helpful with nested loops).

Here is an example of CONTINUE WHEN:

BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here

      /* I want to execute this new logic and then "escape." */
      ... new code here

      CONTINUE WHEN new_condition;

      ... more of the same here

   END LOOP;
END;

The following block shows how you can skip not only the rest of the inner loop but also the outer loop by specifying a label with CONTINUE:

BEGIN
   <<outer_loop >>
   FOR o_index IN 1 .. my_collection.COUNT
   LOOP
      <<inner_loop>>
      FOR i_index
            IN your_collection.FIRST ..
                your_collection.LAST
      LOOP
         ... lots of code

         /* Skip the rest of this and the outer loop if condition is met. */
         CONTINUE outer_loop WHEN condition_is_met;

         ... more inner loop logic

      END LOOP inner_loop;

      ... more outer loop logic

   END LOOP outer_loop;
END;

So: you like CONTINUE, right? Nice, clear, easy-to-understand syntax and code structure. You'll start using it, right? Right!

In that case, the following resources may be of assistance:

The Oracle Dev Gym offers a number of quizzes on CONTINUE. Just head to the home page and search on "CONTINUE".

And here's a LiveSQL script that demonstrates CONTINUE, comparing it to using GOTOs and exceptions to achieve the same result (please don't!).

Note: Much of this content originally appeared in Oracle Magazine.

Monday, August 28, 2017

About the Date Literal in Oracle Database

I offered up a past blog post on Twitter today:



And I saw this reply:
I am unfamiliar with this magical expression: DATE '2016-10-31'. How is this being resolved when it doesn't match the NLS date format?
Which reminded me than many developers are not aware of the date Literal feature of both SQL andPL/SQL. So I figured I should give you all a bit more detail on the topic.

So you are likely familiar with string literals, like 'ABC' and q'[don't need two single quotes]'.  And who isn't aware of using pretty much all the time number literals, like 123 and 2e7?

Relatively few developers know that you can have a date literal, too (and timestamp literal and timestamp with local timezone literal and interval literals).

Literals of these types generally have the form:

TYPE string-literal

where TYPE is the name of the datatype and string-literal is, well, you get the idea. :-)

Here are some examples:

1. Date literal


DATE '2017-08-26'

2. Timestamp literal

TIMESTAMP '2017-08-26 09:26:50.124'

2. Interval literal

INTERVAL '100-4' YEAR(3) TO MONTH

So why does the date literal "work" even if it doesn't match the default date format for my session?

Because the date literal is defined as part of the ANSI SQL standard. It contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD'). Sorry, that's just how it is.

Now that you know about the date literal, you can test your knowledge with a Dev Gym quiz (just search for "date literal").

You can also play around with it easily using this LiveSQL script.

Wednesday, August 23, 2017

Do you REALLY need that SQL to be dynamic?


Dynamic SQL means a SQL statement that is constructed, parsed and executed "dynamically" at run time (vs. "statically" at compile time).

It's very easy to write static SQL in PL/SQL program units (one of the great joys of working with this database programming language). It's also quite easy to implement dynamic SQL requirements in PL/SQL.

But that doesn't mean you should. The bottom line regarding dynamic SQL is:
Construct and execute SQL at runtime only when you have to.
There are several good reasons to avoid unnecessary dynamic SQL:
  1. Security: dynamic SQL opens up the door to SQL injection, which can lead to data corruption and the leaking of sensitive data.
  2. Performance: while the overhead of executing dynamic SQL has gone way down over the years, it is certainly still faster to use static SQL.
  3. Maintainability: the code you write to support dynamic SQL is more - literally more code - and harder to understand and maintain.
Sometimes the misuse of dynamic SQL is obvious. Consider the following:

CREATE OR REPLACE FUNCTION name_from_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   the_table.the_name%TYPE;
BEGIN
   EXECUTE IMMEDIATE 'select the_name from the_table where id = ' || id_in
      INTO l_the_name;

   RETURN l_the_name;
END;
/

The developer apparently believed, however, that since the value of the ID can change, it needs to be concatenated to the SQL statement, so it's gotta be done dynamically. Ha! There's nothing dynamic about this query. It should be rewritten to:

CREATE OR REPLACE FUNCTION name_from_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   the_table.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM the_table
    WHERE id = id_in;

   RETURN l_the_name;
END;
/

Often, the need for dynamic SQL is compelling at first, but then disappears with a little bit of analysis. Consider this function:

CREATE OR REPLACE FUNCTION name_from_id (table_in   IN VARCHAR2,
                                         id_in      IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   VARCHAR2 (32767);
BEGIN
   EXECUTE IMMEDIATE
      'select the_name from ' || table_in || ' where id = ' || id_in
      INTO l_the_name;

   RETURN l_the_name;
END;
/

Well, heck, if I don't know the table name till run time, I sure can't use a static SELECT statement, right? Of course, right!

So, first of all, if this code is really and truly necessary, you need to think about SQL injection.

1. Is the table name provided directly by the user? You should never allow user input to be stuffed directly into your dynamic statement. They could enter all sorts of nasty stuff.

2. Even if not passed directly from user fingertips to EXECUTE IMMEDIATE, you should use DBMS_ASSERT to make sure that the table name passed in is a valid DB object, as in:

CREATE OR REPLACE FUNCTION name_from_id (table_in   IN VARCHAR2,
                                         id_in      IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   VARCHAR2 (32767);
BEGIN
   EXECUTE IMMEDIATE
      'select the_name from ' || 
         SYS.DBMS_ASSERT.sql_object_name (table_in) || 
      ' where id = :id'
      INTO l_the_name
      USING id_in;

   RETURN l_the_name;
END;
/

Great, so the function is less vulnerable than before to injection. But does it really need to be dynamic?

The only way to answer that question is to check and see where and how the function is used. I could do a text search through my code (via an editor like Sublime) or an object search (in SQL Developer). I could also use PL/Scope if I'd gathered identifier information across my code base.

Suppose after doing my analysis, I find that the function is called twice as follows:

l_name := name_from_id (table_in => 'TABLE1', id_in => l_id);

l_recent_name := name_from_id (table_in => 'TABLE2', id_in => l_most_recent_id);

OK, I could shrug and say "Yep, two different table names. I need to use dynamic SQL."

But that would be a mistake. What I should think and say is:

"What? Just two different tables? I don't need dynamic SQL for that. That's just laziness."

Instead I could do either of the following:
  1. Create two different functions.
  2. Change to static SQL inside the function.
Two Different Functions

Really, why not? It's so easy and fast to write PL/SQL functions that call SQL. Just change to:

CREATE OR REPLACE FUNCTION name_from_table1_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name table1.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM table1
    WHERE id = id_in;

   RETURN l_the_name;
END;
/

CREATE OR REPLACE FUNCTION name_from_table2_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name table2.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM table2
    WHERE id = id_in;

   RETURN l_the_name;
END;
/

One Function, No Dynamic SQL

So you don't want two, three many functions for the "same" functionality (get name for ID). Fine, keep them all in one subprogram, but move the conditional logic inside.

CREATE OR REPLACE FUNCTION name_from_id (table_in   IN VARCHAR2,
                                         id_in      IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   VARCHAR2 (32767);
BEGIN
   CASE table_in
      WHEN 'TABLE1'
      THEN
         SELECT the_name
           INTO l_the_name
           FROM table1
          WHERE id = id_in;
      WHEN 'TABLE2'
      THEN
         SELECT the_name
           INTO l_the_name
           FROM table2
          WHERE id = id_in;
      ELSE
         raise_application_error (
            -20000,
            'name_from_id does not support fetching from ' || table_in);
   END CASE;

   RETURN l_the_name;
END;
/

Sure, sometimes you really do need to use EXECUTE IMMEDIATE and dynamic SQL. In those situations, make sure you minimize the attack surfaces for SQL injection. Make sure you've got strong exception handling and logging.

But do make certain that this extra "technical debt" is necessary. There's a good chance you can get by without dynamic SQL. If so, your users, your fellow developers and your manager will all thank you!

Thursday, August 17, 2017

Referencing package-level variables inside the package body

I received this question yesterday:
Is there a shortcut for referencing package variables in the package body? In Java, as an example, you can use the "this" keyword as a reference to the current object. This came about as I needed to create a copy of a package for debug purposes and realized I had to rename all the references to the package name within the package body.
Before I answer, let's look at an example of what Tony is talking about. I create a package specification and then a body with package-level variable (declared outside any subprogram of the package):

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE proc (n_in IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
   BEGIN
      IF n_in < pkg.smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

Notice the line in blue and bold. I reference the package level variable, qualified with the package name (the approach that Tony has taken).

But then when Tony changes the name of the package, so he also has to change the dot-qualified references within the package.

Can you use "this" syntax, as is available in Java? No. You will see this error when trying to compile the package body:

PLS-00201: identifier 'THIS.SMALLEST' must be declared

"this"-style syntax is used only with object types in Oracle Database (our object-oriented extensions to the relational model), in which case you use "SELF" to reference the current object type instance.

So what should Tony do? Here are his (and mine, and your) options:
  • Continue to dot qualify with the package name, and do a (careful, very careful) global search and replace of the old package name to new. Like I say: CAREFUL.
  • Remove the package name. You don't need it.
That's right. You don't need to qualify the reference to this variable. In other words, this version of he package body will compile just fine:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
   BEGIN
      IF n_in < smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

The compiler will try to resolve the reference to smallest inside proc. When that doesn't work, it will check the "next level up" in scope: the package body. Ah ha! There it is. And the body compiles.

Of course, this will not work as desired if you declare a variable or constant with the same name inside proc. The following version will also compile, but that reference to smaller inside proc will not refer to the package level variable.

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      smallest NUMBER;
   BEGIN
      IF n_in < smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

In this case, you must qualify the variable with the scope you mean. You could qualify with the subprogram (procedure) or package name, by the way:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      smallest NUMBER;
   BEGIN
      IF n_in < proc.smallest -- references procedure's smallest
      THEN

      IF n_in < pkg.smallest -- references package level smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

Of course, best of all is to avoid declaring variables with the same names at different scopes. That can be confusing - and then requires the use of dot-qualification. Another approach, frequently used, is to use different naming conventions for variables of different scope. For example, I commonly use "g_" to indicate a global variable in a package, and "l_" for local variables.

So my version of the package body would look like:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   g_smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      l_smallest NUMBER;
   BEGIN
      IF n_in < l_smallest -- references procedure's smallest
      THEN

      IF n_in < g_smallest -- references package level smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

The most important thing is to be consistent in your approach so others (including the Future You) can more easily understand your code.

A final comment regarding dot-qualifying variable names: when you reference PL/SQL variables and constants inside SQL statements in your PL/SQL blocks, you should always qualify them with their scope names. Example:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   g_smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      l_salary NUMBER;
   BEGIN
      SELECT salary INTO l_salary
        FROM employees e
       WHERE e.employee_id = proc.n_in;
   END;
END;
/

By doing this, you give more information to the compiler that it can use to reduce the times when a program unit needs to be invalidated and recompiled. In the above package body, if I did not qualify my reference to n_in inside the SELECT, adding a column named "n_in" will cause the package body to be set to INVALID.

And upon recompilation, well, that reference to "n_in" will now be to the column and not your variable. Not good. By fully qualifying the reference, however, the compiler knows (via fine-grained dependency management, added in 11.1) that there can be no confusion, and adding a column to the employees table could not possibly disrupt the current behavior of the package body.

Thursday, August 10, 2017

No subqueries allowed in materialized view? No problem!

Have you ever run into the following error when trying to create a materialized view?

ORA-22818: subquery expressions not allowed here

Yes, it is true: you cannot have a scalar subquery in the SQL statement used to create your materialized view. Here's an example of what won't work (note: I am not claiming this query makes any sense):

CREATE MATERIALIZED VIEW hr_demo_mv
AS
     SELECT employee_id,
            (SELECT MAX (hire_date)
               FROM employees ce) maxhd
       FROM employees t
/
ORA-22818: subquery expressions not allowed here
22818. 00000 -  "subquery expressions not allowed here"
*Cause:    An attempt was made to use a subquery expression where these
           are not supported.
*Action:   Rewrite the statement without the subquery expression.

Rewrite my query without the subquery expression? But I just spent an hour putting it all together. Works great. Gives me exactly the results I want and need. Rewrite it? ARGH.

Calm yourself. While it is true that you will need to "rewrite the statement" that you provide in your CREATE MATERIALIZED VIEW statement, you will not have to abandon your subqueries and all your hard work.

All you have to do is create a view with the subqueries, and then create your materialized view based on the view:

CREATE VIEW hr_demo_v
AS
     SELECT employee_id,
            (SELECT MAX (hire_date)
               FROM employees ce) maxhd
       FROM employees t
/

View HR_DEMO_V created.

CREATE MATERIALIZED VIEW hr_demo_mv
AS
     SELECT * FROM hr_demo_v
/

Materialized view HR_DEMO_MV created.

I recommend this approach (the materialized view is "nothing more" than a select from a view), even if your materialized view query does not contain a subquery or anything else that would preclude the materialized view from being created.

By taking this approach, you can change the contents of the materialized view with the next refresh by doing nothing more than changing the query (instead of dropping and re-creating the materialized view).

Tuesday, August 8, 2017

Tightening security in your PL/SQL code with 12c new features, part 2

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units. These features include:
  • Avoid privilege escalation: Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit. I covered this topic here.
  • Code-based access control: fine-tune access to database objects inside program units by granting roles to program units (doc), rather than - or in addition to - roles granted to schemas. That's the topic for this post.
Note: Oracle Magazine also offers this content (both blog posts) in a single article here.

Securing your database – and properly restricting access to the data and data structures within your database – ranks at the very top of the "most important things to do" list when building applications.
The best way to avoid unintended access or actions is to apply the "least privilege" principle: give a user the smallest number of (and most narrowly defined) privileges on database objects and the data inside those objects.

Oracle Database has always offered a very robust security mechanism: you can only access objects you own or those to which you were granted access. Within a PL/SQL program unit, you can choose the definer rights model (a user executes your code with your privileges) or the invoker rights model (a user executes your code with their privileges).  But the granularity of this mechanism operates at the schema level, making it difficult to apply the "least privilege" principle.

With Oracle Database 12c, you can now restrict privileges as tightly as you would like, right down to the individual program unit, by granting roles to program units, and not just to schemas. I'll explore this feature for both definer rights and invoker rights program units.

First, with definer rights, suppose that the HR schema was initially granted just two privileges: CREATE SESSION and CREATE PROCEDURE. I could then compile the following procedure in HR:

CREATE OR REPLACE PROCEDURE create_table (
   table_name_in IN VARCHAR2)
   AUTHID DEFINER
IS
BEGIN
   EXECUTE IMMEDIATE
      'CREATE TABLE ' || table_name_in || '(n NUMBER)';
END;

But when I try to create a table using the procedure, I see an error:

CONNECT HR/*****

BEGIN
   create_table ('my_table');
END;
/

ERROR at line 1: ORA-01031: insufficient privileges

Prior to Oracle Database 12c, the only way that HR could use this procedure would be to grant the CREATE TABLE procedure to the schema itself. But this means that any program unit defined in HR could then create a table, which the Chief Security Officer finds unacceptable.

With Oracle Database 12c, however, I can take a much more fine-grained approach, by granting privileges to the procedure itself, and not its owning schema.

Here’s how:

1. Create a role from a schema with the authority to do so, and grant it the CREATE TABLE privilege.

CREATE ROLE create_table_role
/

GRANT CREATE TABLE TO create_table_role
/

2. Grant the role to the procedure. This can be done as SYSDBA. It can also be done from the HR schema, if the role is granted to HR with the admin option. Here’s the grant as SYSDBA:

GRANT create_table_role TO PROCEDURE hr.create_table
/

To grant from HR, first execute this as SYSDBA:

GRANT create_table_role TO hr WITH ADMIN OPTION
/

ALTER USER hr DEFAULT ROLE ALL EXCEPT create_table_role
/

Then execute the grant from HR:

GRANT create_table_role TO PROCEDURE create_table
/

And now I can execute the procedure and successfully create the table:

BEGIN
   create_table ('my_table');
END;
/

PL/SQL procedure successfully completed.

But if I try to create the table directly, I see the same, earlier privileges error:

CREATE TABLE my_table2 (n NUMBER)
/

ERROR at line 1: ORA-01031: insufficient privileges

The only way to create a table from the HR schema is by calling this one procedure: a very targeted assignment of privileges. Now let's take a look at using code-based access control with an invoker rights module.

With invoker rights, the privileges of the invoking schema are used to determine what the module will be allowed to do. I need to give users the ability to display non-confidential information about employees: namely, they can see employee names and emails, but not salary information.

I can do this by creating a view on top of the EMPLOYEES table and only granting SELECT on the view. But I can also achieve this effect through code based access control, thereby avoiding the need to create a view.

Here's the invoker rights procedure for displaying appropriate employee information, owned by HR, which also owns the employees table.

CREATE OR REPLACE PROCEDURE show_employees (department_id_in IN INTEGER)
   AUTHID CURRENT_USER
AS
BEGIN
   DBMS_OUTPUT.put_line (
      'Employees in Department ' || department_id_in);

   FOR rec IN (SELECT e.last_name, e.email FROM hr.employees e
                WHERE e.department_id = department_id_in
                ORDER BY e.last_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.last_name || ' - ' || rec.email);
   END LOOP;
END;
/

I'll let everyone execute the procedure:

GRANT EXECUTE ON show_employees TO PUBLIC
/

No other schemas have been granted SELECT on employees, so if, for example, a user connected to the SCOTT schema tries to execute this procedure, she will see an error:

BEGIN
   hr.show_employees (10);
END:
/

ERROR at line 1:
ORA-00942: table or view does not exist

Prior to Oracle Database 12c, to get this to work, you would have to do one of the following:
  • Grant SELECT on this table to SCOTT, but that would give SCOTT access to confidential information.
  • Create a view on top of EMPLOYEES that does not include the confidential information, and then grant SELECT on that view to SCOTT.      
With Oracle Database 12c and higher, I can instead create a role that has the SELECT privilege on the EMPLOYEES table, and then assign the role to just that single procedure. Assuming HR has the CREATE ROLE privilege, here are the steps:

CREATE ROLE view_employees_role
/

GRANT SELECT ON employees TO view_employees_role
/

GRANT view_employees_role TO PROCEDURE show_employees
/

BEGIN
   hr.show_employees (10);
END:
/

Employees in Department 10
Whalen – JWHALEN@MY_COMPANY.COM

Now users can access the employee information appropriate to them, but I have not provided any other opportunities to access the employees table. I have, in other words, kept the attack surface (the number of points through which an unauthorized user can try to get at the table) to a minimum.

Monday, July 24, 2017

Document deprecated program units with new pragma (12.2)


Software is constantly evolving: bugs fixed, new features added, and better ways to do things discovered and implemented.

A great example of this dynamic from PL/SQL itself is the UTL_CALL_STACK package. This package was first introduced in Oracle Database 12c Release 1, and it improves upon the functionality already provided by the following functions in the DMBS_UTILITY package: FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE.

The same thing happens in PL/SQL code that is developed by customers. The now-outdated subprograms (or other elements) of one's API cannot be removed immediately; that would break existing code. But everyone would like to make sure that any new code uses the new API.

The new DEPRECATE pragma (compiler directive) in Oracle Database 12.2 will help you accomplish this transition in a smooth, error-free fashion. It provides a formal way to communicate information about deprecated elements [my change ok? SF YES] with a power that ordinary external documentation cannot convey.

You can apply this new pragma to a whole unit, to a subprogram within a unit, at any level of nesting, to any definition, and so on. When a unit is compiled that makes a reference to a deprecated element, a warning is displayed (when you have compile time warnings enabled).

Let's take a look at some examples.

1. Deprecate an entire package.

CREATE PACKAGE pkg AUTHID DEFINER 
AS
   PRAGMA DEPRECATE(pkg);

   PROCEDURE proc;
   FUNCTION func RETURN NUMBER;
END;

2. Deprecate a subprogram in a package. Note the comment added to the pragma. This text will be displayed along with the warning/error information.

CREATE PACKAGE pkg AUTHID DEFINER
AS
  PROCEDURE proc;
  PRAGMA DEPRECATE (
    proc,
    ’pkg.proc deprecated. Use pkg.new_proc instead.’);

  PROCEDURE new_proc;
END;

Let's try using that deprecated procedure, with warnings enabled.

ALTER SESSION SET plsql_warnings = 'enable:all'
/

CREATE OR REPLACE PROCEDURE use_deprecated
   AUTHID DEFINER
IS
BEGIN
   pkg.proc;
END;
/

Procedure USE_DEPRECATED compiled with warnings

PLW-06020: reference to a deprecated entity: PROC declared in unit PKG[4,14]. pkg.proc deprecated. 
   Use pkg.new_proc instead.

Deprecation Warnings

PL/SQL in Oracle Database 12.2 has four new warnings to help you utilize the DEPRECATE pragma:

6019. The entity was deprecated and could be removed in a future release. Do not use the deprecated entity.

6020. The referenced entity was deprecated and could be removed in a future release. Do not use the deprecated entity. Follow the specific instructions in the warning if any are given.

6021. Misplaced pragma. The DEPRECATE pragma should follow immediately after the declaration of the entity that is being deprecated. Place the pragma immediately after the declaration of the entity that is being deprecated.

6022. This entity cannot be deprecated. Deprecation only applies to entities that may be declared in a package or type specification as well as to top-level procedure and function definitions. Remove the pragma.

Try it on LiveSQL!

I know, I know. You'd love to start exploring this and other new features in 12.2, but you don't yet have 12.2 installed at work. No problem!

LiveSQL offers free, 24x7 access to a 12.2 schema. You can execute SQL and PL/SQL code to your heart's content.

You can also run scripts that others, including myself, have uploaded to LiveSQL.

Here's one on the deprecate pragma. Enjoy!

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.

If you like to complement your graphical IDE with a command line tool (or OMG if you actually prefer a command line tool to a graphical interface), you should also check out the relatively new and generating-lots-of-excitement SQLcl.

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.
Important Note: These are settings for use in DEVELOPMENT - and they will be applied to all connections made in SQL Developer. When you deploy to production, you should use a script that explicitly sets values for warnings, optimization level (still 3) and PL/Scope (off).
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?