Oracle 10g New PL/SQL Features

I am on a mission to learn what new features were added to PL/SQL over the years. Now onto what was new in Oracle 10g. There are new BINARY_FLOAT and BINARY_DOUBLE types. They adhere to the IEEE definitions. FORALL let's you transfer data in sparse collections to the database. Use the keywords INDICES OF or VALUES OF.

Regular expressions can now be used in SQL queries. Here are the functions:

• REGEXP_LIKE
• REGEXP_INSTR
• REGEXP_SUBSTR
• REGEXP_REPLACE

Nested tables have some improvements. They support multiple set operations. For example, you can use EQUALS, UNION, INTERSECT, and EXCEPT on them. Collections can also be assigned to other collections. There is a user defined quote character. You choose the character. It cannot appear in your string. When you use it, single quotes can be in the middle of your string.

 A new UTL_MAIL package sends mail programmatically without having to know the low level details of the SMTP protocol. A new UTL_COMPRESS package lets you zip and unzip data. It works with RAW/BLOB/BFILE types.

There is now conditional compilation of PL/SQL. You can get the current version with DBMS_DB_VERSION. Then you can use conditional $IF expression $THEN statement syntax. There is also $ERROR handling.

DBMS_DDL allows you to hide PL/SQL source code from users. Use the CREATE_WRAPPED keyword. The PLS_INTEGER grows in size to match BINARY_INTEGER. Implicit conversion can now be done between CLOB and NCLOB. Watch out for performance implications though.

DBMS_UTILITY gets the FORMAT_ERROR_BACKTRACE method to print out the call stack during exception processing. COLLECT is a new group function that gathers GROUP records and rturns them in a collection. You can write and read external tables.