OPP/News for October 2005

Dear Oracle technologists,

Sorry about the delay in getting out this newsletter. I've been a bit busy. We held the first-ever PL/SQL-exclusive conference, Oracle PL/SQL Programming 2005, in early November and I've been recovering/catching up ever since. This newsletter will be a bit briefer than usual, but I hope still very useful.

Upcoming training opportunities

In case you are wondering how you can get your next live fix of "PL/SQL by Feuerstein" education, please check out the following:

November 17, NYC: the NYOUG is sponsoring a day-long "21st Century PL/SQL" seminar.

November 30, December 1, December 6, ONLINE! Quest is offering a series of on-line webinars. These "Focus On" programs offer in-depth instructions on collections, native dynamic SQL, and exception handling. It will be a great way to strengthen your knowledge of PL/SQL without even having to leave your cubicle!

OPP2005 - first PL/SQL conference a big success!

On November 2nd and 3rd, over 170 Oracle technologists gathered at the Wyndham O'Hare for the first ever Oracle PL/SQL Programming conference: OPP2005. Participants attended sessions on In-Depth PL/SQL, the View from Redwood Shores, and Multimedia Programming with PL/SQL. We ended the conference with a fascinating presentation by Bryn Llewellyn, Oracle PL/SQL Product Manager, giving us a glimpse of future directions for the PL/SQL language.

This conference also served as a birthday party for Oracle PL/SQL Programming the Book, and you can't have a party without a cake!

Links to all conference presentations are available at http://oracleplsqlprogramming.com/opp2005_agenda.php.

