Obsessed with Oracle PL/SQL

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. ...

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: COLUMN_VALUE 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    IS TABLE OF VARCHAR2 (100); / GRANT EXECUTE ON list_of_names_t TO PUBLIC / DECLARE    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 (); BEGIN    /* Can extend in "bulk" - 6 at once here */    hap...

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/": This: http://www.plsqlchallenge.com/apex/f?p=QDB_PROD:34:::::P34_USER,P34_CODE:... should be this: http://www.plsqlchallenge.com/pls/apex/f?p=QDB_PROD:34:::::P34_USER,P34_CODE:... Looked into the code and found:    FUNCTION apex_website_url RETURN VARCHAR2    IS    BEGIN ...

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 gener...

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 12 c  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