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!

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

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.

12.2 Helps You Manage Persistent Code Base w/New Deprecate Pragma

"Persistent code base"? What's that?

Well, I suppose I could have said "aging" or "legacy" code base, but that doesn't capture my point, which starts with:

If I were a Javascript programmer, I would probably be excited if my code lasted more than a year.

As an Oracle Database developer, though, it is not unreasonable to expect that my code will be in production for years, perhaps decades.

Now, that's persistent code. And why is that?

Because, well, DATABASE.

The database is the repository for your enterprise. Certainly it contains the data, and if you fully leverage the database properly, it will also contain your business logic.

And while it is not all that painfully disruptive to rewrite your UI code, it can be business-threatening to do any of the following:

• Switch your database technology
• Upgrade your database software too quickly
• Rewrite your business logic in a new scripting language 

So your database - data and associated code - tends to be the most stable layer in your stack, with upgrades applied with great care, and code maintained for many years.

And "maintained" could mean:

• True legacy mode: no further enhancements, critical bug fixes only. This will always be true for at least a part of your code base, and I will not address it further in this post.
• Actively used and enhanced: this is where the action is. Sure, lots of the code has been around for years, and will continue to be. Most of it works great and doesn't need to be touched. But then there are enhancements, bug fixes, new features.

Oh, and, inevitably, deprecation. Which is the topic of this blog post.

Many things happen to code that lasts a long, and one of them is that we come up with better ways to do things. The "better" part could be a better name for a subprogram, a seriously modified parameter list, or an entirely new implementation.

And, of course, what you'd really like to do is immediately get rid of the "old stuff" and make sure everyone's program units, across the entire application, use only the cool, new stuff.

Sadly, that's not always possible. Sure you can send out an email:

Hey, folks, listen up! Don't use pkg_a.proc_b anymore. We've got a much better version in pkg_c.proc_d.

But there's no guarantee that anyone (or, more to the point: everyone) will switch over to the new iteration. And since a published, in-use API is a sort of contract you signed with users, you can't just force them to change (usually).

So instead, the old stuff sticks around and might even get used again by a careless developer - even if you add a comment like:

PACKAGE pkg_a AUTHID DEFINER
IS
   /* DON'T USE THIS! Use pkg_c.proc_d instead! */
   PROCEDURE proc_b;

Wouldn't it be nice if there was a way to document deprecated functionally, so that when your code is compiled, you would be notified (and could even have it flagged as a compile error) that you are using out-of-date stuff?

Yes, that sure would be lovely.

[Pretend you are now listing to a Drum Roll....]

Welcome to PL/SQL 12.2 and the DEPRECATED pragma.

You want to notify developers that proc_b is out of date and proc_d should be used instead?

This is now what you do:

PACKAGE pkg_a AUTHID DEFINER
IS
   PROCEDURE proc_b;
   PRAGMA DEPRECATE (proc_b,
      'pkg_a.proc_b deprecated. Use pkg_c.proc_d instead.');

Looks really similar to the comment version, doesn't it? So what, then, does this pragma do for you? Not much, if that's all you do.

But let's suppose that you've decided your team should "step it up" in terms of code quality and overall professionalism.

As a part of that initiative, you are going to take advantage of PL/SQL's compile-time warnings. You go into SQL Developer's preferences, type "compile" in the search field, and find this:

Yep, as suspected, you are not currently utilizing compile-time warnings. So let's start by enabling all of them:

Then when I compile pkg_a, I see this warning:

The compiler is simply notifying me that proc_b has been marked as deprecated. That's fine. But what happens when I try to use this deprecated subprogram?

I am now "on notice" for using a subprogram that should not be used.

Well, now are you thinking to yourself: "Big deal. That's easy to ignore."

True. But we can take things a step further: I can tell PL/SQL to treat that warning as an error:

ALTER SESSION SET plsql_warnings='ERROR:(6020)'
/

When I do this and compile my program unit, suddenly compilation fails. The warning (PLW-06020) has been transformed into an honest-to-goodness compile error (PLS-06020).

Of course when I do that, I have declared that all subprograms and program units (aka, packages) declared as deprecated via the pragma are no longer deprecated. They are "gone", off-limits, unusable.

