Skip to main content

Wait, that's NOT a reserved word?

When it comes to PL/SQL puzzles via Twitter, I decided to change things up this week. I presented it as multiple choice this time.

Here's the puzzle:
After executing the code shown in the image what will be displayed on the screen (serveroutput is on!)?
a. "No dummy"
b. Unhandled VALUE_ERROR exception
c. Unhandled NO_DATA_FOUND exception
d. Compilation error
e. Your guess is as good as mine.

Before I unveil the amazing, mind-boggling answer....I will give you a moment to try to solve it yourself.
OK. So the first inclination you might have as regards the output from this block is, quite logically, "No dummy!".

After all, there are no rows in the dual table (or any other table for that matter) for which 1 is equal to 2.

So that SELECT-INTO is going to raise a NO_DATA_FOUND exception. No doubt about that at all.

And there's an exception handler for NO_DATA_FOUND (well, no_data_found, wait they are the same thing in PL/SQL! :-) ). So "No dummy" it is, right?

Wrong.

Now, a number of people in Twitterland also chose (d) Compilation error.

They reasoned that "no_data_found" and "pls_integer" are reserved words in PL/SQL. So if you try to use them for your own identifiers, the PL/SQL compiler slaps you down.

That's very solid reasoning....and it would be true if those were actually reserved words.

Now, IF is definitely a reserved word, so if I try to declare a variable with that name, nuh-uh:

NO_DATA_FOUND and PLS_INTEGER are not reserved words, which you can confirm with a query in your database:
That's right - I got a NO_DATA_FOUND exception searching for "NO_DATA_FOUND" as a reserved word. :-)

OK but to be completely honest with you, that view contains SQL keywords. Check Appendix D of the PL/SQL Language Reference for PL/SQL Reserved Words and Keywords.

So "no_data_found" and "pls_integer" are not reserved words, which means I can use them myself in my code. Which then means that any usage of those names in my block will be resolved to my variables or types or whatever they are, and not the pre-defined element in the PL/SQL language.

Specifically the "when no_data_found" handler will trap only the no_data_found exception that I declared in my code, which is not the same exception as that raised by the PL/SQL runtime engine.

In fact, the only way that handler could trap anything is if "RAISE no_data_found" was executed in this block.

I hope that makes sense. It is of course very confusing and even if "no_data_found" is not a reserved word, you should certainly avoid using it for your own identifiers in your code.

But if "no_data_found" is not a reserved word, what is it? Is it a keyword? Not even that. It doesn't appear in the D-2 table, but you should think of it as a keyword since the word on keywords is "You can use keywords as ordinary user-defined identifiers, but it is not recommended."

In fact, "no_data_found" is an exception declared in a special package called STANDARD. That package is owned by SYS and, along with DBMS_STANDARD, define many elements of the PL/SQL language, including exceptions like DUP_VAL_ON_INDEX and VALUE_ERROR and types like PLS_INTEGER.

You can view the package source as follows:
  SELECT line, text
    FROM all_source
   WHERE TYPE = 'PACKAGE' AND name = 'STANDARD' AND owner = 'SYS'
ORDER BY line
You can also view the package body, but it's not very helpful, except to reveal to you that the PL/SQL dev team has access to functionality that we do not. Which should not come as a surprise to anybody.

In that package specification, you will find lines of code like this:

At which point, I am sure you are now saying to yourself: "What? What is that number for NO_DATA_FOUND? 100? I thought the error code for this exception was -1403." Yeah, it's a rather strange outlier of an exception. Bottom line is that the ANSI standard error code for "no data found" is 100, so we go with that, but when the exception is raised, SQLCODE returns -1403.

Get over it.

And there you have it. Now you know that you, too, can write code that is so hard to read, so confusing, so downright strange that everyone will conclude that you must be a genius.

Since, however, neither you nor I are a genius, I suggest you never write code like this.

Comments

  1. time to start an Obfuscated PL/SQL Code Contest ?
    When I programmed in C i *loved* https://www.ioccc.org/

    ReplyDelete
    Replies
    1. I like the idea, Socrates. We did that several times on the Dev Gym (https://DevGym.oracle.com) for SQL. I am not exactly sure how well it would work in PL/SQL, but I will explore it further.

      Delete

Post a Comment

Popular posts from this blog

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 ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

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…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…