Tuesday, December 20, 2016

PL/SQL Brain Teaser: When is NO_DATA_FOUND not?

Here goes:

I execute this statement:

CREATE OR REPLACE FUNCTION ndf
   RETURN NUMBER
IS
BEGIN
   RAISE NO_DATA_FOUND;
END;
/

We all know what that function is going to do, right? #Fail, as one might say on Twitter.

So the brain teaser is: 

In the block below, replace <statement> with a single statement that does not contain any exception handling, but does call the NDF function, so that after the block executes, "NDF? What NDF?" is displayed on the screen.


DECLARE
   n   NUMBER;
BEGIN
   <statement>
   DBMS_OUTPUT.PUT_LINE ('NDF? What NDF?');
END;
/

I will wait a bit to post my answer. I encourage you to post yours as a comment to this blog post.

Wait....
Wait....
Wait for it....

OK!

After a couple of days of collecting responses on this post and also in LinkedIn, it's time to publish the answer:

You can replace <statement> with any DML statement that executes the function, and the failure of the function with an unhandled NO_DATA_FOUND exception will not cause the SQL statement to terminate with said exception.

Instead, the SQL engine swallows up that exception and simply returns NULL to the statement.

Why, you might wonder, would the SQL engine do this?

NO_DATA_FOUND is, on the one hand, an exception like any other. And on the other hand, it is different, in that the lack of data often does not indicate any kind of actual error, but simply a data condition. And so it was decided that when a function executed within a SQL statement fails with an unhandled NO_DATA_FOUND, that NULL would simply be returned.

You might not like that answer or decision, but there it is.

Now, there is another way to both invoke the NDF function in a single statement and not have the exception terminate the block, as Edwin points out in the comments: Call the function inside a COALESCE function call.

COALESCE offers the very cool feature of not evaluating an expression in its least until it needs to (in contrast, for example, to NVL, which always evaluates the second argument, even if the first argument is not NULL.

Update 12-21: Jeff Kemp (@jeffreykemp) notes on LinkedIn that since the text in the brain teaser says "call the NDF function", COALESCE is not a valid answer, since you never call the function. Strictly speaking he is correct.

Friday, December 16, 2016

Do Comments Throw Off Error Backtrace? No!


Just received this feedback on an Oracle Magazine article:
I’ve just started using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it’s a great utility when the code contains no comments, but unless I’m missing something it’s not terribly useful with regard to pinpointing the exact line number when the code contains comments.
Now, I must confess that I am tempted to respond with such questions along these lines:
Did you try it out? That's the best way to learn!
But I am always looking for another reason to post on my blog and add to the LiveSQL repository. So that's what I am doing! :-)

The answer is quite straightforward:

Comments do not interfere with "pinpointing the exact line number" on which the error was raised.

But I bet you want proof, so let's get down to business.

I create a procedure with comments and a RAISE statement:

CREATE OR REPLACE PROCEDURE comments_throw_off_line# 
IS 
/* 
Here's my header 
 
Blah blah blah 
*/ 
BEGIN 
   DBMS_OUTPUT.put_line ('wow!'); 
   /* 
   And here's another comment because I really love 
   to fill my code with meaningless comments! 
   */ 
   RAISE PROGRAM_ERROR; /* This is line 14 */ 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace); 
END;

I run the procedure and see this output:

BEGIN 
   comments_throw_off_line#; 
END;

wow!
ORA-06512: at "COMMENTS_THROW_OFF_LINE#", line 14

So, there's your answer.

Next question? :-)

Wednesday, December 14, 2016

Some Beginner Tips for Working with JSON - From a Beginner

We are enhancing the Oracle Dev Gym to automatically execute code you type as a solution to a problem, and validate that it works. We are doing this via REST calls to LiveSQL. It's very cool and a lot of fun to put together. Hopefully it will be way more fun for you to see if you can solve our challenges.

Anyway, LiveSQL is going to pass back results via a JSON document, which means that yes, finally, I am going to start working with JSON and learning about Oracle Database JSON functionality.

Yes, I am a total novice at this. Which, I imagine, is the case for many other PL/SQL developers. So I thought I would immediately share some early lessons learned. That way maybe you will save yourself the 15 minutes I wasted sorting this out.

First, though, if you haven't done anything with JSON yet, start by looking over these:

JSON in Oracle Database
JSON Support in Oracle Database 12c Release 1 (12.1.0.2)

Plus, all the code shown below can be seen, and run, in LiveSQL.

OK. So first I created a table to hold my JSON documents:

CREATE TABLE json_data (my_doc CLOB)
/

Then I insert a row of data. JSON! (or so I thought)

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN 
          DBMS_OUTPUT.PUT_LINE ('Hello JSON!'); 
       END;"}^');

   COMMIT;
END;
/

Use the JSON_VALUE function to get the value for a given property name.

SELECT json_value (j.my_doc, '$.without_spaces') r
  FROM json_data j
/

OK

I can also enclose the property name inside double quotes:

SELECT json_value (j.my_doc, '$."without_spaces"') r
  FROM json_data j
/

OK

Now let's the value for a property name that contains spaces:

SELECT json_value (j.my_doc, '$.with spaces not a good idea') r
  FROM json_data j
/

ORA-40442: JSON path expression syntax error ('$.with spaces not a good idea')

Ugh. OK, so if I am going to put spaces in my names I must enclose that name within double quotes in my call to JSON_VALUE:

SELECT json_value (j.my_doc, '$."with spaces not a good idea"') r
  FROM json_data j
/

Not OK

Fine. Now let's get that PL/SQL block back from my table - notice, I remembered to include the double quotes:


SELECT NVL (json_value (j.my_doc
                      , '$."carriage returns definitely bad idea"'), 'NULL') r
  FROM json_data j
/

NULL

Nothing there! Nothing there? But there is something there. What's going on?

So now I waste time trying to figure it out - but you get to save yourself the time by reading my blog! The problem is that you cannot have carriage returns or newline characters in your JSON values. In other words, that "JSON document" I inserted isn't even valid JSON. And I didn't realize it, because I was lazy and did not tell Oracle Database that the my_doc column contains JSON.

Let's do that:

DELETE FROM json_data
/

ALTER TABLE json_data
ADD CONSTRAINT json_data_json_chk
CHECK (my_doc IS json) enable
/

