So, you are all excited about the new PL/SQL features in Oracle database 11g and you cannot wait until you upgrade your 10g database. Well, you do not have to wait. I will show you a simple way to put, and successfully compile, any new 11g PL/SQL feature inside your 10g PL/SQL code.
Let’s say you have just read about this new feature in the Oracle 11g database called “Function Result Cache”, which allows you to request that the function’s result be cached after the first call resulting in a much faster execution of this function in subsequent calls.
Function Result Cache is a great new 11g feature. But, too bad you are still on 10g. You have two options, the first is to wait until you upgrade to 11g, which, I suspect, may not be anytime soon. The second option is to prepare your 10g PL/SQL code to use this 11g feature now, while it is fresh in your mind. To be able to do this, you will have to use the combination of DBMS_DB_VERSION and conditional compilation.
Here is an example (tested in 10g XE in the HR schema):
SQL> CREATE OR REPLACE FUNCTION all_regions 2 RETURN sys_refcursor 3 4 $IF DBMS_DB_VERSION.VER_LE_10_2 5 $THEN 6 $ELSE 7 result_cache relies_on (regions) 8 $END 9 IS 10 l_regions_cur sys_refcursor; 11 BEGIN 12 OPEN l_regions_cur FOR 13 SELECT * 14 FROM regions; 15 RETURN l_regions_cur; 16 END all_regions; 17 / Function created.
Lines four to eight mean that if the database version is less than or equal to 10.2 then do nothing. If the database version is greater than 10.2, like 11.0 for example, then instruct the compiler to include
result_cache relies_on (regions) in the compilation of the program.
Let’s test calling the function:
SQL> DECLARE 2 l_regions_cur sys_refcursor; 3 l_regions_rt regions%ROWTYPE; 4 BEGIN 5 l_regions_cur := all_regions; 6 LOOP 7 FETCH l_regions_cur 8 INTO l_regions_rt; 9 EXIT WHEN l_regions_cur%NOTFOUND; 10 DBMS_OUTPUT.put_line (l_regions_rt.region_name); 11 END LOOP; 12 CLOSE l_regions_cur; 13 END; 14 / Europe Americas Asia Middle East and Africa PL/SQL procedure successfully completed.
So, when you upgrade to 11g, all what you have to do is to re-compile your stored function and, boom!, the 11g new feature will be activated. Nice! Of course, in real world, you will have stored packages instead of standalone functions and procedures.
Note that I have not tested the above code in 11g. If you are an 11g beta tester, I would appreciate it if you let me know if the above works as expected.
Here are a few new PL/SQL features in 11g that Jurgen Kemmelings from AMIS has blogged about and that you may want to “conditionally compile” in your 10g database:
And here are a couple of white papers from Oracle (PDF):