So what's planned for the upcoming 12c database regarding PL/SQL? What follows is a list of what are presented as the most important changes...
Improved PL/SQL - SQL interoperability
- You don't need to specify the type at the schema level when you want to use it SQL, specification at the package level is sufficient and you can bind it to SQL directly;
- Get the performance of SQL with the clarity and reusability of PL/SQL by adding a pragma UDF to a schema level PL/SQL function - almost as fast as pure SQL;
- You can declare a PL/SQL function in the WITH clause of a subquery: with function x(param) <body> end x; select x(p) from t;
- Using the "with" construct is 3.8x faster than using an old fashioned PL/SQL function, function with pragma is 3.9x faster, pure SQL is 5x faster.
A new security capability
- Grant access to a PL/SQL unit by granting a role to a PL/SQL unit, where the role contains the required rights for accessing the tables - so a function can only select, update etc from a table where it has been granted access to.
Improved programmers usability
- New package UTL_CALL_STACK for better display of errors (with functions like dynamic_depth(), unit_line(), subprogram(), concatenate_subprogram(), owner(), current_edition(), lexical_depth());
- You can whitelist units that can reference a particular unit to prevent unwanted calls: add "accessible by (proc1, proc2)" to the unit definition.
Comments
Thanks for sharing this.
Do you know where I can find a complete (reliable ?) list of PL/SQL enhancements in Oracle 12c ?
A.