And  now when I try to insert this value....kaboom!

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN 
          DBMS_OUTPUT.PUT_LINE ('Hello JSON!'); 
       END;"}^');

   COMMIT;
END;
/

ORA-02290: check constraint (JSON_DATA_JSON_CHK) violated ORA-06512: at line 2

OK. So I replace the newline characters with "\n", a standard replacement string to indicate a new line. In the code below you see "\\" because the first \ escapes the next one.

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN\\nDBMS_OUTPUT.PUT_LINE ('Hello JSON!');\\nEND;"}^');

   COMMIT;
END;
/

And now....no problems!

SELECT json_value (j.my_doc, '$."carriage returns definitely bad idea"') r
  FROM json_data j
/

BEGIN\nDBMS_OUTPUT.PUT_LINE ('Hello JSON!');\nEND;

And so to conclude:
  1. When stuffing JSON into a table's column, make sure you use the IS JSON check constraint to ensure valid JSON.
  2. Don't put spaces in your property names. Use underscores or camelCase.
  3. Keep newline characters out of your JSON documents.

Tuesday, December 13, 2016

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x the lowest salary in the company. Your salary will be automatically set to the maximum allowed, if this rule is broken.
So just check to see if that rule is violated, right? Easy enough in PL/SQL, right in a database trigger (see links at bottom of post for discussions on whether or not you should put logic like this in your database triggers):

CREATE OR REPLACE TRIGGER equitable_salary_trg 
   AFTER INSERT OR UPDATE 
   ON employees 
   FOR EACH ROW 
DECLARE 
   l_max_allowed   employees.salary%TYPE; 
BEGIN 
   SELECT MIN (salary) * 25 
     INTO l_max_allowed 
     FROM employees; 
 
   IF l_max_allowed < :NEW.salary 
   THEN 
      UPDATE employees 
         SET salary = l_max_allowed 
       WHERE employee_id = :NEW.employee_id; 
   END IF; 
END equitable_salary_trg; 

Well....maybe not. I execute the following block:

BEGIN 
   UPDATE employees 
      SET salary = 100000 
    WHERE last_name = 'King'; 
END; 

and I see this error:

ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "EQUITABLE_SALARY_TRG", line 4

OK, we get that, right? I am both selecting from and trying to update the EMPLOYEES table in a row-level trigger. That's the no-no.

Getting Around ORA-04091 with PL/SQL Packages

The solution, conceptually, is simple enough. If I can do task X in the row level trigger, save whatever information I need to perform X on that row in a to-do list (a collection, perhaps?). Then define an AFTER STATEMENT trigger that goes through the to-do list, and executes the desired logic for each row.

The traditional (now, out-of-date) solution is to define a package that contains a collection defined at the package level. Package-level variables have session scope. So I can add information to the collection within the row-level trigger, and it will still be there when I bubble up to the statement-level trigger.

Here's my package specification:

CREATE OR REPLACE PACKAGE equitable_salaries_pkg 
IS 
   PROCEDURE initialize; 
 
   PROCEDURE add_employee_info ( 
      employee_id_in IN employees.employee_id%TYPE 
    , salary_in IN employees.salary%TYPE 
   ); 
 
   PROCEDURE make_equitable; 
END equitable_salaries_pkg; 

Huh. I don't see any collection there. Right. You shouldn't. If you put the collection in the specification, it can be modified by any schema with EXECUTE authority on the package, in whatever way anyone wants to mess with that collection. Well, that's no good. So I "hide" the list in the body and "expose" it through the procedures in the spec.

CREATE OR REPLACE PACKAGE BODY equitable_salaries_pkg   
IS   
   TYPE id_salary_rt IS RECORD (   
      employee_id   employees.employee_id%TYPE   
    , salary        employees.salary%TYPE   
   );   
   
   TYPE g_emp_info_t IS TABLE OF id_salary_rt   
      INDEX BY PLS_INTEGER;   
   
   g_emp_info                 g_emp_info_t;   
   g_corrections_in_process   BOOLEAN      := FALSE;   
   
   PROCEDURE initialize   
   IS   
   BEGIN   
      g_emp_info.DELETE;   
   END initialize;   
   
   PROCEDURE finished_corrections   
   IS   
   BEGIN   
      g_corrections_in_process := FALSE;   
   END finished_corrections;   
   
   PROCEDURE starting_corrections   
   IS   
   BEGIN   
      g_corrections_in_process := TRUE;   
   END starting_corrections;   
   
   FUNCTION corrections_in_process   
      RETURN BOOLEAN   
   IS   
   BEGIN   
      RETURN g_corrections_in_process;   
   END corrections_in_process;   
   
   PROCEDURE add_employee_info (   
      employee_id_in IN employees.employee_id%TYPE   
    , salary_in IN employees.salary%TYPE   
   )   
   IS   
      l_index   PLS_INTEGER := g_emp_info.COUNT + 1;   
   BEGIN   
      IF NOT corrections_in_process   
      THEN   
         g_emp_info (l_index).employee_id := employee_id_in;   
         g_emp_info (l_index).salary := salary_in;  
      END IF;   
   END add_employee_info;   
   
   PROCEDURE make_equitable   
   IS   
      l_max_allowed   employees.salary%TYPE;   
      l_index         PLS_INTEGER;   
   BEGIN   
      IF NOT corrections_in_process   
      THEN   
         starting_corrections;   
   
         SELECT MIN (salary) * 25   
           INTO l_max_allowed   
           FROM employees;      
   
         WHILE (g_emp_info.COUNT > 0)   
         LOOP   
            l_index := g_emp_info.FIRST; 
   
            IF l_max_allowed < g_emp_info (l_index).salary   
            THEN   
               UPDATE employees   
                  SET salary = l_max_allowed   
                WHERE employee_id = g_emp_info (l_index).employee_id;   
            END IF;   
   
            g_emp_info.DELETE (g_emp_info.FIRST);   
         END LOOP;   
   
         finished_corrections;   
      END IF;   
   END make_equitable;   
END equitable_salaries_pkg;

See? Aren't you glad I wrote that, so you didn't have to? :-) Well, it gets better - as in lots of that code is unnecessary. But before I get to that, let's finish up the old-style approach. We need to rebuild the triggers!

