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

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