Evaluations are still rolling in, but attendees made very clear that they would like to see another PL/SQL conference in 2006. That was very gratifying to me, as I have never before organized a conference, from beginning to end (and couldn't have done it without the professional help of Your Conference Connection).

Oracle PL/SQL for DBAs

O'Reilly Media just released my latest book on PL/SQL: Oracle PL/SQL for DBAs. Actually, the main author is Arup Nanda. I am the secondary co-author, seeing as I am not even a DBA! Although primarily a tool for developers, PL/SQL has also become an essential tool for database administration, as DBAs take increasing responsibility for site performance and as the lines between developers and DBAs blur. Until now, there has not been a book focused squarely on the language topics of special concern to DBAs Oracle PL/SQL for DBAs fills the gap. Covering the latest Oracle version, Oracle Database 10g Release 2 and packed with code and usage examples, it contains:

  • A quick tour of the PL/SQL language, providing enough basic information about language fundamentals to get DBAs up and running

  • Extensive coverage of security topics for DBAs: Encryption (including both traditional methods and Oracle's new Transparent Data Encryption, TDE); Row-Level Security (RLS), Fine-Grained Auditing (FGA); and random value generation

  • Methods for DBAs to improve query and database performance with cursors and table functions

  • Coverage of Oracle scheduling, which allows jobs such as database monitoring and statistics gathering to be scheduled for regular execution

I expect this book to be very handy even for developers. Some of the content of this book appears in the fourth edition of Oracle PL/SQL Programming, but in abbreviated form. If you truly need to get a handle on security issues, encryption or job scheduling, this book will take you a long way.

Tip of the Month: Index collections by strings

If you are running Oracle9i Database Release 2 or above, you can advantage of a very, very cool feature: associative arrays that are indexed by strings, rather than integer. I have found this technique handy in a number of situations, most recently when I wanted to cache the contents of a relational table in a collection and access rows by a GUID (globally unique identifier) primary key value. These GUIDs are strings, not integers, so the typical INDEX BY BINARY_INTEGER won't help me out much. String-based indexing is also very helpful when you need to index information by more than one value, as is the case with a concatenated index.

In the package definition below, I am loading the contents of my books table (code to define this table is available in this newsletter's download) into a series of associative arrays that emulate the primary key and unique indexes on the table. The unique index arrays rely on string-based indexes. By caching the table contents in collections, I can greatly speed up the performance of querying against this static copy of the table. Very handy when running batch processes against large tables!

For some additional background on this technique, check out my Brave New World seminar. 

CREATE OR REPLACE PACKAGE summer_reading
IS
 SUBTYPE author_title_t IS VARCHAR2 (32767);

 -- Guarantee uniqueness of concatenated values 
 -- from the index with a delimiter.
 FUNCTION author_title (
 author_in books.author%TYPE
 ,title_in books.title%TYPE
 ,delim_in IN VARCHAR2 := '^'
 )
 RETURN author_title_t;

 FUNCTION onebook (
 book_id_in IN books.book_id%TYPE
 )
 RETURN books%ROWTYPE;

 FUNCTION onebook (
 isbn_in IN books.isbn%TYPE
 )
 RETURN books%ROWTYPE;

 FUNCTION onebook (
 author_in books.author%TYPE
 ,title_in books.title%TYPE
 )
 RETURN books%ROWTYPE;

 -- Only call if you want to RE-load the data.
 -- This is invoked automatically in the initialization section.
 PROCEDURE load_arrays;

 PROCEDURE set_reload_interval (
 interval_in IN NUMBER
 );

 PROCEDURE set_reload_interval (
 interval_in IN INTERVAL DAY TO SECOND
 );
END summer_reading;
/

CREATE OR REPLACE PACKAGE BODY summer_reading
IS
 g_last_load DATE;
 -- Auto reload turned off
 g_reload_interval INTERVAL DAY TO SECOND := NULL; 

 TYPE book_id_aat IS TABLE OF books%ROWTYPE
 INDEX BY PLS_INTEGER;

 TYPE isbn_aat IS TABLE OF books.book_id%TYPE
 INDEX BY books.isbn%TYPE;

 TYPE author_title_aat IS TABLE OF books.book_id%TYPE
 INDEX BY author_title_t;

 books_aa book_id_aat;
 by_isbn_aa isbn_aat;
 by_author_title_aa author_title_aat;

 FUNCTION author_title (
 author_in books.author%TYPE
 ,title_in books.title%TYPE
 ,delim_in IN VARCHAR2 := '^'
 )
 RETURN author_title_t
 IS
 BEGIN
 RETURN UPPER (author_in) || delim_in || UPPER (title_in);
 END;

 PROCEDURE load_arrays
 IS
 BEGIN
 DBMS_OUTPUT.put_line ( 'Reloading books arrays at '
 || TO_CHAR (SYSDATE, 'HH24:MI:SS')
 );

 FOR rec IN (SELECT *
 FROM books)
 LOOP
 books_aa (rec.book_id) := rec;
 by_isbn_aa (rec.isbn) := rec.book_id;
 by_author_title_aa (
 author_title (rec.author, rec.title)) :=
 rec.book_id;
 END LOOP;

 g_last_load := SYSDATE;
 END load_arrays;

 PROCEDURE set_reload_interval (
 interval_in IN INTERVAL DAY TO SECOND
 )
 IS
 BEGIN
 g_reload_interval := interval_in;
 END;

 PROCEDURE set_reload_interval (
 interval_in IN NUMBER
 )
 IS
 BEGIN
 g_reload_interval := 
 NUMTODSINTERVAL (interval_in, 'SECOND');
 END;

 FUNCTION reload_needed
 RETURN BOOLEAN
 IS
 retval BOOLEAN := g_reload_interval IS NOT NULL;
 l_date DATE := SYSDATE;
 BEGIN
 IF retval
 THEN
 retval :=
 NUMTODSINTERVAL (l_date - g_last_load, 'DAY') >
 g_reload_interval;
 END IF;

 RETURN retval;
 END;

 FUNCTION onebook (
 book_id_in IN books.book_id%TYPE
 )
 RETURN books%ROWTYPE
 IS
 BEGIN
 IF reload_needed
 THEN
 load_arrays;
 END IF;

 RETURN books_aa (book_id_in);
 END;

 FUNCTION onebook (
 isbn_in IN books.isbn%TYPE
 )
 RETURN books%ROWTYPE
 IS
 l_book_id books.book_id%TYPE := 
 by_isbn_aa (isbn_in);
 BEGIN
 RETURN onebook (l_book_id);
 END;

 FUNCTION onebook (
 author_in books.author%TYPE
 ,title_in books.title%TYPE
 )
 RETURN books%ROWTYPE
 IS
 BEGIN
 RETURN onebook (
 by_author_title_aa (
 author_title (author_in, title_in)));
 END;
BEGIN
 load_arrays;
END summer_reading;
/

Useful Code of the Month: Emulate primary key and unique indexes

The summer reading package shown above demonstrates a very powerful technique: emulation of primary key and unique indexes in collections, relying on string-based indexes for concatenated indexes and string values in the key or index definition. Unfortunately, you have to write a whole bunch of code to take advantage of this technique -- or do you?

Ah the suspense...the answer is: no, you do not have to write all that code yourself! Instead, you can download, install and run the genaa.sql script. It will create the genaa procedure, which has the following header:

PROCEDURE genaa ( 
 -- name of table against which to generate the emulation code
 tab_in IN VARCHAR2 
 -- owner of table, default to USER
 , sch_in IN VARCHAR2 := NULL 
 -- delimiter to use with concatenated indexes
 , delim_in IN VARCHAR2 := '^' 
 -- name of package generated, default is [table_name]_cache
 , pkg_name_in IN VARCHAR2 := NULL 
 -- enter TRUE to display a trace of processing
 , trace_in IN BOOLEAN := FALSE 
 -- enter TRUE to send package def to file, otherwise to screen
 , to_file_in IN BOOLEAN := TRUE 
 -- name of file, default is [table_name]_cache.pkg
 , file_in IN VARCHAR2 := NULL 
 -- directory to which the file will be written
 , dir_in IN VARCHAR2 := 'DEMO' 
)

In other words: give the genaa procedure the name of the table, and it will generate all the code you need to emulate the primary key and unique indexes. It will automatically figure out the correct kind of indexing datatype to use. It even includes logic to automatically refresh your cache after a specified amount of time!

Copyright © 2003-2005 PL/Solutions. All Rights Reserved.