1. Before getting started, make sure no one is going to muck with those rows. And make sure the package-based collection is empty.


CREATE OR REPLACE TRIGGER equitable_salaries_bstrg 
   before INSERT OR UPDATE  
   ON employees 
BEGIN 
   LOCK TABLE employees IN EXCLUSIVE MODE; 
   equitable_salaries_pkg.initialize; 
END; 

2. For each insert or update to employees, add the necessary information to the to-do list.

CREATE OR REPLACE TRIGGER equitable_salaries_rtrg  
   AFTER INSERT OR UPDATE OF salary  
   ON employees  
   FOR EACH ROW  
BEGIN  
   equitable_salaries_pkg.add_employee_info (:NEW.employee_id, :NEW.salary);  
END; 

3. Create a statement-level trigger to apply the rule.

CREATE OR REPLACE TRIGGER equitable_salaries_astrg  
   AFTER INSERT OR UPDATE   
   ON employees  
BEGIN  
   equitable_salaries_pkg.make_equitable;  
END; 

And now the update statement will work without raising any ORA-04091 errors!

BEGIN  
   UPDATE employees  
      SET salary = 100000  
    WHERE last_name = 'King';  
  
   ROLLBACK;  
END; 

add_employee_info: 100-100000
add_employee_info: 156-100000
make_equitable max allowed 52500
make_equitable emp id and salary: 100-100000

Yep. That's a lot of code to write and deal with to get around this problem. So several years back, the PL/SQL team decided to make things easier for their users with....

The Compound DML Trigger

Straight from the doccompound DML trigger created on a table or editioning view can fire at multiple timing points. Each timing point section has its own executable part and optional exception-handling part, but all of these parts can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error. Two common uses of compound triggers are: (1) To accumulate rows destined for a second table so that you can periodically bulk-insert them; (2) To avoid the mutating-table error (ORA-04091).

The compound trigger more allows you to define variables which persist through the execution of the steps defined in the compound trigger. And that's the aspect of this feature that makes things so much easier when it comes to mutable table errors.

Using this feature, I can combine all the different trigger events and code, plus they share scope like the subprograms of a package body. So I declare a variable in the compound trigger and reference it in both trigger events. Take a look:

CREATE OR REPLACE TRIGGER equitable_salary_trg    
FOR UPDATE OR INSERT ON employees    
COMPOUND TRIGGER     
   TYPE id_salary_rt IS RECORD (    
      employee_id   employees.employee_id%TYPE    
    , salary        employees.salary%TYPE    
   );    
    
   TYPE row_level_info_t IS TABLE OF id_salary_rt  INDEX BY PLS_INTEGER;    
    
   g_row_level_info   row_level_info_t;    
    
   AFTER EACH ROW IS    
   BEGIN  
      g_row_level_info (g_row_level_info.COUNT + 1).employee_id :=    
           :NEW.employee_id;    
      g_row_level_info (g_row_level_info.COUNT).salary := :NEW.salary;
   END AFTER EACH ROW;    
    
   AFTER STATEMENT IS    
      l_max_allowed   employees.salary%TYPE;    
   BEGIN      
      SELECT MIN (salary) * 25    
        INTO l_max_allowed    
        FROM employees;     
       
      FOR indx IN 1 .. g_row_level_info.COUNT    
      LOOP                                      
         IF l_max_allowed < g_row_level_info (indx).salary    
         THEN    
            UPDATE employees    
               SET salary = l_max_allowed    
             WHERE employee_id = g_row_level_info (indx).employee_id;    
         END IF;    
      END LOOP;    
   END AFTER STATEMENT;    
END equitable_salary_trg; 

Much simpler  - all relevant code in one place.

More reliable - you don't have to worry about managing the session-persistent collection.

Less code - always a nice thing, as long as the "less code" is also understandable and easy to maintain.

You might also find these resources helpful:

ORACLE-BASE: Trigger Enhancements in Oracle Database 11g Release 1
ORACLE-BASE: Should you use triggers at all? (Facts, Thoughts and Opinions)
Toon Koopelars: Triggers Considered Harmful, Considered Harmful

Tuesday, December 6, 2016

PL/SQL 201: Ensuring backward compatibility in your PL/SQL code

Unlike other code in the stack, database-level programs such as those built in PL/SQL, tend to have a very long half-life. In part, that's because companies are far less likely to change their database technology than user interface language.

And that means that it is especially important for Oracle Database programmers to prioritizing writing maintainable, well-documented programs. It is also critical that as we make changes to our code base, we don't break programs that have been working for years.

In other words, whenever possible, enhanced code should be backward compatible with earlier versions of code. That's called backward compatibility. In this post, I will explore features of PL/SQL that make it easy to ensure your latest and greatest code has maximum compatibility with "the past."
If a tree falls in a forest and no one is around to hear it, does it make a sound?
You've heard that before, right? Well, how about this one:
If you write a program and no one ever uses it, did you really write that program?
I ask myself this a lot. I've written hundreds of "demo" programs over the years, for trainings, for play, for libraries. But, hey, maybe nobody ever used them or ran them. Did I ever really write it? Oh, sure I did! Did it really matter? Maybe just to me.

Anyway, chances are that you don't write a program unless it is going to be used. And as soon as "someone" uses your code (it could even by you, with a new packaged procedure calling an existing subprogram), you've implicitly signed a contract with that person (or other program unit), stating:
I will do everything in my power to ensure that as I make changes in the future, I will honor how the program works for you now.
So let's see how we can keep our promises, by taking a look at what I just had to do today to my code.

Most of the production PL/SQL code I've written since 2010 is for the PL/SQL Challenge, a website offering weekly quizzes on SQL, PL/SQL and more (try it out!). The backend code uses the "qdb" prefix for "Quiz DataBase". And I created a package to manage player information.

You can play weekly quizzes competitively or just for fun. So I created the set_competitive_status procedure to toggle between them. In other words, the first time it is called for my user ID, it sets up to compete (my answers are ranked). The next it is called, my status is set to "for fun only."

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
END;

And for six years it worked just fine. In other words, it is used, both in the Oracle Application Express "front-end" and in other packages. Here's an example from the backend:

PROCEDURE qdb_process_answers
   AUTHID DEFINER
IS
BEGIN
   qdb_player_mgr.set_competitive_status (
      user_id_in          => 1506,
      competition_id_in   => 15709);
