Tuesday, January 31, 2017

Find duplicate SQL statements with PL/Scope in 12.2

PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in your PL/SQL code.

You can do all sorts of amazing deep-dive analysis of your code with PL/Scope, answering questions like:
  • Where is a variable assigned a value in a program?
  • What variables are declared inside a given program?
  • Which programs call another program (that is, you can get down to a subprogram in a package)?
  • Find the type of a variable from its declaration.
  • Show where specific columns are referenced
  • Find all program units performing specific DML operations on table (and help you consolidate such statements)
  • Locate all SQL statements containing hints
  • Find all dynamic SQL usages – ideal for getting rid of SQL injection vulnerabilities
  • Show all locations in your code where you commit or rollback
And my latest favorite: Locate multiple appearances of same "canonical" SQL statement.

What does this mean and why does it matter? One great feature of PL/SQL is that the PL/SQL compiler automatically "canonicalizes" all static SQL statements in your code. This means that it upper-cases all keywords, removes extraneous white space, and so on. It standardizes the format of your SQL statements.

This standardization is important because it reduces the number of times that Oracle Database will "hard parse" your SQL statement when it is executed. That's because standardization of format raises the likelihood that SQL statements which "look" different (different case, line breaks, spaces) but are actually the same "under the surface".

So canonicalization of SQL can improve performance.

Now on to another benefit gained from this process: PL/Scope compares all the canonicalized SQL statements and assigns the same SQL_ID to matching statements. Consider the following two statements.

I turn on PL/Scope to gather both identifier and statement information. Then I compile two procedures. Clearly, they were written by two different developers on my team, with their formatting and naming standards. Sigh....welcome to the real world, right?

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

          employee_id = p_id;

   SELECT last_name
     INTO name_out
    WHERE employee_id = id_in;

Now let's analyze the PL/Scope data:

  SELECT signature, sql_id, text
    FROM all_statements
   WHERE object_name IN ('P1', 'P2')
ORDER BY line, col

517C7D44CC74C7BD752899158B277868 641rpxvq1qu8n 
DFD0209075761780F18552DE6661B4E7 641rpxvq1qu8n 

