Obsessed with Oracle PL/SQL

Here goes: I execute this statement: CREATE OR REPLACE FUNCTION ndf RETURN NUMBER IS BEGIN RAISE NO_DATA_FOUND; END; / We all know what that function is going to do, right? #Fail, as one might say on Twitter. So the brain teaser is:  I n the block below, replace <statement> with a single statement that does not contain any exception handling, but does call the NDF function, so that after the block executes, "NDF? What NDF?" is displayed on the screen. DECLARE n NUMBER; BEGIN <statement> DBMS_OUTPUT.PUT_LINE ( 'NDF? What NDF?' ); END; / I will wait a bit to post my answer. I encourage you to post yours as a comment to this blog post. Wait.... Wait.... Wait for it.... OK! After a couple of days of collecting responses on this post and also in LinkedIn, it's time to publish the answer: You can replace <statement> with any DML statement that executes the function, and the failure of the function with an unh

Just received this feedback on an Oracle Magazine article : I’ve just started using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it’s a great utility when the code contains no comments, but unless I’m missing something it’s not terribly useful with regard to pinpointing the exact line number when the code contains comments. Now, I must confess that I am tempted to respond with such questions along these lines: Did you try it out? That's the best way to learn! But I am always looking for another reason to post on my blog and add to the LiveSQL repository. So that's what I am doing! :-) The answer is quite straightforward: Comments do not interfere with "pinpointing the exact line number" on which the error was raised. But I bet you want proof, so let's get down to business. I create a procedure with comments and a RAISE statement: CREATE OR REPLACE PROCEDURE comments_throw_off_line# IS /* Here's my header Blah blah blah */ BEGIN DBMS_OU

We are enhancing the Oracle Dev Gym to automatically execute code you type as a solution to a problem, and validate that it works. We are doing this via REST calls to LiveSQL . It's very cool and a lot of fun to put together. Hopefully it will be way more  fun for you to see if you can solve our challenges. Anyway, LiveSQL is going to pass back results via a JSON document, which means that yes, finally, I am going to start working with JSON and learning about Oracle Database JSON functionality. Yes, I am a total novice at this. Which, I imagine, is the case for many other PL/SQL developers. So I thought I would immediately share some early lessons learned. That way maybe you will save yourself the 15 minutes I wasted sorting this out. First, though, if you haven't done anything  with JSON yet, start by looking over these: JSON in Oracle Database JSON Support in Oracle Database 12c Release 1 (12.1.0.2) Plus, all the code shown below can be seen, and run, in LiveSQL .

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

Unlike other code in the stack, database-level programs such as those built in PL/SQL, tend to have a very long half-life. In part, that's because companies are far less likely to change their database technology than user interface language. And that means that it is especially important for Oracle Database programmers to prioritizing writing maintainable, well-documented programs. It is also critical that as we make changes to our code base, we don't break programs that have been working for years. In other words, whenever possible, enhanced code should be backward compatible with earlier versions of code. That's called backward compatibility.  In this post, I will explore features of PL/SQL that make it easy to ensure your latest and greatest code has maximum compatibility with "the past." If a tree falls in a forest and no one is around to hear it, does it make a sound? You've heard that before, right? Well, how about this one: If you write a prog

As my 25th winter in Chicago approaches (and after the 2016 elections), I attempt to cheer myself up with jokes. Programmer jokes. Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.  Here's my second joke for your enjoyment. And just in case you have any doubts, this is a true story. As in not "based on a true story." True. All the way through. After I left Oracle Corporation back in 1992, I spent a few years consulting before I wrote the first edition of Oracle PL/SQL Programming (and my life changed big time). For about six months, I commuted to downtown Chicago to work at an insurance company. I was, as you might expect, part of the development team, writing PL/SQL packages and building user interfaces in Oracle's wonderful SQL*Forms 3. This particular insurance company had been acquired by another, even larger insurance company. And it tur