END;

And from APEX:


We are now, though, working on a new "skin" for the PL/SQL Challenge: the Oracle Dev Gym. This site has a different flow when taking a quiz, and I needed to be able to force the competitive status to "for fun only" - even if it was the first time, even if it was already set that way. In other words, my "toggle" procedure should not toggle.

First, let's see what happens if I push ahead somewhat blindly, paying no attention to the past. "Hey, no problem, I will just add a parameter!"

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in           IN INTEGER,
      competition_id_in    IN INTEGER,
      force_non_compete_in IN BOOLEAN);
END;

I compile the package with the modified specification. No errors! Exciting! Even before I get to changing the implementation of this enhancement, though, I notice that the QDB_PROCESS_ANSWERS procedure has an INVALID status. That makes sense - I changed a program unit (QDB_PLAYER_MGR) on which the procedure relies.

No worries. I will just recompile the procedure. But when I do, I see this error:

PLS-00306: wrong number or types of arguments in call to 'SET_COMPETITIVE_STATUS' 

Um, still no worries? Well....I guess not. I will just change QDB_PROCESS_ANSWERS to pass a Boolean value for the third argument:

PROCEDURE qdb_process_answers AUTHID DEFINER
IS
BEGIN
   qdb_player_mgr.set_competitive_status (
      user_id_in           => 1506,
      competition_id_in    => 15709,
      force_non_compete_in => FALSE);
END;

Now it compiles without errors. Problem solved.

No! No! No! That is almost certainly the wrong thing to do. Why?

1. This procedure does not need the "force non compete" functionality. It's fine the way it is.

2. What if there are hundreds of invocations of this procedure in my code base? Am I going to search them all out (which I can do quite nicely with PL/Scope) and change each one, even though they do not need the new functionality?

3. Even worse, am I going to deal with the outraged howls of other developers on my team whose code now will not compile?

So what can I do instead?

1. Provide a default value for the new parameter: if it is a trailing IN parameter, then I can assign a default value, and all existing invocations of that subprogram will recompile successfully:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in           IN INTEGER,
      competition_id_in    IN INTEGER,
      force_non_compete_in IN BOOLEAN DEFAULT FALSE);
END;

2. Create an overloading of that procedure (same name, different parameter list):

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER,
      force_non_compete_in IN BOOLEAN);
END;

Now, when QDB_PROCESS_ANSWERS is recompiled, the PL/SQL compiler will automatically determine which of the SET_COMPETITIVE_STATUS subprograms "match" the invocation in the procedure.

