Skip to main content


Showing posts from February, 2016

Use TABLE Operator with Associative Arrays in Oracle Database 12c

Starting with 12.1, you can now use the TABLE operator with associative arrays whose types are defined in a package specification. One really sweet application of this feature is to order the contents of your collection. Let's take a look Suppose I create these database objects: CREATE TABLE plch_employees ( employee_id INTEGER PRIMARY KEY, last_name VARCHAR2 (100) UNIQUE, salary NUMBER ) / BEGIN INSERT INTO plch_employees VALUES (100, 'Apramy', 1000); INSERT INTO plch_employees VALUES (175, 'Shipo', 2500); INSERT INTO plch_employees VALUES (242, 'Inkul', 500); END; / CREATE OR REPLACE PACKAGE plch_arrays IS TYPE employees_t IS TABLE OF plch_employees%ROWTYPE INDEX BY PLS_INTEGER; TYPE emps_by_name_t IS TABLE OF plch_employees%ROWTYPE INDEX BY plch_employees.last_name%TYPE; g_employees employees_t; END; / CREATE OR REPLACE PACKAGE BODY plch_arrays IS BEGIN SELECT *

PL/SQL Challenge approaches 1,000,000 should we celebrate?

Chris Saxon , the PL/SQL Challenge Database Design Quizmaster and Oracle Database Advocate for SQL and member of Ask Tom Answer Team, reminded me yesterday that the number of answers submitted on the PL/SQL Challenge is getting close to 1M. Now in the, ahem, good old days, when we had a daily PL/SQL quiz, the 1M barrier would be smashed in short order. These days, with our sweep of weekly quizzes, we have a little more time before the earth-shaking event. So I thought I'd reach out to my pals in the Oracle Database community - PL/SQL Challenge players and otherwise - and see if you had any fun, interesting ideas of how we might celebrate this milestone. We've got some thoughts, but I'll hold off on sharing those until I hear from you.