Brilliant! The signatures are different (no big surprise there; that's a value generated by PL/Scope that is guaranteed to be unique across all statements and identifiers). But notice that the SQL_IDs are the same - and the SQL statements are the same, too.

There you see the canonicalization at work.

Why I am so excited about this? One of the worst things you can do in your code is repeat stuff: copy/paste algorithms, magic values...and, most damaging of all, SQL statements.

If you repeat the same SQL statements across your application, it is much harder to optimize and maintain that code.

Now, with PL/Scope 12.2, we have an awesome and easy-to-use tool at our disposal to identify all duplicates of SQL. We can then decide which of those should be moved into functions (SELECTs) or procedures (non-query DML), so that the subprogram can be invoked in multiple places, and the SQL can be managed in one place.

Here's a query that tells you precisely where duplication of SQL occurs:

SELECT owner,
  FROM all_statements
 WHERE sql_id IN (  SELECT sql_id
                      FROM all_statements
                     WHERE sql_id IS NOT NULL
                  GROUP BY sql_id
                    HAVING COUNT (*) > 1)

Cool stuff.

Try it out at LiveSQL, our free, 24x7 playground for Oracle Database 12c Release 2 SQL and PL/SQL (and a code library).

And check out the extensive doc on PL/Scope, with lots of examples and insights.

Thursday, January 26, 2017

Players for Logic Annual Championship for 2016

The following players will be invited to participate in the Logic Annual Championship for 2016, currently scheduled to take place on 4 April.

The number in parentheses after their names are the number of championships in which they have already participated.

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

Name Rank Qualification Country
Pavel Zeman (2) 1 Top 50 Czech Republic
SteliosVlasopoulos (3) 2 Top 50 Belgium
Marek Sobierajski (1) 3 Top 50 Poland
mentzel.iudith (3) 4 Top 50 Israel
Vyacheslav Stepanov (3) 5 Top 50 No Country Set
James Su (3) 6 Top 50 Canada
Rytis Budreika (3) 7 Top 50 Lithuania
JasonC (3) 8 Top 50 United Kingdom
Cor (2) 9 Top 50 Netherlands
Köteles Zsolt (2) 10 Top 50 Hungary
Kuvardin Evgeniy (2) 11 Top 50 Russia
NickL (2) 12 Top 50 United Kingdom
Chad Lee (3) 13 Top 50 United States
NeilC (0) 14 Top 50 United Kingdom
TZ (1) 15 Top 50 Lithuania
D. Kiser (2) 16 Top 50 United States
ted (3) 17 Top 50 United Kingdom
MarkM. (3) 18 Top 50 Germany
Elic (3) 19 Top 50 Belarus
mcelaya (1) 20 Top 50 Spain
Sandra99 (3) 21 Top 50 Italy
tonyC (2) 22 Top 50 United Kingdom
seanm95 (3) 23 Top 50 United States
Talebian (2) 24 Top 50 Netherlands
richdellheim (3) 25 Top 50 United States
Arūnas Antanaitis (1) 26 Top 50 Lithuania
ratte2k4 (1) 27 Top 50 Germany
umir (3) 28 Top 50 Italy
Kanellos (2) 29 Top 50 Greece
NielsHecker (3) 30 Top 50 Germany
Andrii Dorofeiev (2) 31 Top 50 Ukraine
Mehrab (3) 32 Top 50 United Kingdom
JustinCave (3) 33 Top 50 United States
krzysioh (2) 34 Top 50 Poland
Stanislovas (0) 35 Top 50 Lithuania
Vladimir13 (1) 36 Top 50 Russia
danad (3) 37 Top 50 Czech Republic
RalfK (2) 38 Top 50 Germany
YuanT (3) 39 Top 50 United States
Mike Tessier (1) 40 Top 50 Canada
Vijay Mahawar (3) 41 Top 50 No Country Set
Eric Levin (2) 42 Top 50 United States
whab@tele2.at (1) 43 Top 50 Austria
puzzle1fun (0) 44 Top 50 No Country Set
Sartograph (1) 45 Top 50 Germany
tonywinn (1) 46 Top 50 Australia
dovile (0) 47 Top 50 Lithuania
Jeff Stephenson (0) 48 Top 50 No Country Set
craig.mcfarlane (2) 49 Top 50 Norway
Paresh Patel (0) 50 Top 50 No Country Set

Tuesday, January 24, 2017

Confused by your error backtrace? Check the optimization level!

The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (and similar functionality in the UTL_CALL_STACK package) is a tremendously helpful function. It returns a formatted string that allows you to easily trace back to the line number on which an exception was raised.

You know what else is really helpful? The automatic optimization performed by the PL/SQL compiler. The default level is 2, which does an awful lot of optimizing for you. But if you want to get the most out of the optimizer, you can ratchet it up to level 3, which then added subprogram inlining.

Unfortunately, these two wonderful features don't mix all that well. Specifically, if you optimize at level 3, then the backtrace may not point all the way back to the line number in your "original" source code (without inlining, of course).

Run this LiveSQL script to see the following code below "in action."

ALTER SESSION SET plsql_optimize_level = 2

   l_level   INTEGER;

   PROCEDURE inline_proc1
         DBMS_OUTPUT.put_line ('inline_proc1 handler');

         DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
   SELECT plsql_optimize_level
     INTO l_level
     FROM user_plsql_object_settings
    WHERE name = 'PROC1';

   DBMS_OUTPUT.put_line ('Opt level = ' || l_level);

      DBMS_OUTPUT.put_line ('inline handler');

      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);


ALTER SESSION SET plsql_optimize_level = 3



Opt level = 2
inline_proc1 handler
ORA-06512: at "STEVEN.PROC1", line 8

inline handler
ORA-06512: at "STEVEN.PROC1", line 15
ORA-06512: at "STEVEN.PROC1", line 8
ORA-06512: at "STEVEN.PROC1", line 25

Opt level = 3
inline_proc1 handler
ORA-06512: at "STEVEN.PROC1", line 25

inline handler
ORA-06512: at "STEVEN.PROC1", line 25
ORA-06512: at "STEVEN.PROC1", line 25

I hope to have an update from the PL/SQL dev team on this topic soon, but I wanted to make you aware of this in case you get all confused and frustrated.

