In this post, I’d like to introduce 5 of the many new features Oracle 12C brings to us, database developer’s.
Of course this blog would be to long to explain them all in detail, so I will stick
to a small introduction.
- Generating a primary key without triggers, using nextval or identity
In 12C, you are now able to use sequence.nextval or the new keyword ‘identity’ as default values.
The ‘identity’ keyword will generate the value max(id)+1 for your primary key. So now you don’t need to create triggers anymore, when generating PK’s with a sequence.
And problems with sequences that are not in sync, when moving/copying tables to another schema/database, can be avoided by using the ‘identity’ keyword.
Example PK row declaration :
id_pers number default person_seq.nextval primary key;
id_pers number generated as identity;
- Accessible key word : define which code can call your function/procedure.
One of the major problems of PL/SQL is, when developing a lot of packages/procedures/functions, in the end there is no telling who is called by who. This problem can now be answered by ‘white listing’. This means that, on creation, you are telling the package/function/procedure/type by whom it is accessible, or may be used.
The accessible by clause takes packages/functions/procedures/triggers as accessor clause.
Example white listing :
- create procedure get_sales_data accessible by (my_sales_proc)…
– create procedure get_sales_data accessible by (my_after_update_trigger)
- create package my_package accessible by (my_other_package)…When the object is not accessible, following error will be thrown during compilation, or at runtime, in case of an anonymous PL/SQL block :PLS-00904: insufficient privilege to access object MY_PACKAGE.MY_PROCEDURE
- Temporal Validity of a row
Sometimes rows in a table are valid or not, depending on a timeframe. For instance a subscription for a magazine, may only be valid for a year. Adding this validity to a row goes as follows :
create table subscriptions
( person_id number,
period for valid(subscr_start_date , subscr_end_date)
Now with following query we can select the ‘valid’ subscriptions :
select * from subscriptions
as of period for valid sysdate;
- New PL/SQL Package UTL_CALL_STACK
The UTL_CALL_STACK package provides subprograms to return the current call stack for a PL/SQL program. This could already be done by DBMS_UTILITY.FORMAT_CALL_STACK, but this new package returns this information in a more structured way, and includes the depth of the call (calling level) and the names of the subprograms.
This will make this information more usable in code. Related to this subject, 2 new directives are added in 12c, next to $$PLSQL_LINE and $$PLSQL_UNIT that already existed).
dbms_output_put.line("Owner of this package is "+$$PLSQL_OWNER);
Will print : Owner of this package is SCOTT
- An Invoker’s Rights Function Can Be Result Cached
Caching results of a PL/SQL function already exists in 11g. Basically what happens is that, for a certain function, you define that the result, for given parameters should be cached in memory.
So first time function getPerson(123) is executed, the data is fetched from the database, second time the function is called with parameter ‘123’, the result is fetched from the cache in memory, resulting in a better performance.
Whenever a DML statement is executed on the table(s) used in that function, the cache is automatically cleared, causing the next call to return the new data. (Since 11G rel. 2, Oracle manages these dependencies himself.)
So in our case, Oracle caches the result’s of function getPerson() for every key it is called with.
Through Oracle Database 11g Release 2 (11.2), only definer’s rights PL/SQL functions could be result cached. Now in 12c, the identity of the invoker is implicitly added to that key.
As already mentioned, the possibilities of these new features go way beyond what I describe here. But hopefully it’s a start to a few experiments on your side !
More info can be found at http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm
Did you ever needed your table data in an XML format?
Well, it isn’t that hard… Oracle provides several ways to generate XML, in both SQL and PL/SQL.
I wrote an article on All Things Oracle in which I explain step by step how you can generate XML from table data.
In this first part I show you some SQL functions which are easy to use. Check it out!
There’s a new Oracle source available: All Things Oracle.
The aim of All Things Oracle is to provide a gateway to the wealth of information and material available for Oracle developers and DBAs.
The site brings articles and other resources of Oracle experts.
Just to name a few:
All very experienced experts that will bring interesting articles!
I will also contribute to this site.
My specialities are SQL, PL/SQL, Forms and Forms Modernization, so expect articles on these topics in the near future on All Things Oracle.
For over a year I play every day the PL/SQL Challenge, as I mentioned already in previous posts.
Today there’s version 2 of the website.
You can read about it on the PL/SQL Challenge blog.
Great effort of Steven Feuerstein and his team!
So, if you want to test your PL/SQL knowledge, compete against collegues/other PL/SQL programmers or you want to know/learn more about PL/SQL… Check it out!
One year ago Steven Feuerstein launched a competition called “The Pl/SQL Challenge“.
The idea was to let Oracle PL/SQL developers test their knowledge, learn and win prices by taking a daily quiz.
The format seems to be a success: every day more than 1000 PL/SQL developers test their knowledge about the language, in total more than 5800 users from 119 countries… a worldwide success!
But it does not stop with doing a quiz, a lot of questions(and answers) are discussed on the blog.
And those discussions can also lead to something, like an enhancement request for the PL/SQL language by Bryn Llewellyn(PL/SQL Product Manager at Oracle).
Steven did a great job with the PL/SQL Challenge and it seems like it keeps on rolling: version 2 of the website is coming up with even more challenges for SQL and APEX.
To Steven and his team of reviewers and developers: thanks for the nice quizzes and keep up te great work!
To all players: have fun and good luck!
On friday my quiz was on the PL/SQL Challenge.
Topic: “Guidelines for Designing Triggers: Avoid Non-Transactional Logic in DML Triggers”
Yes, about “statement restart”
My idea about the quiz was “making developers aware of the Oracle behaviour”.
And I hope a lot of PL/SQL developers learned from it.
It wasn’t easy to come up with the question, the way of asking, the correct words, the answers…
But after a lot of mailing between Steven and the reviewers we finally had a quiz.
A lot people had it wrong, but I hope they don’t mind and are now aware of statement restart.
For more information and discussion about the quiz: PL/SQL Challenge blog
Steven Feuerstein started last year the PL/SQL Challenge, a quiz about…PL/SQL!
What else could one expect from Steven
The PL/SQL Challenge is an interesting quiz to test your PL/SQL knowledge and win some great prizes(up to $1000!).
Not only it’s about testing your knowledge, but also about learning a lot PL/SQL(even experienced developers can learn!).
At iAdvise and O2U we have some great PL/SQL developers and to prove that: we are the number one company in the PL/SQL challenge in the last quarter of 2010 and leading in this quarter.
We have three players in the top 100 this quarter of which one is in the top 20. And this with over a 1000 participants each day. Not bad I would say.
Not only the experienced players are participating and doing very well, but also our young Apex developers are doing great(one of them was in the beginners top 10 in December).
In the days of Java, E2.0, SOA, Fusion etc… don’t forget the roots: PL/SQL is still the preferred language on the database!
So, come out and play…and maybe win some prizes at the PL/SQL Challenge.
In my previous post I mentioned the problem with compiling a program unit that calls a database function with result cache.
Like I wrote in that post, the problem exists in the PL/SQL Client version.
There’s a patch available to update the PL/SQL client: Oracle Database Server Version 188.8.131.52 Patch 33.
This is a database patch, but it can also be applied to the middleware home.
After applying the patch, the program unit compiles…Problem solved!
So, go and start using Result cache!