As my 25th winter in Chicago approaches, I attempt to cheer myself up with jokes. Programmer jokes. Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision. Here's my first joke for your enjoyment. No Packages for You! Packages should be the foundation, the building blocks of any PL/SQL-based applications. I believe that you should not create schema-level procedures and functions, but instead define them as subprograms in one or more packages. So you can imagine it came as quite a shock to me when an attendee at one my of trainings came up over the break and said to me: My DBAs won't let me put my code in packages. Everything has to be defined as a procedure or function at the schema level. They tell me that packages take up too much memory and so they cannot be used. Wow! Packages take up too much memory.... Who would've thought it? Now, as wit

As my 25th winter in Chicago approaches (and after the 2016 elections),, I attempt to cheer myself up with jokes. Programmer jokes. Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.  Here's the most recent "joke" I heard from a developer, via an email back in July: We just encountered a PL/SQL performance problem after migrating a database from 10g to 11g and I tought it might interest you. I isolated the problem using    the 10046 traces and DBMS_PROFILER. So I was able to reproduce the problem with a very simple PL/SQL testcase, but I cannot explain it. He then pasted in 439 lines of code and output. So this is the "very simple" testcase? It might be, but still I wrote back: "It may be simple, but it's long and I'd appreciate it if you would summarize for me what you believe you have discovered." This way, at least, I

On the PL/SQL Challenge , all times are shown in the UTC timezone. Weekly quizzes end on Friday, midnight UTC. So I recently decided that when I display the time that the quiz starts and ends, I should add the string "UTC". Our quiz website is built in Oracle Application Express 5.0 , so I opened up the process that gets the date and found this: DECLARE l_play_date DATE := qdb_quiz_mgr.date_for_question_usage (:p46_question_id); BEGIN :p46_scheduled_to_play_on := TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI'); "OK, then," says Steven the Fantastic Developer to himself. "I know exactly what to do." And I did it: DECLARE l_play_date DATE := qdb_quiz_mgr.date_for_question_usage (:p46_question_id); BEGIN :p46_scheduled_to_play_on := TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI') || ' UTC'; Ah, PL/SQL and APEX - so easy to use! :-) Now, there are lots of things you could say about the

With programmers new to PL/SQL (and SQL), it is not uncommon to find that they overcomplicate things, writing more code than is necessary, and putting too much logic into PL/SQL.  That problem can then be compounded by accidentally getting the "right answer" based on inadequate testing and test data. So it is always good to be reminded:  Do as much work as you can in SQL, and then finish up in PL/SQL.  Where "finish up" means do whatever is appropriate within the database, and then make that available to whatever language is being used to write the UI! A recent quiz on the PL/SQL Challenge  explored this topic, and I offer it below as a learning exercise via blog post. Suppose I have a table and dataset as follows: CREATE TABLE plch_animals ( animal_id INTEGER PRIMARY KEY, animal_name VARCHAR2 (100) ) / BEGIN INSERT INTO plch_animals (animal_id, animal_name) VALUES (1, 'Bonobo'); INSERT INTO plch_animals (anima

DBMS_OUTPUT. PUT_LINE is the built-in procedure that PL/SQL developers use to display output on the screen. Let's watch it do it's thing on LiveSQL : So I displayed a string, a date, a date converted to a string, and a number. Cool.  Now let's display a Boolean value (TRUE, FALSE or NULL): Ouch! It did not like a Boolean value, that's for sure. But why not? To figure that out, we need to take a look at the specification of the DBMS_OUTPUT package. That's easy in SQL Developer : just right-click and choose Popup Describe.  Searching for "procedure put_line", I see: create or replace package dbms_output authid definer as ... procedure put_line(a varchar2); Huh. That's weird. It only accepts a string. So how could it display a number and a date with no problem, but then choke on a Boolean? It all has to do with implicit conversions . Generally, Oracle Database in both SQL and PL/SQL will automatically and implic

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