Check your optimization level! Oh, how do you do that? Here you go:

SELECT p.plsql_optimize_level
  FROM user_plsql_object_settings p
 WHERE name = 'PROC1'

Wednesday, January 18, 2017

Players for PL/SQL Challenge Championship for 2016

The following players will be invited to participate in the PL/SQL Challenge Championship for 2016, currently scheduled to take place on 23 March at 14:00 UTC.

The number in parentheses after their names are the number of championships in which they have already participated.

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

Name Rank Qualification Country
SteliosVlasopoulos (13) 1 Top 50 Belgium
siimkask (16) 2 Top 50 Estonia
mentzel.iudith (16) 3 Top 50 Israel
li_bao (4) 4 Top 50 Russia
James Su (11) 5 Top 50 Canada
ivan_blanarik (10) 6 Top 50 Slovakia
NielsHecker (17) 7 Top 50 Germany
Rakesh Dadhich (8) 8 Top 50 India
Karel_Prech (6) 9 Top 50 No Country Set
Marek Sobierajski (1) 10 Top 50 Poland
Rytis Budreika (4) 11 Top 50 Lithuania
_tiki_4_ (9) 12 Top 50 Germany
krzysioh (5) 13 Top 50 Poland
Chad Lee (13) 14 Top 50 United States
João Borges Barreto (6) 15 Top 50 Portugal
Andrey Zaytsev (5) 16 Top 50 Russia
coba (1) 17 Top 50 Netherlands
patch72 (3) 18 Top 50 Netherlands
Kuvardin Evgeniy (2) 19 Top 50 Russia
VictorD (3) 20 Top 50 Russia
Vyacheslav Stepanov (15) 21 Top 50 No Country Set
Maxim Borunov (3) 22 Top 50 Russia
tonyC (2) 23 Top 50 United Kingdom
JustinCave (13) 24 Top 50 United States
Chase (2) 25 Top 50 Canada
Joaquin_Gonzalez (10) 26 Top 50 Spain
Pavel_Noga (4) 27 Top 50 Czech Republic
seanm95 (3) 28 Top 50 United States
syukhno (0) 29 Top 50 Ukraine
tonywinn (5) 30 Top 50 Australia
JasonC (1) 31 Top 50 United Kingdom
Andrii Dorofeiev (0) 32 Top 50 Ukraine
Sachi (1) 33 Top 50 India
ratte2k4 (0) 34 Top 50 Germany
Alexey Ponomarenko (1) 35 Top 50 No Country Set
PZOL (2) 36 Top 50 Hungary
Otto Palenicek (0) 37 Top 50 Germany
Jānis Baiža (10) 38 Top 50 Latvia
JeroenR (10) 39 Top 50 Netherlands
Rimantas Adomauskas (3) 40 Top 50 Lithuania
Henry_A (3) 41 Top 50 Czech Republic
Sherry (2) 42 Top 50 Czech Republic
ted (0) 43 Top 50 United Kingdom
MarkM. (0) 44 Top 50 Germany
YuanT (11) 45 Top 50 United States
kbentley1 (1) 46 Top 50 United States
swesley_perth (2) 47 Top 50 Australia
Talebian (3) 48 Top 50 Netherlands
mcelaya (1) 49 Top 50 Spain
berkeso (0) 50 Top 50 Hungary

Emulating a finally clause in PL/SQL

PL/SQL does not support a finally clause, as many other languages do, including Java. Here's a description of the finally block from the Java SE doc:
The finally block always executes when the try block exits. This ensures that the finally block is executed even if an unexpected exception occurs. But finally is useful for more than just exception handling — it allows the programmer to avoid having cleanup code accidentally bypassed by a return, continue, or break. Putting cleanup code in a finally block is always a good practice, even when no exceptions are anticipated.
The first thing to say regarding PL/SQL and finally is that the need for it in PL/SQL is likely less critical than in other languages, precisely because the PL/SQL runtime engine (and the underlying Oracle Database engine) does most of the clean up for you.

Any variables you declare, cursors you open, types you define inside a block are automatically cleaned up (memory released) when that block terminates.

Still, there are exceptions to this rule, including:

