Obsessed with Oracle PL/SQL

On January 14, 2020 at 9 AM Eastern , I am very pleased to hold a PL/SQL Office Hours session on one of the biggest challenges faced by developers setting up automated tests for database code: setup and teardown. 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 and your questions, and let's all work together on improving our code quality! For this session, we have two presenters: Deepti Bandari and Jasmin Fluri. Deepti Bandari is a senior software engineer at Fidelity Investments since 2013. Her focus areas include database design and developm

I posted the following puzzle on Twitter : A quick little #PLSQL puzzle: I set serveroutput on. After running the code you see in the block below, what will be displayed on the screen? Try it yourself before reading the rest of the post! White space so you do not immediately see my answer.  :-) The output is: 10 1 9 I expect most of you got the first two right and maybe the third one wrong. Note also that the block does not fail with any kind of exception, such as VALUE_ERROR. The reason for all this can be summed up in one phrase: implicit conversion. As noted by several people, this is a collection indexed by strings , not integers. Only associative arrays (INDEX BY) types support this. And that makes all the difference in this puzzle. The value being used in the assignment of 100 to elements in the array is an integer (indx). Since the index type is a string, the PL/SQL engine implicitly converts integers 1 through 10 yto strings "1", "2"

Dynamic means "run-time." Polymorphism means "multiple shapes." Synonyms for dynamic polymorphism include "runtime polymorphism" and "dynamic method dispatch." If you are a "traditional" relational database developer, these terms might sound unfamiliar. But how about overloading ? Are you familiar with that? Overloading occurs when you have more than one subprogram (procedure and/or function) with the same name in the declaration section of a block, package specification or package body. These subprograms need to differ by parameter list or type (procedure vs function) in a way that is sufficient for the compiler to distinguish. Well, guess what? Another name for overloading is "static polymorphism." Static means "compile-time." Polymorphism means "multiple shapes." Why, you might be wondering, does the Oracle Database need to wait till runtime to determine which method in which type in th

When it comes to PL/SQL puzzles via Twitter, I decided to change things up this week. I presented it as multiple choice this time. Here's the puzzle: After executing the code shown in the image what will be displayed on the screen (serveroutput is on!)? a. "No dummy" b. Unhandled VALUE_ERROR exception c. Unhandled NO_DATA_FOUND exception d. Compilation error e. Your guess is as good as mine. Before I unveil the amazing, mind-boggling answer....I will give you a moment to try to solve it yourself. OK. So the first inclination you might have as regards the output from this block is, quite logically, "No dummy!". After all, there are no rows in the dual table (or any other table for that matter) for which 1 is equal to 2. So that SELECT-INTO is going to raise a NO_DATA_FOUND exception. No doubt about that at all. And there's an exception handler for NO_DATA_FOUND (well, no_data_found, wait they are the same thing in PL/SQL! :-) ). So "

Almost certainly, whenever you write a procedure or function, you make certain assumptions. Some of them are quite reasonable, such as "I assume my database is up and running." Some of them are scary, such as "I assume my users will never change their minds." But many simply go unnoticed. You don't even realize you are making an assumption until it smacks you in face, most likely in production, when an unexpected error exposes the assumption. So in this PL/SQL puzzle, as I state on Twitter : The procedure shown below compiles without error. What assumptions am I making so that when it executes, it does not terminate with an exception? White space so you do not immediately see my answers.  :-) OK, let's dive in. I provide below all of the assumptions I was aware, and also some others that were provided in Twitter on the very active discussion that followed. As usual, I learned something new from the community! Line 3: by hardcoding the dataty

On December 3 for our AskTOM Office Hours session , three deeply experienced and highly respected APEX prosshared their wisdom on how best to write and manage PL/SQL code for Oracle Application Express projects. You can watch the recording and follow links to related resources here . Our experts were: Karen Cannell Karen promotes APEX topics and best practices through local, regional and national user group presentations and papers. A devoted user group volunteer since 2007, she is especially active in ODTUG, where she serves as the editor of the ODTUG Technical Journal. She is former Associate Editor of IOUG SELECT Magazine. She is co-author of Agile Oracle Application Express, Expert Oracle Application Express and Beginning Oracle Application Express 4. Her most recent presentation at Kscope15 focused on APEX Interactive Reports with a Deep Dive: APEX 5 New Features and Upgrade Cheat Sheet. Karen has delivered Application Express solutions since its Web DB and HTMLDB b

I published yet another PL/SQL puzzle on Twitter yesterday. Generated lots of interest and interesting replies. I don't think any single person caught everything , but as usual the community came through. I will repeat the puzzle here. If you haven't already seen it on Twitter, please try to solve it yourself before looking at my answer. What text can be removed from lines 3 though 12 in the code below so that after the anonymous block is executed, "121212" is still displayed on the screen? White space so you do not immediately see my answers.  :-) OK, let's dive in. Notice, first of all, that I asked about text  that can be removed, not lines. So you can remove entire lines or portions of lines. I refuse to accept that whitespace is text, so blank lines don't count. :-) Here are the opportunities for removal that I found: 4 - Remove the IN keyword. That's the default for parameters (though I generally always include it in my cod

I published a PL/SQL puzzle on Twitter on November 6 2019. I asked the following question: Which lines of code can be removed (either entirely or in part) from the block below and not affect the output of the program in any way? I neglected to mention in my original tweet a few important assumptions: You are running this code on Oracle Database 10g or higher. Server output is turned on. Whitespace (spaces, tabs, new-lines) don't count. Here's the code. I will publish it as an image, just as I did on Twitter, so that you can give it a go yourself, before taking a look at the answers from me and others below that. Check out the Twitter conversation for all the answers that were submitted. It's a fun read! Here are the full lines that I believe can be removed: 2 - There is not need to declare the iterator used in a FOR loop, numeric or cursor versions. 7 - There is no need to declare an "empty" collection to be used to initialize l_objects. 10 -

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