Oracle 9i New PL/SQL Features

In my day to day job, I usually employ PL/SQL features that have been around since the old days. I am talking about Oracle 8 here. It is not that I don't occasionally see features recently added. But I just don't have them in my repertoire. I think I got Oracle certified around the time that Oracle 9i came out. Specifics for 9i were not on my test. So I probably never studied them.

Now I am looking around for my next gig. I want it to be a database development job. Specifically I want to be an Oracle developer. So I had better know what is new. I use the term "new" loosely. Well let's pick up where I left off. What was new way back when Oracle 9i came out?

There is a new DBMS_METADATA package that can extract object definition. It will output XML or DDL. Your choice. Then there are the ANSI-standard CASE statements and expressions. The UTL_RAW package can convert to and from NUMBER and BINARY_INTEGER. There is a web version of SQL*Plus called iSQL*Plus.

You can insert a full row into a PL/SQL table now. You can create collections that are indexed by a VARCHAR2. They become hash tables. You can override an object's default constructor with your own code. The TREAT keyword allows you to specify which method you have inherited gets executed.

TIMESTAMP is a new type with fractional seconds. "Table Functions" produce a collection of rows. The result can be queried like you query with SQL. An example of the product is a nested table or VARRAY. Speaking of nested, you can nest collection types now. For example, you can have a PL/SQL table of PL/SQL tables.

You can do bulk fetches with native dynamic SQL (EXECUTE IMMEDIATE). There is a MERGE command which combines insert and update. This is something that is allegedly important for data warehousing.