Obsessed with Oracle PL/SQL

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

I will giving a presentation to Oracle Corporation PL/SQL developers (that is, Oracle employees who build internal apps using PL/SQL) in September, lots of them new to the language and starting new development. The focus of the presentation is on best practices: what are the top recommendations I want to give developers to ensure that they write high quality, maintainable, performant code? I plan to turn this into a checklist developers can use to stay focused on the Big Picture as they write their code. So if you could pick just THREE recommendations to developers to help them write great code  what would they be?

First the key details: When: 6:30 - 8:00 PM on Monday, 29 September Where: Moscone South - 103 To register: Session CON9027. This event will be offered as a "regular" OOW session, which means you register to attend through Schedule Builder . Why: Because SQL and PL/SQL are amazing technologies, and the people who them to deliver applications are amazing technologists For many, many years - since 1979, in fact - Oracle Database software and other relational solutions have been at the core of just about every significant human development, whether it be based in private enterprise, government, or the world of NGOs. SQL, relational technology, Oracle Database: they have been incredibly, outrageously successful. And SQL in particular is a critical layer within the technology stack that runs the systems that run the world. SQL is a powerful yet relatively accessible interface between algorithmic processing and data.  Rather than write a program to extract, mani...

I recorded the second in my PL/SQL by Feuerstein 2014 webinar series yesterday. You can access the recording here . If you were registered for the event, simply provide your email address and log in. But first have the popcorn ready because the show will begin immediately! If you did not register, provide your email in the Register field. You will then be prompted to confirm it and then  have your popcorn ready. I tried something different with this webcast: rather than spend most of my time in slides, I spent 40 minutes taking a look at spaghetti code and then showing you the cleaned up version. I am not wildly excited about how it turned out. I would be very happy to read any comments you have about the webinar right here on this blog. And be sure to sign up for the other webinars, available at the  Oracle PL/SQL Learning Library .