Obsessed with Oracle PL/SQL

Millions of people develop applications on top of Oracle Database. The most secure and optimized of those applications take full advantage of SQL and PL/SQL.

In our next CodeTalk webcast, I will interview Oren Nakdimon of Moovit, lead developer for the backend of this popular transit app, to find out just how he and his small team have made the most of PL/SQL, and how they manage their PL/SQL code base.

Oren gave an excellent presentation at OOW16 describing how they have been able to leverage Edition-Based Redefinition (EBR) to minimize downtime and improve delivery of new services to users. He mentioned briefly in that talk (a) how much he enjoys PL/SQL and (b) the features of the language he appreciates most.

Don't worry, Tom. He likes SQL an awful lot, too. :-)

So I thought it would be interesting to hear about how Moovit uses PL/SQL in more detail. That's what we will be discussing on November 16 starting at 10 AM Chicago time.

Real world PL/SQL. From a really sharp crew.…

I occasionally am asked about naming conventions for PL/SQL program units.

I did publish a document back in 2009 (link below), but there are also other sources for naming conventions from others, offering different ideas and excellent motivation for standardizing the way you write your code. I have collected links to them here.

The most important thing about naming conventions is BE CONSISTENT.

PL/SQL Naming Conventions and Coding Standards - Steven Feuerstein 2009

Coding Standards for SQL and PL/SQL - William Robertson

ORACLE-BASE / Tim Hall / Oracle Naming Conventions

Trivadis PL/SQL and SQL Coding Guidelines Version 2.0

Ask Tom on Naming Conventions

PL/SQL and SQL naming conventions

Oracle SQL and PL/SQL Coding Standards – Cat Herding for Dummies

Slideshare Presentation on PL/SQL Coding Conventions

I received this email yesterday with a question about "weird SQL behavior".
I wrote a SQL delete statement with a select statement in its where clause. I made a mistake and forgot to create a column in the table that I used in the subquery. But the table from which I am deleting has a column with the same name. I did not get an error on compilation. Why not? There is no column with this name in this table in the where-clause. As a result I deleted all the rows in the table.  That last sentence - "I deleted all the rows in the table." - has got to be one of the worst things you ever say to yourself as an Oracle Database developer. Well, OK, there are worse, like "I truncated a table in production accidentally". Still, that's pretty bad.

So is that "weird" SQL behavior? Should the DELETE have failed to compile? Answers: No and No. Let's take a look at an example to drive the point him clearly.

I create two tables:

CREATE TABLE houses ( ho…

"Persistent code base"? What's that?

Well, I suppose I could have said "aging" or "legacy" code base, but that doesn't capture my point, which starts with:
If I were a Javascript programmer, I would probably be excited if my code lasted more than a year.
As an Oracle Database developer, though, it is not unreasonable to expect that my code will be in production for years, perhaps decades.

Now, that's persistent code. And why is that?
Because, well, DATABASE.
The database is the repository for your enterprise. Certainly it contains the data, and if you fully leverage the database properly, it will also contain your business logic.
And while it is not all that painfully disruptive to rewrite your UI code, it can be business-threatening to do any of the following: Switch your database technologyUpgrade your database software too quicklyRewrite your business logic in a new scripting language  So your database - data and associated code - tends to b…

Probably once a month, I receive an email from a programmer who either wants to tune up their Oracle Database programming skills (SQL and PL/SQL, primarily) or wants to help me or the community in some way.

Here's the latest, via LinkedIn Messages:

My friends and I are just in love with Oracle PL/SQL and the whole concept of database development and database design. This is like a religion for us. Can you you provide us some work to do?
I don't have a handy set of projects for people to work on, especially now that I am back with Oracle.

But there are plenty of opportunities for you out there, especially if you'd like to help build community tools. So here's a short list. I hope readers will offer others via the Comments section.

1. OraOpenSource - github.com/oraopensource

A project of oraopensource.com, led by Martin Giffy D'Souza, this repo offers an ever-growing set of utilities, with lots of room for expansion.




2. utPLSQL - github.com/utplsql

utPLSQL, which I o…

Building a script for an upcoming PL/SQL Challenge quiz, I wrote a nested procedure as follows:

PROCEDURE show_cursor_status IS BEGIN IF all_in_one_cur%ISOPEN THEN DBMS_OUTPUT.put_line ('all_in_one_cur is still open'); ELSE DBMS_OUTPUT.put_line ('all_in_one_cur is closed'); END IF; IF department_cur%ISOPEN THEN DBMS_OUTPUT.put_line ('department_cur is still open'); ELSE DBMS_OUTPUT.put_line ('department_cur is closed'); END IF; IF employee_cur%ISOPEN THEN DBMS_OUTPUT.put_line ('employee_cur is still open'); ELSE DBMS_OUTPUT.put_line ('employee_cur is closed'); END IF; END;
Nothing wrong with that, of course. Works just fine.

But there's a lot of repetition. I hate that. I like to normalize my code. And when the repetitive code is based on an IF statement, I immediately think of CASE - expressio…