A collection of PL/SQL essays

December 16, 2021 | 2 minute read
Chris Saxon
Developer Advocate
Text Size 100%:

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.

Chris Saxon

Developer Advocate

Chris Saxon is an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL!

To help you with this he blogs at All Things SQL. He also creates videos combining SQL and magic on YouTube at the The Magic of SQL.

If you have questions about working with Oracle Database technology, please reach out to him. You can do this via Twitter or on Ask Tom.

Previous Post

Announcing the winners of the Oracle Dev Gym 21c tournament

Chris Saxon | 2 min read

Next Post

Announcing the 2021 Oracle Dev Gym Championships

Chris Saxon | 2 min read