3. Create an entirely new procedure for this new requirement, as in:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE turn_off_ranking (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
END;

So which should you do? It depends (when doesn't it "depend"? :-) ).

You will not be able to simply add a parameter with a default value if the new parameter is an OUT or IN OUT parameter.(only IN parameters can have default values). In this case, you will need an overloading. For example:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER,
      new_status_out OUT VARCHAR2);
END;

If adding that single new IN parameter actually means making a whole lot of changes in the existing procedure, it will likely make the most sense to create a new subprogram dedicated to that strikingly different algorithm.

To Conclude (and offer some additional advice)

1. When changing the header of a subprogram already in use, honor the current signature, and do everything you can to avoid having to change existing invocations to match the new signature. Use default parameter values or overloading. Which, by the way, is another reminder of a baseline best practice for PL/SQL: put all your procedures and functions in packages. Do not create schema-level procedures and functions. You can't overload at the schema level.

2. Determine whether the changes made to the implementation of the subprogram (its executable section) may affect the way the subprogram is currently used. For example, if it is a function that is executed from within a SQL statement, then you could cause all sorts of problems by inserting a SQL statement into the function, especially non-query DML (inserts, updates, deletes).

3. If a subprogram that is currently in use is being replaced entirely by a new, shinier, better version, mark the original subprogram as deprecated with the DEPRECATE pragma (new to 12.2).


Tuesday, November 15, 2016

PL/SQL Programming Joke #2: Don't Use Built-In Packages, Because - Definer Rights

As my 25th winter in Chicago approaches (and after the 2016 elections), I attempt to cheer myself up with jokes. Programmer jokes.



Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision. 

Here's my second joke for your enjoyment. And just in case you have any doubts, this is a true story. As in not "based on a true story." True. All the way through.

After I left Oracle Corporation back in 1992, I spent a few years consulting before I wrote the first edition of Oracle PL/SQL Programming (and my life changed big time). For about six months, I commuted to downtown Chicago to work at an insurance company.

I was, as you might expect, part of the development team, writing PL/SQL packages and building user interfaces in Oracle's wonderful SQL*Forms 3.

This particular insurance company had been acquired by another, even larger insurance company. And it turned out that all the DBAs worked in an entirely separate department in the parent company. As you might expect, that amount of physical and organization distance did not contribute to good relations between the developers and DBAs.

Well, I'd been there for a month or two, when I came into work Monday morning to find that chaos reigned. Almost of the code was broken and would not compile successfully. 

The problem?

The DBAs had, over the weekend, revoked privileges on all the built-in packages (DBMS_SQL, UTL_FILE, DBMS_OUTPUT, DBMS_UTILITY, etc.) from our development schemas.

Yes, that would make it hard for program units to compile.

Why would a DBA take such drastic action? They were eager to explain:
When you execute a stored subprogram, it uses the privileges of the defining schema. So when you run a subprogram in a package owned by SYS (like all the built-ins), you are running with SYS authority. That is a major security hole, and we plugged it.
Hmmmm. Well, gee, that sounds kind of reasonable so maybe….wait just a minute. That might "sound" reasonable, but it also sounds like absolute nonsense. There is no way that Oracle would have designed the database, and the built-in packages that are necessary for building our apps, with such a security problem.

And once again, we find a nugget of truth inside this ludicrous action: it is absolutely true that when you compile a program unit using definer rights (which happens when you include the AUTHID DEFINER clause in the program unit header, or when you leave out the AUTHID clause entirely), the privileges of that defining schema (the owner of the program unit) will be used to resolve references to database objects and therefore control what you can do when you call that program.
The AUTHID clause was added to PL/SQL in Oracle8i. We were on Oracle8 at the insurance company. So no AUTHID and what does that mean? That all of the code we (all PL/SQL programmers, all of us) wrote in that version of Oracle were "definer rights" programs. So the DBAs were right?

No, no, no!

OK, it's true that the AUTHID clause was not available to PL/SQL programmers. That has nothing to do, however, with how Oracle itself could set up its built-in packages. And so, of course, packages like DBMS_SQL executed with the privileges of the invoker, not the definer. You could not drop data dictionary views using DBMS_SQL, not in Oracle7, Oracle8 or any other version of the Oracle Database.

Silly DBAs. We got them to reverse their revocation of privileges that day. Developers 1 - DBAs 0.
Hey, but if you'd like to test your DBA, ask them to grant you EXECUTE authority on a package named DBMS_SYS_SQL because you need to write some dynamic SQL method 4. 

DBMS_SYS_SQL is the package that DBMS_SQL itself calls to perform its dynamic SQL operations, and if you use that package, you will execute SQL statements with the privileges of SYS.

So if your DBA says "OK, you can use DBMS_SYS_SQL"....then quick make sure your manager knows that person is in drastic need of training and/or mentoring.

Know Any Good "Jokes"?

I bet you've run into all sorts of hilariously truthy stories of developers or DBAs doing the most absurd things for the only slightly less absurd reasons. Please share them with me, either via comment below or by sending an email to steven dot feuerstein at oracle.com. 

In the meantime, do not use any built-in packages. 'Cause they're owned by SYS and therefore you can....

NO

That was a joke, OK? If you have execute authority on a package owned by SYS, then you can use it.

Monday, November 14, 2016

PL/SQL Programming Joke #1: Don't Use Packages, Says My DBA

As my 25th winter in Chicago approaches, I attempt to cheer myself up with jokes.

Programmer jokes.

Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.

Here's my first joke for your enjoyment.

No Packages for You!

Packages should be the foundation, the building blocks of any PL/SQL-based applications. I believe that you should not create schema-level procedures and functions, but instead define them as subprograms in one or more packages.

So you can imagine it came as quite a shock to me when an attendee at one my of trainings came up over the break and said to me:
My DBAs won't let me put my code in packages. Everything has to be defined as a procedure or function at the schema level. They tell me that packages take up too much memory and so they cannot be used.
Wow! Packages take up too much memory....

Who would've thought it?

Now, as with most lies, exaggerations and extremely bad advice, there is a small kernel of truthiness that led this person's DBA to take this outlandish position.

That small kernel is the fact that when you invoke any subprogram of a package, the entire package is loaded into SGA memory.

Given this fact, it is certainly possible to imagine a worst-case scenario about which a DBA should be concerned.

Worst case scenario: All of the backend, PL/SQL code for the application is implemented in a single, enormous package, containing hundreds - no, thousands - of subprograms. So to run anything in that backend requires that Oracle load all the partially-compiled code into the SGA, consuming memory unnecessarily and perhaps causing other data in the SGA to be pushed out of the cache, through application of the least recently used algorithm.

I hope you would all agree quite readily that just because you can come up with a worst-case scenario, you should not necessarily reject the feature involved in that scenario.

No, you just need to make sure you utilize the feature properly.

So let's revisit the DBA's concern: "Packages use too much memory."

What he was really saying was: "If you only need to run procedure X, why use up memory to also load procedures Y and Z, as well?"

This is a valid concern - if your package is filled up with subprograms that support a wide range of disconnected requirements. Suppose, on the other hand, that your package is very focused and contains only those subprograms related to a specific area of functionality.

It is, therefore, quite likely that if you are running procedure X, you will also soon need to run procedures Y and Z.

If these three procedures are not in a package, then they have to be loaded and managed separately in the SGA. They will require almost exactly the same amount of memory as the three procedures in the package, but Oracle will have to consume more CPU cycles.

In other words, Oracle's memory management for packages - loading the entire package into memory - should help your application run more efficiently, and certainly not consume memory unnecessarily when packages are constructed properly.

So by all means use packages but follow these guidelines when doing so:

  • Create lots of small, tightly-focused packages. Group all related functionality in the same package.
  • Minimize dependencies between packages (especially in the package specification).
  • When a package gets big, check to see if its scope (the functionality implemented in the package) has broadened. If so, consider breaking up that single package into several, smaller packages.
Know Any Good "Jokes"?

I bet you've run into all sorts of hilariously truthy stories of developers or DBAs doing the most absurd things for the only slightly less absurd reasons. Please share them with me, either via comment below or by sending an email to steven dot feuerstein at oracle.com. 

In the meantime, don't forget to put all your code in schema level procedures and functions. That way you will use less memory and the Schema Browser in SQL Developer will be about 100 miles long.

That was a joke, OK? All code in packages!

PL/SQL Programming Joke #3: Need to make my code compile faster!


As my 25th winter in Chicago approaches (and after the 2016 elections),, I attempt to cheer myself up with jokes. Programmer jokes.

Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision. 

Here's the most recent "joke" I heard from a developer, via an email back in July:
We just encountered a PL/SQL performance problem after migrating a database from 10g to 11g and I tought it might interest you. I isolated the problem using  the 10046 traces and DBMS_PROFILER. So I was able to reproduce the problem with a very simple PL/SQL testcase, but I cannot explain it.
He then pasted in 439 lines of code and output. So this is the "very simple" testcase? It might be, but still I wrote back:

"It may be simple, but it's long and I'd appreciate it if you would summarize for me what you believe you have discovered."

This way, at least, I didn't have to feel the least bit obligated to tangle with his problem until he replied.

But his response was even better and more entertaining than I'd hoped:
I just found out the cause of the strange behavior...Somebody had set the PLSQL_OPTIMIZE_LEVEL to 1 instead of 2 at the database level so that a massive recompilation of all packages would go faster.
When the optimization was set back to 2, the performance problem he noticed earlier disappeared. Hurray!

So now let's go back and parse that paragraph:

"Somebody had set" the optimization level - I sure hope that somebody else knows exactly who that original somebody is. You'd like to think that there aren't too many people with the authority to change the default optimization level on a database instance.

"…so that a massive recompilation of all packages would go faster."

Ah, that's just too delicious! Again, an element of truth that goes a loooong way towards mucking up application performance.

It is true that the higher you set the PL/SQL optimization level, it takes longer for your code to compile, because the compiler is doing more work analyzing and applying transformations to your code to improve runtime performance.

Hey, let's shrink that paragraph down to its essence:
Spend more time compiling your code so that code runs faster for your users.
Sounds like an excellent tradeoff. We wait a little longer for code to compile (and would we even notice the difference? Unlikely.) and in return our users are happier.

Not for that DBA, though. Maybe he had a hot lunch date or had signed up for one of Tom Kyte's amazingly popular webinars and trainings and that was about to begin. At which point he might learn about how important it is to keep that optimization at least at 2. 

Whatever the case, all he knew was that he needed to get that code complied fast.

I just hope it wasn't on a production instance.

Know Any Good "Jokes"?

I bet you've run into all sorts of hilariously truthy stories of developers or DBAs doing the most absurd things for the only slightly less absurd reasons. Please share them with me, either via comment below or by sending an email to steven dot feuerstein at oracle.com. 

In the meantime, don't forget to set the optimization level to 1. Or maybe 0. That way your code will compile faster. 

Giving you less time for that extra cup of coffee. Which is probably a good thing.

That was a joke, OK? Optimization level at 2 or higher, please! 


Originally published in ODTUG's Confessions of a Quick and Dirty Programmer series

Monday, November 7, 2016

On the importance of keeping algorithmic logic separate from display logic

On the PL/SQL Challenge, all times are shown in the UTC timezone. Weekly quizzes end on Friday, midnight UTC. So I recently decided that when I display the time that the quiz starts and ends, I should add the string "UTC".

Our quiz website is built in Oracle Application Express 5.0, so I opened up the process that gets the date and found this:

DECLARE
   l_play_date   DATE
      := qdb_quiz_mgr.date_for_question_usage (:p46_question_id);
BEGIN
   :p46_scheduled_to_play_on := TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI');

"OK, then," says Steven the Fantastic Developer to himself. "I know exactly what to do."

And I did it:

DECLARE
   l_play_date   DATE
      := qdb_quiz_mgr.date_for_question_usage (:p46_question_id);
BEGIN
   :p46_scheduled_to_play_on := 
      TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI') 
      || ' UTC';

Ah, PL/SQL and APEX - so easy to use! :-)

