PL/SQL Enhancements in Oracle 11g

Real Native Compilation

Reference: http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-new-in-11gr1-128133.pdf

• PL/SQL is an interpreted language.

• But it is not just like other interpreted languages, e.g. BASIC, the PL/SQL code is compiled into machine code called M-Code with a target virtual machine called PVM (PL/SQL Virtual Machine) (similar to JVM).

• The PVM is implemented as a set of subroutines in Oracle executables, and at run time, the M-Code is scanned by another such subroutines.

• The scanning detects each successive OPCODE and its OPERANDS, then calls the subroutine that implements this OPCODE with actual arguments. This run time scanning of the M-Code takes some resources.' This is where NATIVE compilation help with improvement.

• Compilations of both follow the same path.
  • Interpreted mode => M-Code is produced
  • Native mode => a platform-specific DLL (similar to .dll in Windows or .so in Unix) is produced.
  • This DLL = at run time, calls exactly the same PVM subroutines with same arguments as would have been called by scanning the M-Code.

• In short, the difference is that the scanning effort has been moved from runtime (when in interpreted mode) to compile time (when in native mode).

• Since exactly the same PVM subroutines are called with exactly the same arguments in both interpreted and native modes, the native mode is guaranteed to have exactly the same semantics as interpreted mode.

PL/SQL Function Result Cache

Coming soon

Access Control for UTL_TCP

• http://oraexplorer.com/2009/10/the-access-control-lists-to-network-services-e-g-utl_http-utl_smtp-utl_tcp-etc-in-oracle-11g/
• http://oraexplorer.com/2010/02/oracle-11g-network-access-denied-by-access-control-list-acl-when-using-utl_inaddr/

Create disabled trigger

• Use the disable keyword in the create to avoid the risk that if the trigger fails to compile.

Specify trigger firing order

• Use follows keyword in the create statement to control the manual firing order for triggers of the same timing point (e.g., multiple before each row triggers).

Compound trigger

See Compound Trigger.

Call sequence

• Use myseq.nextval directly without select myseq.nextval into v from dual.
• This applies to myseq.currval.