Obsessed with Oracle PL/SQL

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

If you ever run across any of the following, apply the suggested cleanup, or contact the owner of the code, or run for the hills. And I am pretty sure many of my readers will have suggestions for other code that should never appear in your PL/SQL programs. Let me know in the comments and I will add them to the post (giving me all the credit of course - no, just joking! YOUR NAME will be bright lights. :-) ). Here's the list of things to avoid: DON'T set default values of variables to NULL. DON'T select from dual for....just about anything. DON'T declare the FOR loop iterator. DON'T trap NO_DATA_FOUND for inserts, updates and deletes. DON'T execute the same DML statement inside a loop. DON'T hide errors. DON'T re-raise an error without logging first. See below for the gory details. Set default value to NULL Whenever you declare a variable it is assigned a default value of NULL. So you should not explicitly provide NULL as a default v...

We've been offering quizzes on the PL/SQL Challenge (and now the new Oracle Dev Gym - still in an "early adaptor" user testing phase) since April 2010. We've covered hundreds of topics in PL/SQL, and hundreds more in SQL. Most quizzes are multiple choice, and one of my favorite question style is to ask: what code in my program unit is unnecessary? By "unnecessary" we mean that the code can be removed without affecting the behavior of the program unit. There can be two reasons, roughly, for a chunk of code to be unnecessary: 1. The code "reinforces" or explicitly defines default behavior. If you remove it, the default comes into play. So no harm done, but it is often beneficial to be explicit. 2. You misunderstand how the language works and therefore write code that should not be there at all, and is likely to cause maintenance issues later (and maybe even lead to bugs). I offer an exercise below in identifying unnecessary code. See if you...

I've published several posts on table functions . Here's another one! Why? A few days ago I tweeted the following: Two of my followers immediately asked to hear the story: "those are the fun ones, but pics or it didnt happen ;-)" and "may be interesting to view the step you do to find solutions more then result." Very good points; I especially agree with the latter. As I was going through the revisions to my code, I was thinking (as I have often done before): "It might be helpful to show the process I go through, because it sure feels like a powerful, positive way to build and improve code." The problems I run into when actually sitting down to tell the story are: 1. Time: yes, I know, we are all busy. Plus, isn't it my job to share thoughts on PL/SQL programming?  Yes it is! Well, part of my job, anyway. And I have  been neglecting my blog. But right now, at this moment, I am very focused on finishing the "early adap...

No language is perfect and while PL/SQL is an incredibly "tight fit" for building applications on top of Oracle SQL (and, of course, Oracle Database), it also is not yet quite perfect . Maybe in Oracle Database 13(?) - (?) being whichever letter Larry decides will best reflect the main theme of that version.... In any case, in the meantime, and to paraphrase a saying: You write code with the language you've got, not the language you want. So the key thing is to maximize your productivity any way you can, all along the way. And sometimes the steps you can and should take in this area can be very small, but they can still add up. Case in point: displaying a Boolean value with DBMS_OUTPUT.PUT_LINE. As anyone who's spent time with PL/SQL knows, DBMS_OUTPUT.PUT_LINE is the procedure provided by Oracle to display text from within a PL/SQL block on your screen. It accepts a string (and anything that can be implicitly converted to a string) and sends it to the ...