Now, there are lots of things you could say about the change I made above, but here's one thing that is undeniably true:
P46_SCHEDULED_TO_PLAY_ON will never by NULL.
Right? Right. Of course, right.

So that's fine, though. Because that's what I wanted: to have "UTC" always show up, and there's always going to be a date when the question is used in a quiz, right?

Well, no. In fact, this code is part of our Quiz Editor page, and on that page we offer a button that allows you to easily and quickly schedule a quiz for play.

But only if it hasn't already been scheduled. If it hasn't already been scheduled, then the date is, oh wait, um, NULL.

And that's why we have a condition on that button:


And that's why Eli Feuerstein, the fine fellow who does most of the work on the PL/SQL Challenge and it's cool new sister, Oracle Dev Gym, reported an issue with this page:
The Schedule button never appears on the page!
Awwwwwwwwwww......

So two lessons learned (re-learned, and learned again, then forgotten, then re-learned, then learned again....):

1. When I am about to make a change, ask myself: "What impact might this have?" 

In the world of APEX, it's pretty easy: search for the string "P46_SCHEDULED_TO_PLAY_ON" and see how it is used in the application. 

2. Keep completely separate the data (in this case, APEX items) that is used for algorithmic logic and the data that is used for display purposes.

I could create a separate item for display purposes, or a different item to be used in conditions and other PL/SQL blocks. 

But I should not use the same item for both.

Thursday, November 3, 2016

PL/SQL 101: Less code is better - avoid unnecessarily complex algorithms

With programmers new to PL/SQL (and SQL), it is not uncommon to find that they overcomplicate things, writing more code than is necessary, and putting too much logic into PL/SQL.  That problem can then be compounded by accidentally getting the "right answer" based on inadequate testing and test data.

So it is always good to be reminded: 
Do as much work as you can in SQL, and then finish up in PL/SQL. 
Where "finish up" means do whatever is appropriate within the database, and then make that available to whatever language is being used to write the UI!

A recent quiz on the PL/SQL Challenge explored this topic, and I offer it below as a learning exercise via blog post.

Suppose I have a table and dataset as follows:

