msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Oracle Database 11g New PL/SQL Features in your 10g Code Today

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):


Filed in Oracle on 01 Aug 07 | Tags:


Reader's Comments

  1. |

    That’s real good. However, if upgrade 9i to 11g … …

  2. |

    Zeeno, The following is an excerpt from a presentation by Bryn Llewellyn, PL/SQL Product Manager at Oracle, about PL/SQL conditional compilation:

    • In 9.2.0.6 it’s disabled. You have to use an “underscore parameter” to enable it.
    • In 10.1.0.4 – while it is enabled by default – you can disable it by using the same “underscore parameter”.
    • In 10.2 this “underscore parameter” is obsolete.
  3. |

    Eddie,

    Excellent post! Kudos to Oracle for adding these features but kudos to you for thinking how to use current features to prepare for future features.

    LewisC

  4. |

    That’s a great use for conditional compilation, perhaps for simple scenarios like you exemplify here; however I’d never recommend putting untested code in production.

    When you upgrade to 11g obviously you’d test the upgrade in a non-production environment. This could be 6+ months from now, and might not necessarily be you; so someone will have the burden of testing your code, which might not compile, or worse will have unwanted side effects.

    Kudos for getting us thinking about the new features, though. Thanks