>> Changes to tables are not automatically rolled back or committed when a block terminates.

If you include an autonomous transaction pragma in your block, PL/SQL will "insist" (raise an exception at runtime) if you do not  rollback or commit, but that's different.

>> Elements declared at the package level have session scope. They will not be automatically cleaned up when a block in which they are used terminates.

Here's a very simple demonstration of that fact. I declare a cursor at the package level, open it inside a block, "forget" to close it, and then try to open it again in another block:

   CURSOR emps_cur
      SELECT *
        FROM employees;
END serial_package;

   OPEN serial_package.emps_cur;

   OPEN serial_package.emps_cur;

   OPEN serial_package.emps_cur;

ORA-06511: PL/SQL: cursor already open
ORA-06512: at "STEVEN.SERIAL_PACKAGE", line 5
ORA-06512: at line 2

Try it out yourself in LiveSQL.

Since there is no finally clause, you have to take care of things yourself. The best way to do this - and I am not claiming it is optimal - is to create a nested cleanup procedure and invoke that as needed.

Here we go - no more error when I attempt to open the cursor the second time.

   CURSOR emps_cur
      SELECT *
        FROM employees;
END serial_package;

   PROCEDURE cleanup
      /* If called from exception section log the error */
      IF SQLCODE <> 0
         /* Uses open source Logger utility:
               https://github.com/OraOpenSource/Logger */
         logger.log_error ('use_packaged_cursor');
      END IF;
      IF serial_package.emps_cur%ISOPEN
         CLOSE serial_package.emps_cur;
      END IF;
   END cleanup;
   OPEN serial_package.emps_cur;
      /* Clean up but do not re-raise (just to show that you might want
         different behaviors for different exceptions). */


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

(also available in LiveSQL)

Now, I am not, repeat NOT, claiming that this is as good as having a finally clause. I am just saying: this is how you can (have to) achieve a similar effect.

Tuesday, January 10, 2017

Rely on the Wisdom of Friends and Strangers - Ask for Help!

[originally published as part of my ODTUG Confessions of a Quick and Dirty Programmer column]

Surely the best way to write a ton of quick and dirty code, and never, ever stop writing such code, is to be firmly convinced that you know all there is to know about something/anything, and stop learning from others.

Consider me - and PL/SQL.

There seems to be an impression among PL/SQL developers "out there" that I know everything there is to know about the PL/SQL language and (even more amazing) Oracle technology more generally.

Ha, I say, ha!

Certainly, I know more about this language than almost every other human being on the planet. Yet I am reminded, in just about every training and presentation I do, of how much I don't know and how much I can learn from others. For, when it comes to programming, the gap between "book knowledge" and "experience knowledge" is vast. It's one thing to know about syntax and features; it is quite another thing to know how to apply those features.

Accepting the limitations of experience, though, is just one part of the escape from smug belief in one's own "know-it-all-ness." Another key ingredient to developing a deep humility towards one's specialty is visibility. The more one's peers see, read and try to use your code, the more weaknesses are exposed in that code and your expertise.

Of late, the most intense and, well, challenging reminder of my limitations is the PL/SQL Challenge.

I decided back in 2010 to build a database of quiz questions on PL/SQL, and then create a website around that database to offer my daily quiz. Gosh, that doesn't sound so difficult, does it? And certainly the easiest part of that would be writing the quizzes. Surely I know how to do that well!
Or not, as the case may be.

Well, I wrote over 600 questions, and then I worked with my friends at then-Sumneva (Dimitri Gielis, John Scott and Paul Broughton) to design and build the website. I was also assisted beyond easy description by Finn Ellebaek Nielsen, one of the smartest and most knowledgeable Oracle technologists I have ever met.

We launched the site in April 2010 and since then I have been taught by PL/SQL Challenge players just how difficult it is to write unambiguous and 100% accurate quizzes. All too often, players have delved into the nooks and crannies around my questions and answers -  and discovered outright errors; exceptions to the "rule" (that I state or assume in my question); features or behavior unknown to me; assumptions I have been making about PL/SQL that are wrong.

Some players have commented on how addictive the PL/SQL Challenge - I sometimes wonder if they are not getting addicting to proving me fallible!