CREATE TABLE plch_animals
(
   animal_id     INTEGER PRIMARY KEY,
   animal_name   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_animals (animal_id, animal_name)
        VALUES (1, 'Bonobo');

   INSERT INTO plch_animals (animal_id, animal_name)
        VALUES (2, 'Cockatoo');

   INSERT INTO plch_animals (animal_id, animal_name)
        VALUES (3, 'Spider');

   COMMIT;
END;
/

And I need to write a program that produces the following output:

Animals in Alphabetical Order
Bonobo
Cockatoo
Spider

Let's take a look at some ways to achieve this objective (some of them distinctly sub-optimal, holding off the best till last):

#1. Accidental Success Through Bad Test Data

DECLARE
   l_count   INTEGER;
   l_name    plch_animals.animal_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   SELECT COUNT (*) INTO l_count FROM plch_animals;

   FOR indx IN 1 .. l_count
   LOOP
      SELECT animal_name
        INTO l_name
        FROM plch_animals
       WHERE animal_id = indx;

      DBMS_OUTPUT.put_line (l_name);
   END LOOP;
END;
/

You see the desired output, but only because the animal IDs happen to ascend in exactly the same sequence as an alphabetical ordering of the animal names.(see line in blue).

You certainly do not want to ever assume this will be the case with real data. Even if you look at the data today and can confirm that pattern. That's just for today. A warning sign that this code is problematic is the SELECT COUNT(*).

You should generally not need to do a separate query just to control the number of iterations of a loop. A cursor FOR loop will usually take care of that for you.

#2. Too Much SQL, Not Enough Data

DECLARE
   TYPE animal_ids_t IS TABLE OF plch_animals.animal_id%TYPE;

   l_animal_ids   animal_ids_t;
   l_name         plch_animals.animal_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   SELECT animal_id
     BULK COLLECT INTO l_animal_ids
     FROM plch_animals;

   FOR indx IN 1 .. l_animal_ids.COUNT
   LOOP
      SELECT animal_name
        INTO l_name
        FROM plch_animals
       WHERE animal_id = indx;

      DBMS_OUTPUT.put_line (l_name);
   END LOOP;
END;
/

Oh my. This choice is similar to #1, in terms of the basic, flawed assumptions (the animal IDs happen to ascend in exactly the same sequence as an alphabetical ordering of the animal names).

But it's even worse because I fetch all the animal IDs via BULK COLLECT into an array, then when I loop through the array, I execute a single row fetch to get the name.

And then it's even worse worse because the only reason this works is that the ID values happen to align with names in alphabetical order. So the results "look" OK when the test is run with this pathetic set of test data, but in the real world? Ugh.

That's a waste of code, PGA memory and CPU cycles.

#3. Having Too Much Fun With Collections

DECLARE
   TYPE animal_ids_t IS TABLE OF plch_animals.animal_id%TYPE
      INDEX BY plch_animals.animal_name%TYPE;

   l_animal_ids   animal_ids_t;
   l_index        plch_animals.animal_name%TYPE;
   l_name         plch_animals.animal_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   FOR rec IN (  SELECT *
                   FROM plch_animals
               ORDER BY animal_name DESC)
   LOOP
      l_animal_ids (rec.animal_name) := rec.animal_id;
   END LOOP;

   l_index := l_animal_ids.FIRST;

   WHILE l_index IS NOT NULL
   LOOP
      DBMS_OUTPUT.put_line (l_index);

      l_index := l_animal_ids.NEXT (l_index);
   END LOOP;
END;
/

I very much enjoy collections (PL/SQL's version of arrays) and use them all over my code. And I especially appreciate string-indexed associative arrays, like I use above. But, really, there is such a thing as too much.

I use a cursor FOR loop to grab the rows from the animals table, and load the ID into my array, using the name as the index value. Then I use a WHILE loop to iterate through that array, simply displaying the index value. I never even use the ID!

It gets the job done, and sure performance will be fine unless you are looking at a very large volume, but this code is downright bewildering.

#4. SQL Simple

SELECT animal_name FROM plch_animals
 ORDER BY animal_name

Doesn't get much simpler than that. And if you need it inside PL/SQL....

#5. PL/SQL Simple

BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   FOR rec IN (SELECT animal_name FROM plch_animals
                ORDER BY animal_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.animal_name);
   END LOOP;
END;

Lessons Learned

1. Let SQL do the heavy-lifting, as much as possible (not that there was anything very "heavy" to lift in this exercise!)

2. Don't over-complicate matters.

3. Make sure your test data has enough volume and variety to truly exercise your algorithm.

4. If you find yourself thinking "Does it have to be this complicated?", almost certainly the answer is a resounding "No!" and you should take a step back, challenge your assumptions, and see how you can simplify your code.

And don't forget:

a. Follow me on Twitter: @sfonplsql
b. Subscribe to my YouTube channel: PracticallyPerfectPLSQL
c. Check out the PL/SQL home page: oracle.com/plsql

Tuesday, November 1, 2016

PL/SQL 101: Why can't I display a Boolean value with DBMS_OUTPUT.PUT_LINE?

DBMS_OUTPUT. PUT_LINE is the built-in procedure that PL/SQL developers use to display output on the screen.

Let's watch it do it's thing on LiveSQL:


So I displayed a string, a date, a date converted to a string, and a number. Cool. 

Now let's display a Boolean value (TRUE, FALSE or NULL):


Ouch! It did not like a Boolean value, that's for sure. But why not? To figure that out, we need to take a look at the specification of the DBMS_OUTPUT package.

That's easy in SQL Developer: just right-click and choose Popup Describe. 



Searching for "procedure put_line", I see:

create or replace package dbms_output authid definer as
...
  procedure put_line(a varchar2);

Huh. That's weird. It only accepts a string. So how could it display a number and a date with no problem, but then choke on a Boolean?

It all has to do with implicit conversions. Generally, Oracle Database in both SQL and PL/SQL will automatically and implicitly convert a value from one data type to another "when such a conversion makes sense." This matrix shows the implicit conversions that are supported, but I can summarize for you in terms of this post:

1. Dates, timestamps and numbers can be implicitly converted to strings.
2. Booleans cannot be converted to strings. 

In fact, the BOOLEAN datatype doesn't even appear in the matrix. That is probably in part because BOOLEAN is a PL/SQL-specific datatype; it's not supported at all in Oracle SQL.

Does that mean that it will never be possible for DBMS_OUTPUT.PUT_LINE to display a Boolean? Not at all. For example, the PL/SQL development team could add an overloading for PUT_LINE in the DBMS_OUTPUT package:

create or replace package dbms_output authid definer as
...
  procedure put_line(a varchar2);
  procedure put_line(a boolean);

And then they just have to figure out what to display. That's the "funny" thing about the Boolean values of TRUE and FALSE. They are in English. If you are using a different language, should the text displayed for a Boolean value change to the words for TRUE and FALSE in that language?

So many questions, so little time - but no matter how many questions there are, adding an overloading to this built-in package is well out of our control.

So what's a developer to do?

Well, first of all, please do not do this, over and over again throughout your code:


BEGIN
   IF my_boolean
   THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
   ELSIF NOT my_boolean
   THEN
      DBMS_OUTPUT.PUT_LINE ('FALSE');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('NULL');
   END IF;
END;

Instead, why not install a "Boolean Manager" in your environment that allows you to easily display Boolean values (and convert them from/to strings)?



A final note: we recommend that you avoid implicit conversions whenever possible, and tell Oracle Database exactly what you want, and how you want it done. A simple example demonstrating the benefit of being explicit has to do with displaying date values.

When I ask to display a date, as in:

BEGIN
   DBMS_OUTPUT.put_line (DATE '2016-10-31');
END;
/

31-OCT-16

Oracle Database uses the default NLS date format setting for my session to do the implicit conversion. The default format is DD-MON-YY, which honestly very few humans find helpful. But notice in particular that the time component is ignored.

Now let's try it again after changing the session date format:


ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
/

BEGIN
   DBMS_OUTPUT.put_line (DATE '2016-10-31');
END;
/

2016-10-31 00:00:00

So rather than assuming the session date format is what you hope or desire, make it explicit when you  ask to display a date, as in:

BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (DATE '2016-10-31'
      , 'YYYY-MM-DD HH24:MI:SS'));
END;
/

31-OCT-16

Monday, October 31, 2016

November CodeTalk Webcast: A View from the Trenches: Oracle Database Development at Moovit

Millions of people develop applications on top of Oracle Database. The most secure and optimized of those applications take full advantage of SQL and PL/SQL.

In our next CodeTalk webcast, I will interview Oren Nakdimon of Moovit, lead developer for the backend of this popular transit app, to find out just how he and his small team have made the most of PL/SQL, and how they manage their PL/SQL code base.


Oren gave an excellent presentation at OOW16 describing how they have been able to leverage Edition-Based Redefinition (EBR) to minimize downtime and improve delivery of new services to users. He mentioned briefly in that talk (a) how much he enjoys PL/SQL and (b) the features of the language he appreciates most.

Don't worry, Tom. He likes SQL an awful lot, too. :-)

So I thought it would be interesting to hear about how Moovit uses PL/SQL in more detail. That's what we will be discussing on November 16 starting at 10 AM Chicago time.

Real world PL/SQL. From a really sharp crew. I expect to learn a lot, and you will, too.

Don't miss it: register now!

Monday, October 24, 2016

Quick Guide to Some Sources for Naming Conventions for Oracle Database Development

I occasionally am asked about naming conventions for PL/SQL program units.

I did publish a document back in 2009 (link below), but there are also other sources for naming conventions from others, offering different ideas and excellent motivation for standardizing the way you write your code. I have collected links to them here.

The most important thing about naming conventions is BE CONSISTENT.

PL/SQL Naming Conventions and Coding Standards - Steven Feuerstein 2009

Coding Standards for SQL and PL/SQL - William Robertson

ORACLE-BASE / Tim Hall / Oracle Naming Conventions

Trivadis PL/SQL and SQL Coding Guidelines Version 2.0

Ask Tom on Naming Conventions

PL/SQL and SQL naming conventions

Oracle SQL and PL/SQL Coding Standards – Cat Herding for Dummies

Slideshare Presentation on PL/SQL Coding Conventions

Wednesday, October 19, 2016

Weird SQL Behavior? No. And the Importance of Table Aliases.

I received this email yesterday with a question about "weird SQL behavior".
I wrote a SQL delete statement with a select statement in its where clause. I made a mistake and forgot to create a column in the table that I used in the subquery. But the table from which I am deleting has a column with the same name. I did not get an error on compilation. Why not? There is no column with this name in this table in the where-clause. As a result I deleted all the rows in the table. 
That last sentence - "I deleted all the rows in the table." - has got to be one of the worst things you ever say to yourself as an Oracle Database developer. Well, OK, there are worse, like "I truncated a table in production accidentally". Still, that's pretty bad.

So is that "weird" SQL behavior? Should the DELETE have failed to compile? Answers: No and No. Let's take a look at an example to drive the point him clearly.

I create two tables:

CREATE TABLE houses
(
   house_id     INTEGER PRIMARY KEY,
   house_name   VARCHAR2 (100),
   address      VARCHAR2 (1000)
)
/

CREATE TABLE rooms
(
   room_id     INTEGER PRIMARY KEY,
   house_id    INTEGER,
   room_name   VARCHAR2 (100),
   FLOOR       INTEGER,
   CONSTRAINT rooms_house FOREIGN KEY (house_id) REFERENCES houses (house_id)
)
/

Then I populate them with data:

BEGIN
   INSERT INTO houses
        VALUES (1, 'Castle Feuerstein', 'Rogers Park, Chicago');

   INSERT INTO rooms
        VALUES (100, 1, 'Kitchen', 1);

   INSERT INTO rooms
        VALUES (200, 1, 'Bedroom', 2);

   COMMIT;
END;
/

OK, time to delete. I write the block below. Notice that my subquery selects the room_id from the houses table. There is no room_id column in houses, so the DELETE should fail to compile, right?

BEGIN
   DELETE FROM rooms
         WHERE room_id = (SELECT room_id FROM houses);

   DBMS_OUTPUT.put_line ('Deleted = ' || SQL%ROWCOUNT);
END;
/

Wrong! Instead, I see Deleted = 2. All the rows in the rooms table deleted. That's some pretty weird SQL, right? Wrong again!

Note: since there are no PL/SQL bind variables in the SQL statement, we don't need to talk at all about name capture in PL/SQL, but you should also be clear about that as well, so here's a link to the doc).

