Skip to main content

Posts

Showing posts from October, 2012

OOW2012 - PL/SQL Enhancements in Oracle 12c

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, updat…

OOW2012 - The Oracle Database 12c: The Pluggable Database

To me, one of the biggest announcements this week was not that Larry finally learnt how to use a clicker, but the disclosure of some of the new features of the next version of the Oracle Database. This version is also the "first real multi-tenant database". So what does this mean?
In the current version of the database the core Oracle data dictionary is mixed up with the tables, packages etc you created yourself. All information is stored in the same obj$, tab$ and source$ tables. In 12c there is an architectural separation between the core Oracle system and your own application. The core Oracle data dictionary is called the Container Database (CDB). The part of the database that contains your own code is called the Pluggable Database (PDB). This PDB also contains its own datadictionary with it own obj$, tab$ etc tables. So it is a sort of hierarchy: objects are first located in the PDB, when they're not found there, information from the CDB is retrieved.  The best thing …