In other words, you can easily and quickly (well, depending on how much code you've got) identify all program units still relying on deprecated functionality.

Just convert that warning to an error, then recompile your schema (or schemas) and see which ones end up invalid. As in:

ALTER SESSION SET plsql_warnings='ERROR:(6020)'
/

BEGIN 
   DBMS_UTILITY.COMPILE_SCHEMA (
      schema          => USER /* Or another */,
      compile_all     => TRUE /* the default */,
      reuse_settings  => FALSE /* the default */);
END;
/

As PL/SQL code bases grow and evolve over time - and they will, 'cause they are not "going anywhere" & play a mission critical role in your applications - structured, reportable deprecation will become more and more important....

So take advantage of this fine, new Oracle Database 12c Release 2 feature as soon as you can.

Which is today, if you sign up for the Oracle Database Exadata Express Cloud Service. Catchy name, right?

And no, you should resist the temptation to use an acronym. ADEECS just doesn't do it. :-)

"Give me a project I can work on, Steven!" OK, here you go....

Probably once a month, I receive an email from a programmer who either wants to tune up their Oracle Database programming skills (SQL and PL/SQL, primarily) or wants to help me or the community in some way.

Here's the latest, via LinkedIn Messages:

My friends and I are just in love with Oracle PL/SQL and the whole concept of database development and database design. This is like a religion for us. Can you you provide us some work to do?

I don't have a handy set of projects for people to work on, especially now that I am back with Oracle.

But there are plenty of opportunities for you out there, especially if you'd like to help build community tools. So here's a short list. I hope readers will offer others via the Comments section.

1. OraOpenSource - github.com/oraopensource

A project of oraopensource.com, led by Martin Giffy D'Souza, this repo offers an ever-growing set of utilities, with lots of room for expansion.




2. utPLSQL - github.com/utplsql

utPLSQL, which I originally built in 1999, is one of the most popular xUnit variants (JUnit, NUnit, etc.) for PL/SQL developers. It has recently moved to Github and is in the process of being revamped for a big V3 release. It's all written in PL/SQL!

3. oddGen - oddgen.org

oddgen is an Oracle SQL Developer extension to invoke dictionary-driven code generators.



4. Alexandria Library - https://github.com/mortenbra/alexandria-plsql-utils

This library is a collection of various utility packages for PL/SQL, as well as links to useful libraries hosted and maintained elsewhere.

Two more great resources, though you have to come up with your own project ideas to use them:

1. I encourage you sign up for an apex.oracle.com workspace. You can then build your own websites using  Oracle Application Express, using nothing but SQL, PL/SQL and some Javascript if you want to get fancy.

2. Use LiveSQL.oracle.com to play around with SQL and PL/SQL, as well as take advantage of an ever-growing library of scripts.

Another reminder of the elegance and brevity of CASE expressions

Building a script for an upcoming PL/SQL Challenge quiz, I wrote a nested procedure as follows:

   PROCEDURE show_cursor_status
   IS
   BEGIN
      IF all_in_one_cur%ISOPEN
      THEN
         DBMS_OUTPUT.put_line ('all_in_one_cur is still open');
      ELSE
         DBMS_OUTPUT.put_line ('all_in_one_cur is closed');
      END IF;

      IF department_cur%ISOPEN
      THEN
         DBMS_OUTPUT.put_line ('department_cur is still open');
      ELSE
         DBMS_OUTPUT.put_line ('department_cur is closed');
      END IF;

      IF employee_cur%ISOPEN
      THEN
         DBMS_OUTPUT.put_line ('employee_cur is still open');
      ELSE
         DBMS_OUTPUT.put_line ('employee_cur is closed');
      END IF;
   END;

Nothing wrong with that, of course. Works just fine.

But there's a lot of repetition. I hate that. I like to normalize my code. And when the repetitive code is based on an IF statement, I immediately think of CASE - expressions:

   PROCEDURE show_cursor_status
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            'all_in_one_cur is '
         || CASE WHEN all_in_one_cur%ISOPEN THEN 'open' ELSE 'closed' END);

      DBMS_OUTPUT.put_line (
            'department_cur is '
         || CASE WHEN department_cur%ISOPEN THEN 'open' ELSE 'closed' END);

      DBMS_OUTPUT.put_line (
            'employee_cur is '
         || CASE WHEN employee_cur%ISOPEN THEN 'open' ELSE 'closed' END);
   END;

But, wait, still....there's more repetition: each of those CASE expressions. So I can use a nested function to encapsulate all of that, and I am left with:

   PROCEDURE show_cursor_status
   IS
      FUNCTION cursor_state (isopen_in IN BOOLEAN)
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN CASE WHEN isopen_in THEN 'open' ELSE 'closed' END;
      END;
   BEGIN
      DBMS_OUTPUT.put_line (
         'all_in_one_cur is ' || cursor_state (all_in_one_cur%ISOPEN));

      DBMS_OUTPUT.put_line (
         'department_cur is ' || cursor_state (department_cur%ISOPEN));

      DBMS_OUTPUT.put_line (
         'employee_cur is ' || cursor_state (employee_cur%ISOPEN));
   END;

Ah....much better. No code repetition. Of course, in a subprogram this small, repetition is not too deadly a problem. Still, programs tend to get more complex over time, not simpler.

