Here’s a collection of essays on PL/SQL, written by former members of the team Charles Whetherell and Bryn Llewellyn.

How to write a safe result cached PL/SQL function

The PL/SQL function result cache stores the output of a function for a given set of inputs. This essay explains how to write these correctly.

A primer on PL/SQL inlining

The PL/SQL language offers the inlining optimization. This primer describes inlining, tells how it works, and guides its use in practical programs.

Calling PL/SQL programs – surprising behaviour?

A procedure has a surprising outcome. Or does it? Once the PL/SQL rules are understood, there are fewer surprise in life.

Names vs identifiers

Databases are full of things: tables, sequences, columns, views, PL/SQL units, what have you. Things have names and are manipulated by mentioning the names. The programming languages SQL and PL/SQL use identifiers, not names. Questions show many programmers are confused about the difference. This note describes the relationships between things, names, and identifiers. Once the programming rules are absorbed, developers can write code faster and with less heartburn.

PL/SQL lexical analysis

A friend wrote a PL/SQL source code analyzer. He was surprised when his tool worked on return 'Y'; but failed on return'Y';. Is the blank-free version legal PL/SQL? Does his tool have a bug? The answer lies in the lexical structure of PL/SQL and SQL. This note explains lexical analysis and provides an answer to our friend’s question.

Transforming one table to another: SQL or PL/SQL?

This mantra is often recited by famous speakers at conferences about Oracle Database:

“If you can do it in SQL, do so; only if you can’t, do it in PL/SQL”

But should it be slavishly obeyed in all circumstances? As with all best practice prescriptions, the answer is “It
depends”. This paper examines alternative solutions to a well-known problem:

how to compute non-overlapping RowID ranges that completely cover a nominated table and that all contain, as nearly as is possible, the same number of rows.