Starting with Oracle10.2, Oracle now allows for PL/SQL to be coded including only certain lines of code:
· Different code for different releases of the database (selection directives)
· Checking INIT.ORA settings at compile time (inquiry directives)
· Checking for various parameter error conditions at compile time (error directives)
This coding technique was implemented in Oracle10r2 but has backward support back to Oracle9.2. Contact Oracle Support to learn how to include this technique for these earlier releases.
This coding technique can make procedures and functions database release independent! You can now have one version of the code that supports the legacy and latest features, compile with only certain INIT.ORA settings, or create error conditions based on parameters and code reminders at compile time.
Selection directives works with $IF $THEN $ELSE $ELSIF $END. At compile time, Oracle will only compile the code that tests true between these $IF directives. The DBMS_DB_VERSION package is very useful for release-specific code. This package allows you to interrogate the Oracle major and minor versions of the database at compile time. DBMS_DB_VERSION.VERSION gives the major database release (such as 10) and the DBMS_DB_VERSION.RELEASE gives the minor database release number such as 2 (…as in Oracle10r2). There are also bullions that will be set true for the release that is currently being used.
Inquiry Directives works with the $IF logic and is used to interrogate compiler settings.
Inquiry Directives example
Notice that the PLSQL_CCFLAGS can be set using any variables!
Standard PL/SQL errors can be invoked with a mix of these techniques. This might be useful to document that code has not been completed, reminders for programmers who are doing modular programming, etc. Notice the code at lines 14 thru 18.
These examples came from Oracle Magazine July / Aug 2006.
Summary
This technique will allow programmers and DBA’s to develop 1 script that will work on multiple Oracle platforms, particularly using that syntax that changes between releases of Oracle!
My 3-day Advanced PL/SQL Tips and Techniques has lab exercises using this new coding style.
· Different code for different releases of the database (selection directives)
· Checking INIT.ORA settings at compile time (inquiry directives)
· Checking for various parameter error conditions at compile time (error directives)
This coding technique was implemented in Oracle10r2 but has backward support back to Oracle9.2. Contact Oracle Support to learn how to include this technique for these earlier releases.
This coding technique can make procedures and functions database release independent! You can now have one version of the code that supports the legacy and latest features, compile with only certain INIT.ORA settings, or create error conditions based on parameters and code reminders at compile time.
Selection directives works with $IF $THEN $ELSE $ELSIF $END. At compile time, Oracle will only compile the code that tests true between these $IF directives. The DBMS_DB_VERSION package is very useful for release-specific code. This package allows you to interrogate the Oracle major and minor versions of the database at compile time. DBMS_DB_VERSION.VERSION gives the major database release (such as 10) and the DBMS_DB_VERSION.RELEASE gives the minor database release number such as 2 (…as in Oracle10r2). There are also bullions that will be set true for the release that is currently being used.
Inquiry Directives works with the $IF logic and is used to interrogate compiler settings.
Inquiry Directives example
Notice that the PLSQL_CCFLAGS can be set using any variables!
Standard PL/SQL errors can be invoked with a mix of these techniques. This might be useful to document that code has not been completed, reminders for programmers who are doing modular programming, etc. Notice the code at lines 14 thru 18.
These examples came from Oracle Magazine July / Aug 2006.
Summary
This technique will allow programmers and DBA’s to develop 1 script that will work on multiple Oracle platforms, particularly using that syntax that changes between releases of Oracle!
My 3-day Advanced PL/SQL Tips and Techniques has lab exercises using this new coding style.
No comments:
Post a Comment