Obsessed with Oracle PL/SQL

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

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

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

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

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

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

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?

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