Oracle 11g New PL/SQL Features

All right. We are now at the point where I am learning features of the current Oracle database we run in production. There is a DBMS_UPDATE_PARALLEL that let's you group sets of rows into chunks. Then you can update the chunk for performance gains. Regular expressions get a new REGEXP_COUNT method.

You can use CURRVAL and NEXTVAL in PL/SQL without having to SELECT them from DUAL. You get all kinds of options for specifying parameters in function/procedure calls. You can still do the old positional method. But you can also do the named method. Or you could combine positional and named in what we call a mixed style.

Big news. There is a RESULT_CACHE directive you can put at the end of a function declaration. Results of the query get cached in the SGA. This can speed things up considerably. There are some limitations:
  • Does not work in an anonymous block
  • Does not work with invoker's rights
  • There can be no OUT parameters
  • The function cannot access a data dictionary
  • The function cannot access a temporary table
  • The function cannot access a sequence
  • The function cannot access a SQL function
  • No parameters can be any type of LOB
  • No parameters can be a REF CURSOR
  • No parameters can be a collection
  • No parameters can be an object
  • No parameters can be a record
  • Return type cannot be any of these prohibited parameter types either
Whew. That was a lot of restrictions. I am sure there are good reasons for them.  Now back to the new stuff. FOLLOWS can be used when defining triggers to set the order of triggers on the same table at the same firing point. PRAGMA_INLINE can inline a subprogram go speed.

New types SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE are what you would expect. They have the added restriction that they cannot be NULL. CONTINUE can be used to move to the next iteration in a loop. Compound triggers can put triggers for more than one firing point together. This let's you share code and data between them. They all have to be DML triggers. You can only GOTO code in your own section. Other restriction that make sense are that :NEW can only be changed BEFORE EACH ROW. And :OLD and :NEW cannot be referenced in the before and after statement triggers.

There is a new syntax to specify the parent you inherited a method from. It looks like (SELF AS my_parent).my_method(). FORALL can not reference specific elements in collections. This can be done in the SET and WHERE clauses. LISAGG is a new built in function that does string aggregation. That is, it can turn values from multiple rows into one big concatenated row.

COLLECTION improvements have been made. You can CAST to a specific type of collection. You can order the elements in a COLLECTION. You can specify that the collection has only DISTINCT values in it.

Virtual columns can be added to tables. This let's you store an expression in something that behaves like a column. Syntax involves GENERATE ALWAYS AS ... VIRTUAL during column definition. It can be index. It can be part of a constraint. This makes it act like a view.

Hey. PIVOT is a new keyword to transpose rows to columns. This is good for applications such as reports. It is an aggregate operation. There is a reverse UNPIVOT that transposes columns to rows instead.