Obsessed with Oracle PL/SQL

The TREAT function comes in very handy when working with an object type hierarchy, and you need to  access attributes or methods of a subtype of a row or column's declared type. This topic was covered in a PL/SQL Challenge quiz offered in March 2016.  Suppose I have the following type hierarchy and   I use them as column types in my meals table: CREATE TYPE food_t AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100) ) NOT FINAL; / CREATE TYPE dessert_t UNDER food_t ( contains_chocolate CHAR (1), year_created NUMBER (4) ) NOT FINAL; / CREATE TYPE cake_t UNDER dessert_t ( diameter NUMBER, inscription VARCHAR2 (200) ); / CREATE TABLE meals ( served_on DATE, appetizer food_t, main_course food_t, dessert dessert_t ); I then insert some rows into the table: BEGIN INSERT INTO meals VALUES (SYSDATE + 1, food_t ('Shrimp cocktail', 'PROTEIN...

This odd little piece of code was featured in the weekly PL/SQL Challenge quiz 12 March - 18 March 2016 . What do you think will be displayed after executing the following block? DECLARE my_flag BOOLEAN; BEGIN CASE my_flag WHEN my_flag IS NULL THEN DBMS_OUTPUT.PUT_LINE ('my_flag is NULL'); WHEN TRUE THEN DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE'); ELSE DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE'); END CASE; END; / At first glance (if you are like me), you would say "my_flag is NULL", right? After all, my_flag is initialized to NULL when declared, and I don't change the value. But, lo and behold, you will see: my_flag is FALSE Curious, right? So what's going on? Well, we have a very confused and confusing piece of code: I have written a simple CASE (which is of the form CASE expression WHEN ...), but then my WHEN clauses follow a typical searched CASE format (CASE WHEN e...

Sometime  in the next couple of months, someone will submit the 1,000,000th answer on the PL/SQL Challenge. And Chris Saxon, member of the AskTom Answer Team, SQL Wizard and Database Design Quizmaster, assures me that he will write a query to identify  exactly  who that person is (will be). That's a very nice milestone, so we figured it will be a good time to celebrate.  We are still sorting out  precisely  what we will do, but for sure, we will want to feature stories from our players: How the PL/SQL Challenge has helped you in your career High points (or low points) from your activity on the site (I have low points, which I will share, hopefully none of you do!) Interesting stories from the last six years of answering and commenting on quizzes Whatever else comes to mind! You are welcome to post your stories here or the  PL/SQL Challenge blog  or email them directly to steven.feuerstein@oracle.com. I will be collating ...

As of Oracle Database 12c Release 1, you can now use the TABLE operator with associative arrays whose types are declared in a package specification. Prior to 12.1, this was only possible with schema-level nested table and varray types. Here's a quick example: CREATE OR REPLACE PACKAGE aa_pkg AUTHID DEFINER IS TYPE record_t IS RECORD (nm VARCHAR2 (100), sal NUMBER); TYPE array_t IS TABLE OF record_t INDEX BY PLS_INTEGER; FUNCTION my_array RETURN array_t; END; / CREATE OR REPLACE PACKAGE BODY aa_pkg IS FUNCTION my_array RETURN array_t IS l_return array_t; BEGIN l_return (1).nm := 'Me'; l_return (1).sal := 1000; l_return (200).nm := 'You'; l_return (200).sal := 2; RETURN l_return; END; END; / DECLARE l_array aa_pkg.array_t; BEGIN l_array := aa_pkg.my_array; FOR rec IN ( SELECT * FROM TABLE (l_array) ORDER BY nm) LOOP DBMS_OUTPUT.put_line (rec.nm); END LOOP; END; / And with nested tables a...

1. Exceptions raised in the declaration section are not handled in the exception section. This sometimes surprises a developer new to PL/SQL. The exception section of a PL/SQL block can only possibly handle an exception raised in the executable section. An exception raised in the declaration section (in an attempt to assign a default value to a variable or constant) always propagates out unhandled to the enclosing block. Verify on  LiveSQL Exceptions Raised in Declaration Section Not Handled Locally 2. An exception raised does not automatically roll back uncommitted changes to tables. Any non-query DML statements that complete successfully in your session are not rolled back when an exception occurs - either directly in PL/SQL or propagated out from the SQL engine. You still have the option of either committing or rolling back yourself. If, however, the exception goes unhandled out to the host environment, a rollback almost always occurs (this is performed b...

Note: download the latest version of the checklist from OTN . You can comment on the checklist there, or on this blog post. Thanks! Bulk processing (FORALL and BULK COLLECT) is pretty much the most powerful and important performance feature available for the PL/SQL language. It addresses head-on one of the most common drags on performance in Oracle Database applications: row-by-row processing. The basic "anti-pattern" to look for in your code is a loop that contains non-query DML (insert, update, delete merge). This "DML loop" could be in PL/SQL code, but it could also appear in Java, Python, C, etc. Here's an example (DML statements in red ): PROCEDURE upd_for_dept ( dept_in IN employees.department_id%TYPE , newsal_in IN employees.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id, salary, hire_date FROM employees WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP BEGIN INSERT INTO emplo...

The PL/SQL Challenge offers weekly quizzes on SQL, PL/SQL, Database Design, Application Express, and Logic. A week on this website starts on Saturday at 00:00:00  UTC and ends on Friday at 23:59:59 UTC. So early in the morning on Saturday, we run weekly processes to rank the past week's quizzes, and do a whole lot of cleaning-up . Therein lies the tale of this post. But before getting into the story, a little "ancient history." In 1978-1980, while I attended the University of Rochester, I took three "101" software classes (Algol, Lisp and something else). That is the extent of my formal training as a software developer. Move forward in time 36 years or so, and here I am: a highly regarded PL/SQL programmer, trainer, author. Still, though, essentially self-taught and definitely  not a software engineer. I have the highest regard for engineers in general, and software engineers in particular. They combine deep knowledge with a strong sense of discipline....