Obsessed with Oracle PL/SQL

The DBMS_UTILITY has long (since 10.2) offered three functions that are very handy when either tracing execution or logging errors: FORMAT_CALL_STACK - answering the question "How did I get here?" FORMAT_ERROR_STACK - answering the question "What was the error?" (or a stack of errors, depending on the situation) FORMAT_ERROR_BACKTRACE - answering the question "On what line was my error raised?" Therefore (and prior to 12.2), if you wanted to get the error information + the line number on which the error was raised, you would need to call both of the "*ERROR*" as in: CREATE OR REPLACE PROCEDURE p3 AUTHID DEFINER IS BEGIN p2; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace); RAISE; END; Of course, in the real world, you would not display the text on the screen. You would write them to a log table via an autonomous tra...

This question found its way into my In Box yesterday: I have a table with an object type column. I want to way to get the value of an attribute of that object type in my query. But Oracle keeps telling me "ORA-00904: invalid identifier". What am I doing wrong? Almost certainly what you are doing wrong is forgetting to use a table alias. Yeah, it's that simple. Don't forget the table alias. Let's take a look. I create an object type, use that object type as a column in a table, and insert a couple of rows: CREATE TYPE food_t AS OBJECT ( NAME VARCHAR2 (100) , food_group VARCHAR2 (100) , grown_in VARCHAR2 (100) ) / CREATE TABLE food_table (id number primary key, my_food food_t) / BEGIN INSERT INTO food_table VALUES (1, NEW food_t ('Mutter Paneer', 'Curry', 'India')); INSERT INTO food_table VALUES (2, NEW food_t ('Cantaloupe', 'Fruit', 'Backyard')); COMMIT; END; ...

Over the weekend of April 21, we upgraded the Oracle Dev Gym site to v3 (code name: ORANGE). Here's the v2 home page: and now v3: Now you see the reason for the code name. It's orange! Here are the key changes you will find on the Dev Gym: Orange theme: all that red was hurting our eyes, but the main reason to switch to orange was to make it visually clear that this site, as with AskTOM , is part of the broader Oracle Developer initiative . Site search: type in a keyword, such as "FORALL" or "listagg" in the search bar on the home page, and we will find all quizzes, workouts and classes that match your criteria. You can further hone your search on the results page. The tournament quizzes are now offered on the home page; no need to click on the Tournaments tab to see them. These quizzes are produced fresh each week, and often focus on the latest features in SQL, PL/SQL and Oracle Database. Your recent activity on the site is a...

There's no shortage of people giving advice on how to improve your presentation skills and impact. I offer a short list of links at the bottom of this post.  I though I'd take a few moments to share some tips I follow to help me make the most of my time in front of audiences. Why listen to me? I've been doing talks on the  PL/SQL  language since 1992 and I am pretty sure that only 3 members of all those audiences ever fell asleep during my talk. What are the (at most) three key takeaways? Most attendees will forget most of what you said soon after leaving the session. Certainly almost every single technical detail  will be lost. So you need to decide before you start your talk what  are the at most three things you want an attendee to remember. Then put those in a slide and tell them right at a start. Remind them during your talk when you are getting to one of those top 3 things. Use the slide again at the end of your talk to drive the point...

This question rolled into my In Box today: If I have a procedure that is AUTONOMOUS_TRANSACTION that does an insert and then it calls a procedure with an insert, does the second procedure need a commit, or will the procedure with the AUTONOMOUS_TRANSACTION handle the commit? If you don't know off the top of your head, don't worry, I can build a test. First of all, if you ever find yourself writing something like "If you don't know off the top of your head, don't worry, I can build a test." then please by all means go right ahead and build yourself a test script. By doing so, you will better understand the feature in question and remember what you learned. Plus you end up with a script you can share with the community on LiveSQL . But I don't mind answering such questions. That way I get to better understand the feature in question, remember what I learned, share a script on LiveSQL (link at bottom of post), and also add to my blog. :-) So here goe...

Note: You might think that this was published on April 2nd, but in fact it was published on April 1st. PL/SQL, the database programming language from Oracle, introduced in 1991 and used by millions over the years to implement data APIs and business logic in mission critical applications from which billions of humans benefit daily, is undergoing a radical transformation in order to stay relevant for, and meta-cool to, future generations of developers. After a careful examination of all modern programming languages and the definitive StackOverflow developer surveys, the PL/SQL development team implemented a super-secret plan (yes, that’s correct, even the Distinguished Product Manager for PL/SQL, Bryn Llewellyn, is unaware of what you are about to read. So don’t bother him about it, OK?). I am, therefore, inordinately pleased and honored to be the first to announce the following changes for PL/SQL in Oracle Database 20c: PL/SQL will now be a case-insensitive language. Sort of. O...