Logic Reigns in the Oracle Dev Gym Logic Championship

Logic is at the very heart of programming, so we complement our quizzes on SQL, PL/SQL and so on with a weekly Logic tournament. And then at the end of the year, the top 50 ranked players qualify for our annual championship.

The following players will be invited to participate in the Logic Annual Championship for 2018, currently scheduled to take place on 19 February.

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!

NameRankStelios Vlasopoulos (5)1Pavel Zeman (4)2mentzel.iudith (5)3Ludovic Szewczyk (1)4James Su (5)5Chad Lee (5)6Tony Winn (3)7Rytis Budreika (5)8ted (5)9Kanellos (4)10Cor van Berkel (4)11K├Âteles Zsolt (4)12Vijay Mahawar (5)13RalfK (4)14pjas (1)15Mike Tessier (3)16seanm95 (5)17NickL (4)18Michal P. (1)19Eric Levin (4)20Sandra99 (5)21Mehrab (5)22JasonC (5)23Talebian (4)24NielsHecker (5)25richdellheim (5)…

Time for another Dev Gym PL/SQL Championship!

A new year has arrived, and that means that it's time (or will soon be time) for the PL/SQL Challenge Championship, when up to fifty top players from last year's PL/SQL tournament quizzes compete for top honors.

The following players will be invited to participate in the PL/SQL Challenge Championship for 2018, currently scheduled to take place on 26 February (hey, it takes some time to put together five advanced quizzes without any mistakes in them!).

The number in parentheses after their names are the number of championships in which they have already participated (note: from 2010 through 2013, we held quarterly championships for our then daily PL/SQL quiz!).

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

NameRankStelios Vlasopoulos (15)1mentzel.iudith (18)2Tony Winn (7)3NielsHecker (19)4Andrey Zaytsev (7)5patch72 (5)6Ivan Blanarik (12)7siimkask (18)8Rakesh Dadhich (10)9Rytis Budreika (6)10Vyacheslav Stepanov (17)11li…

Working With JSON Arrays in PL/SQL

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks.

In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods.

Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods.

Here are the methods you are most likely to use:

A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL:

Error Handling Behavior

By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not raised back to your block.

If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value greater than 0.

Array I…

PL/SQL 101: Defining and managing transactions

If you've got a read-only database, you don't have to worry about transactions. But for almost every application you're ever going to build, that is not the case. Therefore, the concept and managing of transactions is central to the success of your application.

A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are. A transaction implicitly begins with any operation that obtains a TX lock:
When a statement that modifies data is issued (e.g., insert, update, delete, merge)When a SELECT ... FOR UPDATE statement is issuedWhen a transaction is explicitly started with a SET TRANSACTION statement or the DBMS_TRANSACTION package Issuing either a COMMIT or ROLLBACK statement explicitly ends the current transaction.

This post reviews how to define, manage and control the transactions in your application with the following statements and features:
Commit and RollbackSavepointsAuto…

Reduce the volume of PL/SQL code you write with these tips

I'm not known for being concise. I'm best known in the world of Oracle Database for my "magnum opus" Oracle PL/SQL Programming, which checks in at 1340 pages (the index alone is 50 pages long).

But I've picked up a few tips along the way for writing PL/SQL code that is, well, at least not as long, as verbose, as it could have been. And certainly shorter than my books. :-)

You probably have some ideas of your own; please offer them in comments and I will add them to the post.

Qualified Expressions (new to 18c)

In the bad old days before Oracle Database 18c was released (and is now available for free in its "XE" form), if you wanted to initialize an associative array with values, you had to do in the executable section as follows:

DECLARE TYPE ints_t IS TABLE OF INTEGER INDEX BY PLS_INTEGER; l_ints ints_t; BEGIN l_ints (1) := 55; l_ints (2) := 555; l_ints (3) := 5555; FOR indx IN 1 .. l_ints.COUNT …

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today:
In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer

Quick Answer
Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption.Test to see if that's fast enough (likely will be for many cases).If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory. Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification.Don't put anything in the collection you don't need. [from Giulio Dottorini]Remember: each session that runs this code will use that amount of memory.Background

When you use BULK COLLECT, you retrieve more than row with each fetch, reducing context switchi…

Why won't MULTISET work for me?

I recently got an email from an Oracle Database developer who was trying to get the MULTISET operator to work in his code.

He'd created nested tables of records and found that MULTISET UNION would work but MULTISET EXCEPT would not.

When he ran his code he got this error:

PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL"
I will confess that it took me longer than I'd like to admit (but I just did!) to get to the heart of his problem, so I figure others might get similarly befuddled. Time for a blog post!

Let's explore some of the nuances behind musing MULTISET, centered around this important statement from the documentation:
Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements. In addition, nested tables of user-defined object types, even if their elements are comparable, must have MAP methods defined on them to be used in equality or IN conditi…