Good-fast-cheap. Pick two. - I got invited to a meeting with the customer today. There was a problem in production. And the customer wanted answers. When it came time, I explained wha...
Oracle 9i New PL/SQL Features
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.