Skip to main content


Showing posts from January, 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 referencedFind all program units performing specific DML operations on table (and help you consolidate such statements)Locate all SQL statements containing hintsFind all dynamic SQL usages – ideal for getting rid of SQL injection vulnerabilitiesShow 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 …

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!

NameRankQualificationCountryPavel Zeman (2)1Top 50Czech RepublicSteliosVlasopoulos (3)2Top 50BelgiumMarek Sobierajski (1)3Top 50Polandmentzel.iudith (3)4Top 50IsraelVyacheslav Stepanov (3)5Top 50No Country SetJames Su (3)6Top 50CanadaRytis Budreika (3)7Top 50LithuaniaJasonC (3)8Top 50United KingdomCor (2)9Top 50NetherlandsK√∂teles Zsolt (2)10Top 50HungaryKuvardin Evgeniy (2)11Top 50RussiaNickL (2)12Top 50United KingdomChad Lee (3)13Top 50United StatesNeilC (0)14Top 50United KingdomTZ (1)15Top 50LithuaniaD. Kiser (2)16Top 50United Statested (3)17Top 50United KingdomMarkM. (3)18Top 50GermanyElic (3)19Top 50Belarusmcelaya (1)20Top …

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."

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!

NameRankQualificationCountrySteliosVlasopoulos (13)1Top 50Belgiumsiimkask (16)2Top 50Estoniamentzel.iudith (16)3Top 50Israelli_bao (4)4Top 50RussiaJames Su (11)5Top 50Canadaivan_blanarik (10)6Top 50SlovakiaNielsHecker (17)7Top 50GermanyRakesh Dadhich (8)8Top 50IndiaKarel_Prech (6)9Top 50No Country SetMarek Sobierajski (1)10Top 50PolandRytis Budreika (4)11Top 50Lithuania_tiki_4_ (9)12Top 50Germanykrzysioh (5)13Top 50PolandChad Lee (13)14Top 50United StatesJo√£o Borges Barreto (6)15Top 50PortugalAndrey Zaytsev (5)16Top 50Russiacoba (1)17Top 50Netherlandspatch72 (3)18Top 50NetherlandsKuvardin Evgeniy (2)19Top 50Ru…

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, th…

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 …

My nine most popular posts in 2016

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):

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS c_max_length constant integer := 32767; SUBTYPE maxvarchar2 IS VARCHAR2 (c_max_length); END; / DECLARE 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....'; BEGIN DBMS_OUTPUT.PUT_LINE (l_big_string1); DBMS_OUTPUT.PUT_LINE (l_big_string2); END; /
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 res…

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, cor…