This widespread laser-like focus on what I am doing has had a noticeable impact. Since I could not afford to allow mistakes to continue to creep into the quiz process, I instituted changes in process as follows:

1. Complete a checklist for every quiz. I no longer have to remember all the key steps in my head (such as "Never use words like never and always. Absolutes are very dangerous.").

2. Generate a verification script from the question and choices, to validate all code examples and claims made in the answers. After all, if it's not tested, you don't know for sure that it is correct.

3. Have at least one PL/SQL developer (not anadmin and not someone who plays the daily quiz) review each quiz.

Does that sound like common sense? Absolutely. Does that sound exactly like what we should all be doing with our application code? Absolutely.

So in this case, I not only learned directly from others (players in the PL/SQL Challenge), but I also learned from this "other" scenario (publishing quizzes). That is, the steps required to ensure high quality quizzes are (some of) the same steps I should be taking to ensure high quality application code.

Learn From Others Even If They Are Not Programmers

Programmers are like any other defined group of human beings. We have our own language (mostly acryonyms), our own diet (Diet Coke and chips), our own forms of entertainment ("Wow, what a cool algorithm!"). That's all well and good, as long as we don't develop the attitude that we have nothing to learn from non-programmers. This attitude of "exceptionalism" (we are different - with an unspoken implication of  "better" - than others; they have nothing to teach us) is both arrogant and self-defeating.

Last year, we decided to go "off the grid" by installing a photovoltaic array of solar panels on the roof of our house, and attaching that array to a whole bunch of batteries. I got a very reasonable quote from a person who claimed to know what he was doing. And he did it - he installed 12 180kw panels and 24 batteries, hooked it all together, flipped the switch - and we had electricity from the sun. Here's a picture of the inside of the PV "shed" in which the batteries were located:

So we were happy. We had power from the sun. A few weeks later, another electrician (Noel) came to the house to install some light fixtures. He asked to see what the first electrician (Jose) had done, since he also does this work.

He went into the PV shed - and came out angry. He felt that Jose had done some very sloppy and dangerous work. He said that the installation did not follow code, and he pointed out that this fuse:

said on it "Audiophone." It was a fuse for a car stereo system, definitely not the kind of fuse needed to handle multiple kilowatts of energy. I was, to say the least, dismayed. I thought about demanding that Jose fix his earlier work, but realized that Jose clearly didn't know what he was doing. Why would I expect to be able to fix the problem he introduced?

