Showing posts from September, 2014

Coming down to earth at OOW14

Before I joined Oracle, I was honored to be an ACE Director for several years, and boy did I get spoiled. Especially at Oracle Open World time.

Oracle paid for my airfare and hotel. They set up the hotel reservation. They picked me up in (non-stretch) limo and delivered me to that hotel. They drove me back to the airport. Nice....

Then I rejoined Oracle in March of this year.

In late August, I was talking with a friend at Oracle and mentioned that I had yet to arrange my hotel.

"Whaaaat?" she practically screeched in the phone. "You don't have your hotel? Oh, Steven, you better get on that right away."

Turns out I had received an email on 7 July saying, in effect, "Congrats, Steven as Oracle employee. We have confirmed your registration for the now. NOW GO RESERVE YOUR HOTEL ROOM."

But I didn't notice that last part. Just filed the email away.

So I followed the advice of my friend, and went on-line to get my hotel reservation set up.

Guess what? I…

Use COLUMN_VALUE when selecting from scalar table function

Received this question today:

I don’t have a problem to select from collection when collection is based on objects with columns/attributes. What about a collection defined as:

TYPE list_of_numbers_t IS TABLE OF NUMBER;

What would be the column name when you select from the collection?

Short answer:


Longer answer: here's a script I used to demonstrate several different features of nested tables. See query at bottom.

CREATE OR REPLACE TYPE list_of_names_t


   happyfamily     list_of_names_t := list_of_names_t ();
   children        list_of_names_t := list_of_names_t ();
   grandchildren   list_of_names_t := list_of_names_t ();
   parents         list_of_names_t := list_of_names_t ();
   /* Can extend in "bulk" - 6 at once here */
   happyfamily.EXTEND (6);
   happyfamily (1) := 'Veva';
   happyfamily (2) := 'Chris';
   happyfamily (3) := 'Lauren';
   happyfamily (…

RTFM? KISS? Comment? Whatever, just get the code to work right!

We've been hitting a snag of late with new registrants at the PL/SQL Challenge. As with many sites, to ensure that a person's email is not being hijacked, we send an email with a verification URL.

It was working for quite a while, but then we noticed players reporting a bug:

On clicking the verification link am getting an error message like: The PL/SQL Challenge website is temporarily unavailable. Please try again later or join the PL/SQL Challenge twitter group: PLSQLChallenge Sorry for the inconvenience.
Ugh. Well, the site is certainly available. So what's going on? Turns out the verification URL is missing the all-important "/pls/":

should be this:,P34_CODE:...
Looked into the code and found:
   FUNCTION apex_website_url RETURN VARCHAR2    IS    BEGIN       RETURN CASE                WHEN qdb_config.prod_state THEN APEX_…
Received this note from a PL/SQL developer today:

I have an idea to build the database of my application in such a way that all DML operations for the application are managed at the back end. There should be generic functions/procedures that will perform the DML operations. Also there should be functions developed that will provide the "Select" data. In short, I want to remove all sort of DML from front end and bring them to back end. In case of any DB structural changes, we should not require a large development effort. For example, if we have to add couple of columns to a table then it should be easily manageable/configurable in order to minimize the development effort cost.

This is the sort of note that makes me happy.

I have proposed for years to Oracle Database developers that we should see SQL as a service that is provided to us, not something we should write over and over and over again in our "high level" application code (as opposed to low level generic utili…

All My OOW14 Sessions

For those of you attending OOW14 and interested in PL/SQL-related sessions, here are mine. You can add them to your schedule via Schedule Builder.

Session ID: CON9027
Session Title: YesSQL! A Celebration of SQL and PL/SQL
Venue / Room: Moscone South - 103
Date and Time: 9/29/14, 18:30 - 20:00

Session ID: CON8450
Session Title: SQL (and PL/SQL) Tuning Experts Panel
Venue / Room: Moscone South - 306
Date and Time: 9/30/14, 17:00 - 17:45

Session ID: CON7828
Session Title: The Whys and Wherefores of New Oracle Database 12c PL/SQL Features
Venue / Room: Moscone South - 103
Date and Time: 10/1/14, 15:30 - 16:15

Session ID: CON8265
Session Title: PL/SQL: The Scripting Language Liberator
Venue / Room: Moscone South - 307
Date and Time: 10/1/14, 12:45 - 13:30