02 October 2012

OOW 2012: Is PL/SQL still alive?

Yesterday Bryn Llewellyn, Distinguished Product Manager, did a session called "PL/SQL Enhancements brought by the latest generation of database technology". The room was packed. For the upcoming release of the Oracle 12c database the enhancements were broken into three areas:
  • Improved PL/SQL and SQL interoperability

    In the current version of the Oracle database you needed a Nested Table or Varray Type defined at schema level in order to be able to use it in the TABLE operator. This is no longer necessary, it can be a PL/SQL declared type.
    The types that can be used as bind variables has been extended: Boolean, Nested Tables can be used as bindvariable in Dynamic SQL.
    Subquery Factoring (the With clause if you will) can hold private functions, the use case presented for this: pretty print an integer in K, M, G, T.
    Using userdefined PL/SQL functions in a SQL statement is not advisable, but if you really need to there is a new pragma: PRAGMA UDF. With this pragma the statement is almost as fast as pure SQL.

  • Security

    A new step has been taken to continue on "least privileges", meaning that the user only gets the privileges that are absolutely necessary. A Role can be granted to a PL/SQL unit.

  • Improved programmer usability

    The ability to inspecting the callstack has been improved by the introduction of a new package: UTL_CALL_STACK. The package contains numerous functions to help in this area, such as current_edition and lexical_depth. White listing of program units: restrict which program unit can be called by other program units.

So, is PL/SQL still alive? YES! ALIVE AND KICKING!

3 comments:

  1. Hi Alex!
    Thanks for sharing this.
    Do you know where I can find a complete (reliable ?) list of PL/SQL enhancements in Oracle 12c ?
    A.

    ReplyDelete
    Replies
    1. Hi Astramierui,
      Until Oracle 12c is officially released the complete documentation will not be available.

      Alex

      Delete
  2. Hi, Nice description about Oracle.Thanks, its really helped me......

    -Aparna
    Theosoft

    ReplyDelete