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

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

"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 technology Upgrade your database software too quickly Rewrite your business logic in a new scripting language  So your database

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/utpls

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

Received this via email today: I have just found the PL/SQL Challenge and I am eager to get started. Under Instructions on the Quiz Introduction page is something to which I would like to call your attention.   "For most quizzes, however, it is possible that more than one choice is correct, that all choices are correct, or that none of the choices are correct. You must check at least one box from those offered before you can submit your answer."   How might one check a box and then submit if none of the choices are correct? On first reading, I groaned. Really? Did I really leave a big hole like that in our quiz-taking process or - not quite as alarming - in my text? Is that a contradiction? No! No! No! There is an explanation which leaves me firmly on the right side of Logic. Can you see what it must be?  I will post this first as a small logic puzzle via Twitter, then I will update this post in a day or two with my answer. A day goes by. Another day goes

You'd think that after working with Oracle Database and PL/SQL since 1990, I'd know everything . Ha. Not so. :-) Of course, there are always the new features, such as those coming in 12.2 (I will be blogging about those extensively in the coming months). But even for features that have been in the language for decades, I still encounter small gaps in my knowledge. For example, I had long known that when you execute a DDL (data definition language) statement in PL/SQL (which must be done as dynamic SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE /EXECUTE) a commit is executed implicitly after the statement. What I'd somehow missed was that a commit is also performed before the DDL statement is executed. So that is the point of this post: Oracle Database issues a commit before a DDL statement is executed, and then afterwards as well. You can see this behavior in action in the script below, which is generated from a recent PL/SQL Challenge quiz  and can be run direct