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 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 f

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 Ku

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 / CREATE OR REPLACE PROCEDURE proc1 IS l_level INTEGER;

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 Ryt

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

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 thin

My nine most popular posts in 2016

It's an interestingly varied list of posts, with interest in table functions shining brightly and a PL/SQL joke thrown in for good measure. I hope to come up with more programming jokes and brain teasers this year! Table Functions, Part 1: Introduction and Exploration Recommendations for unit testing PL/SQL programs Table Functions, Part 5a: An introduction to pipelined table functions Dealing with "PLS-306: Wrong number or types of arguments" across schemas PL/SQL 101: Why can't I display a Boolean value with DBMS_OUTPUT.PUT_LINE? Lint Checkers for PL/SQL The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts PL/SQL Programming Joke #1: Don't Use Packages, Says My DBA Most important PL/SQL coding standards?

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

Starting with Oracle Database 12 c  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. Operat

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 f