[Hmmm. Now there's an interesting thought to apply to fixing bugs in software!]

Instead, I asked Noel to refactor the existing implementation. And when he was done, the wall of the PV shed looked like this:

Notice how neat and clean it is; all the electrical wires and now enclosed, so they cannot be easily damaged. And everything has a label - in fact, when I open the door of the gray box, I see:

It wasn't until several weeks after Noel had finished his work that I realized that:

1. Jose's approach was a classic "quick and dirty" job that was cheap but impossible to maintain and likely to break or cause problems.

2. Noel's approach represented a "best practices" style in programming: encapsulate or hide the details of the implementation; document the architecture and key features of the system so that everyone (including the original "author") can come back later and understand what is going on.

How delightful to learn that the same principles that drive best practice programming also apply to electrical work! Once you can see this (and surely those same principles apply to many other endeavors), your eyes will be open to so many more opportunities for learning how to improve your code.

You Can't Learn If You Don't Ask

When you are stuck trying to sort out a bug, when you are not sure about how to solve a problem or implement an algorithm, when you wonder if there might be a new feature of Oracle that could help you, ask someone for advice or help.

It is much, much harder to learn if you don't ask for help, if you don't actively seek learning opportunities.

If you are one of those unfortunate programmers who mostly codes in isolation (a team of one, or a team of more than one, but with very little interaction), you might not have someone to turn to for help.

In that case, I suggest that when you are stuck on a problem, write yourself an email, explaining the problem and what you see as possible solutions. Simply getting that information out of your head will change the way you look at it and make it easier to find a solution.

The bottom line, though, is that no matter how much we know, or think we know, we can always learn more and different from others. Leverage that fact or you will be stuck in a rather deep rut constructed with your own limitations.

Wednesday, January 4, 2017

12.2: Avoid hard-coding maximum length of VARCHAR2 (and more)

Starting with Oracle Database 12c Release 2 (12.2), we can now use static expressions* where previously only literal constants were allowed. Here are some examples (also available in this LiveSQL script):

   c_max_length constant integer := 32767;
   SUBTYPE maxvarchar2 IS VARCHAR2 (c_max_length);

   l_big_string1 VARCHAR2 (pkg.c_max_length) := 'So big....';
   l_big_String2 pkg.maxvarchar2 := 'So big via packaged subtype....';
   l_half_big VARCHAR2 (pkg.c_max_length / 2) := 'So big....';
   DBMS_OUTPUT.PUT_LINE (l_big_string1);
   DBMS_OUTPUT.PUT_LINE (l_big_string2);

As you can see from this code, static expressions can now be used in subtype declarations.

The definition of static expressions is expanded to include all the PL/SQL scalar types and a much wider range of operators. Character operands are restricted to a safe subset of the ASCII character set. Operators whose results depend on any implicit NLS parameter are disallowed

Expanded and generalized expressions have two primary benefits for PL/SQL developers:
  • Programs are much more adaptable to changes in their environment
  • Programs are more compact, clearer, and substantially easier to understand and maintain
* A static expression is an expression whose value can be determined at compile time. This means the expression cannot include character comparisons, variables, or function invocations. An expression is static if it is any of the following:
  • the NULL literal
  • a character, numeric, or boolean literal
  • a reference to a static constant
  • a reference to a conditional compilation variable begun with $$ 
  • an operator is allowed in static expressions, if all of its operands are static, and if the operator does not raise an exception when it is evaluated on those operands
Read more in the doc

Tuesday, January 3, 2017

Learn to hate repetition - a lesson from APEX LOVs

If I was forced to pick just one piece of advice any developer should follow when writing code it would be: 

Avoid Repetition!
a.k.a, DRY - Don't Repeat Yourself
a.k.a., SPOD - Single Point of Definition

When the same code (business rule, formula, "magic" value, SQL statement, etc.) appears in more than one place, you create opportunities for bugs to creep into your code. How? You fix the problem in one place, but what about all the other places?

Here's an example of repetition in the PL/SQL Challenge APEX application and how I was able to get rid of the redundancy. 

I needed to add an LOV (list of values) to an item. I soon discovered that we had three LOVs already defined that were very similar to what I needed:

Now, just looking at the names of those LOVs made me shudder. They differ only by what appears to be the page number on which they are used. That didn't make much sense to me. So I drilled down in an attempt to gain clarify, and found that I was, indeed, correct. The only differences between these LOVs was the use of a different page item in the WHERE clause.

I had been using APEX for well over a year now by this time, but I still consider myself a novice and certainly lack an understanding of many of the nuances and limitations of the tool. Still, my quick glance at this situation had me thinking as follows:

1. It really would be better if page-specific item references were not stuck inside a named LOV. These LOVs do not "live" inside a single page and can, theoretically, be used across an entire application.

2. Surely there's got to be a way to generalize the query so that I can have a single LOV that can be used in all these locations.

So I clicked on the link in one of the LOVs to get some help and found:

Well, gee, that looks like a very useful approach. So I created a new LOV that does not contain the page number in its name, and does not contain a hard-coded page item reference in the query:

Now the only requirement for using this LOV is that the name of the item contain the domain ID is of the form:


where NNN is the page number. So it's not quite completely generic, but it's a lot closer than before, and I was able to replace three LOVs with just one.

Goodbye (the worst of the) hardcoding, goodbye repetition!

And another nice reminder of how easy it is to build and execute dynamic SQL statements via PL/SQL. And you can see here, I changed a static query to a PL/SQL block that returns a string. That string will then be executed by the APEX engine via an EXECUTE IMMEDIATE call.

Note also that even though I concatenate text to put together my where clause, I do not introduce a SQL injection vulnerability. The :app_page_id bind variable is set by APEX itself. And the end result of the concatenation is a string that contains a bind variable.


Oracle Application Express (v5.1 was just released last month!)
Get Rid of Hard Coding in PL/SQL (a Practically Perfect PL/SQL YouTube playlist)