So assuming you (I) will be coming back to this code in the future, making sure that any particular piece of logic is implemented in just once place greatly reduces the cost and complexity of maintenance going forward, and also reduces the chance of introducing bugs.

Follow Up

I invited readers (via Twitter) to improve upon my code, and BluShadow came through with:

PROCEDURE show_cursor_status
IS
   PROCEDURE cursor_state (cur_name IN VARCHAR2, isopen_in IN BOOLEAN)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            cur_name
         || ' is '
         || CASE WHEN isopen_in THEN 'open' ELSE 'closed' END);
   END;
BEGIN
   cursor_state ('all_in_one_cur', all_in_one_cur%ISOPEN);
   cursor_state ('department_cur', department_cur%ISOPEN);
   cursor_state ('employee_cur', employee_cur%ISOPEN);
END;

I love people who think logically - except when they expose my illogic

Received this via email today:

I have just found the PL/SQL Challenge and I am eager to get started. Under Instructions on the Quiz Introduction page is something to which I would like to call your attention. 

"For most quizzes, however, it is possible that more than one choice is correct, that all choices are correct, or that none of the choices are correct. You must check at least one box from those offered before you can submit your answer." 

How might one check a box and then submit if none of the choices are correct?

On first reading, I groaned. Really? Did I really leave a big hole like that in our quiz-taking process or - not quite as alarming - in my text? Is that a contradiction?

No! No! No!

There is an explanation which leaves me firmly on the right side of Logic.

Can you see what it must be? 

I will post this first as a small logic puzzle via Twitter, then I will update this post in a day or two with my answer.

A day goes by.

Another day goes by.

Marcus solves the puzzle (see comments).

Here's the answer:

Nowhere in the text do I say that a "box" on the page must correlate to one of the choices. In fact, there is a box (a selection you can make) that is explicitly....

So that's the solution to the logic puzzle.

But Chris also makes a great point in the comments below that an explanation should not be needed for explanatory text. :-) So I am going to fix that text.

And that makes for a nice bonus to this post - I improve my application!

Execution of DDL in PL/SQL commits TWICE: before and after the statement

You'd think that after working with Oracle Database and PL/SQL since 1990, I'd know everything.

Ha. Not so. :-)

Of course, there are always the new features, such as those coming in 12.2 (I will be blogging about those extensively in the coming months).

But even for features that have been in the language for decades, I still encounter small gaps in my knowledge.

For example, I had long known that when you execute a DDL (data definition language) statement in PL/SQL (which must be done as dynamic SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE /EXECUTE) a commit is executed implicitly after the statement.

What I'd somehow missed was that a commit is also performed before the DDL statement is executed. So that is the point of this post:

Oracle Database issues a commit before a DDL statement is executed, and then afterwards as well.

You can see this behavior in action in the script below, which is generated from a recent PL/SQL Challenge quiz and can be run directly in LiveSQL.  All four blocks result in "Count=0" being displayed, because the effect of the DELETE is committed before the DDL statement is executed. Therefore, even when an error is raised when the DDL statement is run, the data's gone.

CREATE TABLE plch_data (n NUMBER)
/

BEGIN
   INSERT INTO plch_data
        VALUES (100);

   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE plch_show_count
IS
   l_count   INTEGER;
BEGIN
   SELECT COUNT (*) INTO l_count FROM plch_data;

   DBMS_OUTPUT.put_line ('Count=' || l_count);
END;
/

DECLARE
   l_count   INTEGER;
BEGIN
   DELETE FROM plch_data;

   EXECUTE IMMEDIATE 'create table plch_data (n number)';

   plch_show_count;
EXCEPTION
   WHEN OTHERS
   THEN
      plch_show_count;
END;
/

DECLARE
   l_count   INTEGER;
BEGIN
   DELETE FROM plch_data;

   EXECUTE IMMEDIATE 'create table plch_data2 (n number)';

   plch_show_count;
EXCEPTION
   WHEN OTHERS
   THEN
      plch_show_count;
END;
/

DECLARE
   l_count   INTEGER;
BEGIN
   DELETE FROM plch_data;

   EXECUTE IMMEDIATE 'create table plch_data (n number)';

   ROLLBACK;
   plch_show_count;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      plch_show_count;
END;
/

DROP TABLE plch_data2
/

DECLARE
   l_count   INTEGER;
BEGIN
   DELETE FROM plch_data;

   EXECUTE IMMEDIATE 'create table plch_data2 (n number)';

   ROLLBACK;
   plch_show_count;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      plch_show_count;
END;
/

DROP TABLE plch_data2
/

DROP TABLE plch_data
/

DROP PROCEDURE plch_show_count
/