OPP/News for August 2005

Oracle Database 10g Release 2 makes an appearance

I participated in the beta testing of Oracle Database 10g Release 2, which meant that I could learn about -- and write about in my upcoming Oracle PL/SQL Programming 4th Edition (O'Reilly Media, September 2005) -- the new PL/SQL features in the latest release of Oracle. It also meant that I couldn't talk about any of it until the produce was officially released, which happened last month.

So you will be reading my articles in Oracle Magazine, Oracle Professional and on the Oracle Technology Network in the coming months, addressing such interesting new capabilities as...

  • Conditional Compilation: you can now include $IF syntax in your PL/SQL programs. The Boolean expressions in these "selection directives" are evaluated at time of compilation. You can use them to include or exclude debugging functionality, code that only works in certain versions of Oracle, and more. This feature will become much more important in post-10g releases, since conditional compilation will then be available in more than one release, but it still offers many tantalizing possibilities today (or as soon as you upgrade to this version.

  • Dynamic obfuscation of code: Oracle has long offered the wrap executable to obfuscate (make it really, really hard to read) your PL/SQL code. This feature is critical for third-party vendors who need to hide their proprietary logic, but has other uses as well. Prior to Oracle Database 10g Release 2, you could not, however, wrap code that was generated dynamically (at run-time). Now you can do that with the DBMS_DDL.WRAP and DBMS_DDL.CREATE_WRAPPED.

  • DBMS_OUTPUT.PUT_LINE enhancements: All right, I realize that dynamic obfuscation probably doesn't get your batteries all charged up. How many of you are going to ever need that? So here's one you've gotta love: you can now display strings of up to 32K characters with a call to DBMS_OUTPUT.PUT_LINE...and there is no limit to the buffer size! What a relief....now if only they would fix up the rest of this package, like support the display of Booleans, XML documents, user defined records, etc., and perform intelligent wrapping on the long strings. Well, let's not get greedy!

If you are coming to Oracle Open World this year, I will be presenting on Sunday, September 18, during the ODTUG track, and on Wednesday, September 21. In between, I will participate in some Expert Panel conversations, officially release Oracle PL/SQL Programming 4th Edition to the world, and sign a bunch of books. Hope to see you there!

Hey, don't forget!

** OOW is great, but it covers all Oracle technologies and actually pays scant attention to PL/SQL. I invite you to join me on November 2-3, 2005 for a two-day PL/SQL-only conference: Oracle PL/SQL Programming 2005

** Help influence the future of the PL/SQL language by visiting and contributing to the 21st Century PL/SQL Idea Collector.

** Check out QCGU, www.qcgu.net, which you can use to quickly generate a PL/SQL code library customized to your own database objects. We just released version 1.3, which includes Codebar, a very cool "coding toolbar" that you can use from your favorite editor to instantly access your QCGU-generated code library. QCGU and Codebar will totally transform the way you write PL/SQL applications!

Tip of the Month: Record-level DML

PL/SQL is tightly integrated with SQL, no doubt about it. That doesn't mean, however, that it isn't possible to make that integration even tighter. Oracle took another step in this direction in Oracle9i Database Release 2, when it added native support for inserting and updating rows of data with PL/SQL records.

Prior to this release, if I had filled up a %ROWTYPE record with data and wanted to insert that record into my table, I would have to separate out each of the individual fields in the INSERT statement. Here is an example. Given this table:

CREATE TABLE books (
 isbn VARCHAR2(13),
 title VARCHAR2(200),
 summary VARCHAR2(2000),
 author VARCHAR2(200),
 page_count NUMBER
)
/

I can perform a record-level insert as follows:

DECLARE
 my_book books%ROWTYPE;
BEGIN
 my_book.isbn := '1-56592-335-9';
 my_book.title := 'ORACLE PL/SQL PROGRAMMING';
 my_book.summary := 'General user guide and reference';
 my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';
 my_book.page_count := 1000;

  INSERT INTO books VALUES my_book;
END; 
/

Notice that I do not place the record inside parentheses. That difference in syntax tells Oracle that you are passing an entire record. Notice also that with this approach, you will need to have already generated the primary key value and placed it into your record, or rely on a database trigger to generate the value.

You can also perform updates with a record. The following example inserts a row into the books table with a %ROWTYPE record. Notice that I use a new keyword, ROW, to indicate that I am updating the entire row with a record: 

DECLARE
 my_book books%ROWTYPE;
BEGIN
 my_book.isbn := '1-56592-335-9';
 my_book.title := 'ORACLE PL/SQL PROGRAMMING';
 my_book.summary := 'General user guide and reference';
 my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';
 my_book.page_count := 980; -- new page count for 3rd edition 
 
 UPDATE books
 SET ROW = my_book
 WHERE isbn = my_book.isbn;
END;
/

There are some restrictions on record-based updates:

  • You must update an entire row with the ROW syntax. You cannot update a subset of columns based on a user-defined record type.

  • You cannot perform an update using a subquery.

I think that generally we will find record-based INSERTs more useful than record-based UPDATEs. Regardless, it is always a pleasure to see (and use) a PL/SQL that is ever more tightly integrated with the underlying SQL language!

Useful Code of the Month: Is element found in collection?

Oracle Database 10g allows you to use MEMBER OF syntax to determine if a particular values is a "member of" a nested table. Here is an example:

DECLARE
 TYPE clientele IS TABLE OF VARCHAR2 (64);
 client_list_12 clientele :=
 clientele ('Customer 1', 'Customer 2'); 
BEGIN
 IF 'Customer 1' MEMBER OF client_list_12
 THEN
 DBMS_OUTPUT.put_line ('Customer 1 is in the 12 list');
 END IF;

 IF 'Customer 3' NOT MEMBER OF client_list_12
 THEN
 DBMS_OUTPUT.put_line ('Customer 3 is not in the 12 list');
 END IF;
END;
/

That's all very wonderful – unless you are not yet running Oracle Database 10g or you are working with associative arrays (declared with INDEX BY syntax), which includes many of us. So I built a general template into QCGU that allows you to generate a function that accepts a collection of the type you specify at time of generation, and a value, and returns TRUE if that value is found in an index of the collection. If you have installed QCGU, search for Universal ID {5ADA17C8-60A3-47D3-993F-72A673393909} in Explorer. You can then generate a function for your specific type.

For the rest of you, I have generated a function that works with DBMS_SQL.VARCHAR2S. You can replace references to this type with your own collection type, and then use it in your own environment. This download also includes test scripts (for utPLSQL and native PL/SQL execution) to verify that the program works correctly.

Recent PL/SQL Writings of Note

You might find these interesting...

  • My latest article published in the Oracle Professional newsletter: "A very common requirement among developers is to be able to use a dynamic set of values in the IN clause. That is, at the time you write your program, you don't know if you'll have one, five, or 75 values to be placed inside the list for IN. In this article, Steven Feuerstein explains various options for implementing a dynamic IN clause."

  • My latest answers on the OTN Best Practice PL/SQL page: "No more WHERE CURRENT OF?" discusses how you can and cannot use that feature with BULK processing. "Optimizing PL/SQL Code Performance" offers a handy high-level overview of steps you can take to improve PL/SQL performance.

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