When the SQL engine parses this statement, it needs to resolve all references to identifiers. It does so within the scope of that DELETE statement. But wait, that DELETE statement has within it a sub-query.

So here's how it goes:

1. Does houses have a room_id column?
2. No. OK, does rooms have a room_id column?
3. Yes, so use that.
4. OK, well that essentially leaves us with "room_id = room_id"
5. All rows deleted.

It's easy to verify this flow. Let's add a column named "ROOM_ID" to houses:

ALTER TABLE houses ADD room_id INTEGER
/

Now, when I try to execute that same block of code that performs the delete, I then see Deleted = 0.

No rows were deleted, and that's because the value of houses.room_id is NULL in every row in the table.

The developer who sent me this email was confused and naturally thought that maybe there was something wrong or weird with SQL.

Now, don't get me wrong: Oracle SQL surely has its share of bugs. But I think that after 35 years, you can pretty well assume that for any basic, common statements, the language is pretty solid. So if you get confused about the result of a SQL statement you should:

First, make sure you understand how the language works.

Second, fully qualify all references inside your SQL statement.

Writing a SQL statement like this:

DELETE FROM rooms
 WHERE room_id = (SELECT room_id FROM houses);

Is akin to writing an arithmetic expression like this:

var := 12 * 15/ 3 - 27 + 100;

Believe this: the compiler NEVER GETS CONFUSED by code like this. Only us humans.

So with arithmetic expressions, you should always use parentheses to make your intent clear (and maybe fix a bug or two, as my parentheses do, below):

var := ((12 * 15) / 3) - (27 + 100);

and always fully qualify references to columns in your SQL statements, using table aliases, as in:

DELETE FROM rooms r
 WHERE r.room_id = (SELECT h.room_id FROM houses h);

This very simple step not only removes confusion, but also makes it much easier for developers "down the line" to maintain your complex SQL statements. It also reduces the chances for bugs to creep into said SQL statements.