Obsessed with Oracle PL/SQL

OK, folks, here's a PL/SQL puzzle. It was originally posted on Twitter. I give you the link to that thread at the end of this post. But first....don't you want to try to solve the puzzle yourself? :-) Please note that the solutions to the puzzle have absolutely NOTHING to do with writing good code. They are simply exercising various features of the PL/SQL language. The puzzle Can you come up with just ONE STATEMENT to add to plsqlpuzzle_proc so that it can execute without terminating with an unhandled exception? Use this LiveSQL script as a starting point for your attempted solutions. CREATE TABLE plsqlpuzzle (n NUMBER) / CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc IS r plsqlpuzzle%ROWTYPE; TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE INDEX BY PLS_INTEGER; t r_t; BEGIN SELECT * INTO r FROM plsqlpuzzle; DBMS_OUTPUT.put_line (r.n); DBMS_OUTPUT.put_line (t (1).n); END; / BEGIN plsqlpuzzle_proc; END; / ORA-01403: no data...

I got an interesting email today from a reader of one of my PL/SQL 101" articles for Oracle Magazine, Building with Blocks . Q. had taken the code from the article, made some changes, tried to run them, and got very confused. He wrote: When I run this code, I see "Hello World". DECLARE l_message VARCHAR2(100) := 'Hello World!'; BEGIN DBMS_OUTPUT.put_line (l_message); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error='||SQLERRM); END; / Hello World! When I change the block to make the l_message variable too small for its string, I see the VALUE_ERROR error message. DECLARE l_message VARCHAR2(10); BEGIN l_message := 'Hello World!'; DBMS_OUTPUT.put_line (l_message); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error='||SQLERRM); END; / Error=ORA-06502: PL/SQL: numeric or value error: character string buffer too small But when I change the name of the variables inside the call to DBMS_OUTPUT.PU...

Since February 2019, I've been running, with Chris Saxon , on my team of Developer Advocates , monthly PL/SQL Office Hours sessions. They generally consist of short presentations on a PL/SQL-related topic, followed by lots of interesting discussion - between Chris and I, with attendees on the session, and with other speakers. On November 5 at 9 AM Eastern , I am very pleased to have a session focused on testing PL/SQL code, featuring developers who are doing it out there in the "real world." No application will ever have zero bugs, but you sure want to keep them to a minimum. The best way to do this is to implement automated regression tests of your code, but "best" as usual does not equate to "easiest." Building and managing tests can be a big challenge, so in this Office Hours session, we will hear from developers who are doing just that. Learn from your peers about the obstacles they faced and how they overcame them. Bring your own stories an...

There are special member methods -  map  or  order  methods - that we use to tell Oracle Database how to compare two objects of the same datatype. This capability is critical when we want to perform an equality test in PL/SQL or when sorting objects in SQL. There is no default way to do this. In other words, if I create a simple object type, add it as a column to a table, and try to compare or sort, all I get are errors. Let's take a look. First I will create a table that has an object type as a column and add a couple of rows. CREATE TYPE food_ot AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (50), grown_in VARCHAR2 (100) ) NOT FINAL / CREATE TABLE meals ( served_on DATE, main_course food_ot ); / BEGIN INSERT INTO meals (served_on, main_course) VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean')); INSERT INTO meals (served_on, main_course) VALUES (SYSDATE + 1, food_ot ('House Salad...

My #ThanksOGB post: I've been working - and personally benefiting from - Oracle Database technology since 1987. I joined Oracle as a pre-sales consultant, which meant back then I was a techie sidekick for one or more Oracle salespeople. I moved on to various other roles and in 1992 left to become a consultant. Two years later, wrote a book on PL/SQL and have been obsessed with that language ever since. In 1999, I released the first version of utPLSQL - unit test for PLSQL, my version of JUnit. Worked for Quest for many years (bringing Quest Code Tester for Oracle to the market, among other things), and in 2014 rejoined Oracle, where I now lead a team of developer advocates (Blaine Carter, Chris Saxon, Connor McDonald, Dan McGhan). It's been a great life - and I expect it to keep on being such for a while to come. Part of the point of my little historical review, though, is that I was always paid to provide resources to the community (some of them free, like utPLSQL and m...

So far in my series on object-oriented development in Oracle Database, all manipulation of object type instances have taken place in PL/SQL. But as you may have guessed from the fact that you "CREATE OR REPLACE" object types, those types are also available for us in SQL. You can create relational tables of object types, called object tables. You can also define columns of relational tables whose datatypes are object types. In this post, I will explore both of these approaches. All the code you see below may be found in this  LiveSQL script , so you can get to know these features by playing around with them yourself. Object Tables It's easy to create object tables and work with the instances in those tables (both selecting and changing rows of data). Here's a simple example: CREATE TYPE food_ot AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (50), grown_in VARCHAR2 (100) ) NOT FINAL / CREATE TABLE food_table OF food_ot (CONSTRAINT food_ta...