PL/SQL Conditional Compilation - Oracle
PL/SQL conditional compilationAn Oracle White PaperOctober 2005
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfNOTEThe following is intended to outline our general product direction. It is intendedfor information purposes only, and may not be incorporated into any contract. Itis not a commitment to deliver any material, code, or functionality, and shouldnot be relied upon in making purchasing decisions. The development, release,and timing of any features or functionality described for Oracle’s productsremains at the sole discretion of Oracle.PL/SQL conditional compilation
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfPL/SQL conditional compilationCONTENTSAbstract. 1Introduction. 2. 5The selection directive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6The inquiry directive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9The error directive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12Choosing between an inquiry directive and a static package constant . . . . . . . 14The DBMS DB Version package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15PL/SQL conditional compilation constructs.The PL/SQL compilation pipeline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using the DBMS Preprocessor package to seethe conditional compilation output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Choosing between the compile-time if constructand the run-time if construct . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1717.Latent self-tracing code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Latent assertions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Unit testing of subprograms declared only in a package body . . . . . . . . . . .Mock objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Comparing competing implementations during prototyping . . . . . . . . . . . .Component based installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Spanning different releases of Oracle Databasewith a single source code corpus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23232427343740How does PL/SQL conditional compilation work?PL/SQL conditional compilation use casesCase study: implementing unit testing, assertions,and tracing for a fast cube rootbody-private helper function . . . . . . . . . . . . . . . . .Introduction to the case study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .The design of the Fast Cbrt() algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . .The requirements for the unit tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Discussion of the PL/SQL implementation . . . . . . . . . . . . . . . . . . . . . . . . .The test results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Conclusion to the Fast Cbrt() case study . . . . . . . . . . . . . . . . . . . . . . . . . . . .How does PL/SQL conditional compilation compare withsimilar features in other programming environments? .The availability of PL/SQL conditional compilationin Oracle Database 10g Release 1 andin Oracle9i Database Release 2 . . . . . . . . . . . . . .17204649495050515555. . . . . . . . . . . . . . . . 57. . . . . . . . . . . . . . . . . . . . . . 59The Catch 22 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59PL/SQL conditional compilation
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfThe decision to make PL/SQL conditional compilationavailable in 10.1 and in 9.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .The PL/SQL conditional compilation underscore parameter . . . . . . . . . . .Functionality restrictions in 10.1 and 9.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . .The purpose of the Ver LE constantsin the DBMS DB Version package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Concluding remarks64. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66Appendix A:Change History.20-September-2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26-September-2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14-October-2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18-October-2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10-November-2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Appendix B:Oracle Database Documentation Library referencesAppendix D:Self-contained SQL*Plus scriptfrom which Code 44 is an extract676767676767. . . . . . . . . . . . . . . . . . . 68Appendix C:The source code of the DBMS DB Version packagein 10.2, 10.1, and 9.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70Appendix E:Tracking information from the Bug Database. . . . . . . . . . . . . . . . . . . . . . . . . 71Appendix F:The Newton-Raphson formula for improvingan approximation for the cube root of a numberAppendix G:SQL*Plus scripts for the case studyPL/SQL conditional compilation606161. . . . . . . . . . . . . . . . . . . . . . 72. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfPL/SQL conditional compilationABSTRACTOracle Database 10g Release 2 delivers a new PL/SQL language feature:conditional compilation.The feature is elegant, easy to understand, and has many interesting uses; someof these may not have occurred to you. This paper illustrates conditionalcompilation with code samples and demonstrates every conditional compilationconstruct. It recommends best practices and discusses alternativeimplementations.Unusually, but for very compelling reasons, the feature has been made availablein patchsets of releases of Oracle Database earlier than the one that introducedthe feature. It is available, with some functionality restrictions, in the first releaseof Oracle Database 10g from 10.1.0.4 onwards and in Oracle9i Database from9.2.0.6 onwards. However, customers who do not use the new conditionalcompilation constructs will see no change whatsoever in the way their PL/SQLprograms are compiled; this is true for all of the releases that support PL/SQLconditional compilation.The paper’s final section explains the restrictions in the feature’s functionality inthese earlier releases and the rationale for the decision to make the feature soavailable. Oracle Independent Software Vendors, and in particular Oracle’sApplications Division, are most likely to benefit from this retrospectiveavailability. But any customer who needs to deploy the same application indifferent releases of Oracle Database might find this useful.This paper does not attempt to be a reference manual for the feature. Rather, itassumes an understanding of the functionality and the syntax. It does, however,describe some aspects of the feature which — for reasons which will becomeobvious — are not described in the Documentation Library.PL/SQL conditional compilationpage 1
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfINTRODUCTION“We participated in the Beta Program forOracle Database 10g Release 2 andextensively tested PL/SQL conditionalcompilation. We found it functionallycomplete and easy to use. It will allow us towrite a more manageable and fasterimplementation for our component-basedarchitecture. We intend to use it in ourproducts at the earliest opportunity.”— Håkan ArpforsSenior Software ArchitectIFSwww.ifsworld.comOracle Database 10g Release 2 delivers a new PL/SQL language feature:conditional compilation. The feature is elegant and easy to understand.Conditional compilation delivers many benefits and is well known inprogramming environments other than PL/SQL1. Broadly speaking, it allowsconstructs — with formally defined syntax and semantics — to be used to markup text so that a so-called preprocessor2 can deterministically derive the text thatwill be submitted to the compiler proper. The following two uses are, perhaps,the most famous. Allowing self-tracing code to be turned on during development and to beturned off when the code goes live. PL/SQL conditional compilation supportsthis use in a direct and obvious way (see Latent self-tracing code on page 23). Allowing alternative code fragments, each appropriate for the peculiarities of aparticular operating system and inappropriate or illegal for other operatingsystems, to coexist in the same source text so the correct fragment can beselected for compilation according to the circumstances. PL/SQL is, by itsnature, operating system independent; but analogous challenges presentthemselves when a PL/SQL compilation unit must be deployed in severaldifferent releases of Oracle Database. Newer releases introduce new featureswith new syntax and programs that take advantage of these are illegal in earlierreleases. PL/SQL conditional compilation supports this use in an elegant andpowerful way (see Spanning different releases of Oracle Database with a single sourcecode corpus on page 46).There are many other uses; the following cases have been selected for discussionin this paper. (The order is the most natural for explanation. Readers may decidewhich they find the most valuable.) Allowing assertions3 to be turned on during development and to be turned offwhen the code goes live. PL/SQL conditional compilation supports this use ina direct and obvious way (see Latent assertions on page 24). PL/SQL conditional compilation allows new approaches to unit testing. Forexample, tests for private helper subprograms may be coded in the body of thepackage that contains them (see Unit testing of subprograms declared only in a packagebody on page 27). Sometimes the unit test for a particular subprogram needs to demonstrate thatit handles exceptional conditions raised by the subprograms it calls. However,it can be difficult to contrive these problems at will. PL/SQL conditionalPL/SQL conditional compilation1.See www.google.com/search?q %22conditional compilation%222.Readers who are familiar with other preprocessors might appreciate a pointer to the sectionHow does PL/SQL conditional compilation compare with similar features in other programmingenvironments? on page 57.3.Roughly, an assertion is a test — which necessarily takes time — that program state at aparticular point in the code is as expected; typically, in PL/SQL, an exception is raised if thetest fails.page 2
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfcompilation supports this testing requirement in a simple way (see Mock objectson page 34). A developer often realizes that more than one approach to the design of asubprogram will result in its correct behavior; sometimes the alternativeapproaches result in source code versions which are textually largely the samebut which differ critically in small areas distributed fairly evenly thought thesource. PL/SQL conditional compilation allows all the approaches to becoded in a single source text — while they are being evaluated — and therebyeliminates the risk of carelessly introduced unintended differences (seeComparing competing implementations during prototyping on page 37). Oracle ISVs sometimes sell applications which provide optional extrafunctionality for incremental cost. The modular delivery is implemented byoptional PL/SQL compilation units which are installed according to what thecustomer has licensed. PL/SQL’s dependency model prevents the core part ofthe application referring statically to optional components that are notinstalled. However, the core part of the application should not need reinstallation in order to accommodate the installation of a new optionalcomponent. This has forced the use of dynamic invocation — which has somedrawbacks. Conditional compilation allows a new approach. (see Componentbased installation on page 40).Before the discussion of the use cases, the section PL/SQL conditional compilationconstructs on page 5 illustrates the full set of primitives that expose the feature;and the section How does PL/SQL conditional compilation work? on page 17 explainsthe introduction of conditional compilation as a new stage in the compilationpipeline and shows how the programmer can inspect its output.It is always hard to find illustrative code samples that are, on the one hand, briefand easy to present and are, on the other hand, sufficiently non-trivial that theyunequivocally show the benefit of a feature without making large demands onthe reader’s ability to extrapolate. The code included in the section PL/SQLconditional compilation use cases on page 23 errs on the side of triviality. Tocompensate, the section Case study: implementing unit testing, assertions, and tracing for afast cube root body-private helper function on page 49 is so realistic that the complete,self-contained SQL*Plus scripts occupy about ten pages. Reading this section isoptional; but those who do study it will be rewarded by seeing the approachesdescribed in the individual use cases applied in concert to solve a real problem inan effective and highly usable way.The section How does PL/SQL conditional compilation compare with similar features inother programming environments? on page 57 addresses questions that programmerswho are familiar with other implementations of conditional compilation mightnaturally ask.Unusually, but for very compelling reasons, PL/SQL conditional compilationhas been made available in patchsets of releases of Oracle Database earlier thanthe one that introduced the feature. It is available in the first release of OracleDatabase 10g from 10.1.0.4 onwards and in Oracle9i Database from 9.2.0.6onwards4. However, customers who do not use the new conditional compilationconstructs5 will see no change whatsoever in the way their PL/SQL programsare compiled; this is true for all of the releases that support PL/SQL conditionalcompilation.PL/SQL conditional compilationpage 3
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfThe paper’s final section, The availability of PL/SQL conditional compilation in OracleDatabase 10g Release 1 and in Oracle9i Database Release 2 on page 59, explains therestrictions in the feature’s functionality in these earlier releases and the rationalefor the decision to make the feature so available.This paper does not attempt to be a reference manual for the feature. Rather, itassumes an understanding of the functionality and the syntax. References to theproduct documentation are given in Appendix B: Oracle Database DocumentationLibrary references on page 68. It does, however, describe some aspects of thefeature which — for reasons which will become obvious — are not described inthe Documentation Library.Sadly, but realistically, this paper is likely to have minor spelling and grammarerrors. For that reason alone, it is bound to be revised periodically6. Otherinteresting applications of PL/SQL conditional compilation cases might come toour attention and — it is hoped — the paper will be revised to discuss them.Therefore, before settling down to study the paper, readers should ensure thatthey have the latest copy — for which the URL is given in the page’s header.URLs sometimes change. But this one will always take you to the OracleTechnical Network’s PL/SQL Technology Center:www.oracle.com/technology/tech/pl sqlEven in the unlikely event that the paper is moved, it will still be easy to findfrom that page.4.In 10.1.0.4, the feature is available by default but can be totally disabled by setting the PL/SQLconditional compilation underscore parameter to “disable condtional compilation”.In 9.2.0.6, the feature is totally disabled by default but can be made available by setting thePL/SQL conditional compilation underscore parameter to “enable condtional compilation”.In Oracle Database 10g Release 2 and later, the feature cannot be disabled and the PL/SQLconditional compilation underscore parameter is obsolete.PL/SQL conditional compilation5.Any of the new PL/SQL conditional compilation constructs, if used in a release of OracleDatabase that does not support the feature, will cause a compilation error. This guarantees thatno compilable extant code will be affected in any way when it is compiled in a release that doessupport the feature.6.This document’s change history is listed at the end. See Appendix A: Change History on page 67.page 4
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfPL/SQL CONDITIONAL COMPILATION CONSTRUCTSFirst, for readers who are new to this feature, Code 17 — so far without anexplanation8 — shows a simple example as a self-contained SQL*Plus script9,10.Here, as in all the code examples in this paper, the PL/SQL conditionalcompilation constructs are emphasized typographically.-- Code 1alter session set PLSQL CCFlags 'Use IEEE:2'/create or replace procedure P is-- Notice that if . end interrupts a regular statement.n if Use IEEE 0 then number; elsif Use IEEE 1 then binary float; else error 'Illegal Use IEEE: ' Use IEEE end endbegin if Use IEEE 0 thenn : 1.0; elsen : 1.0f; endPrint(n);end P;/SHOW ERRORSalter procedure P compilePLSQL CCFlags 'Use IEEE:0' reuse settings/begin P(); end;/alter procedure P compilePLSQL CCFlags 'Use IEEE:1' reuse settings/begin P(); end;/Code 1 produces this output:.5/11PLS-00179: ERROR: Illegal Use IEEE: 211.0E 000The way n is rendered in the two versions reflects the rules for the defaultconversion of, respectively, a number and a binary float variable to varchar2.7.I don’t like to split code illustrations across page boundaries. That’s why you’ll sometimes seeunused white space at the bottom of a page.8.The meaning of constructs if, then, elsif, else, and end can easily be guessed. Readers whoare impatient to understand the object of the test, Use IEEE, and how it is defined usingthe new PL/SQL compilation parameter PLSQL CCFlags, can look ahead to the section Theinquiry directive on page 9. And those who are impatient to understand the error construct canlook ahead to the section The error directive on page 12.9.The schema-level procedure Print() — a simple wrapper for DBMS Output.Put Line(), andeffectively a “synonym” for that packaged procedure — is used in the code samples to shortenthem.10. When I write Some Proc() I mean the procedure or function itself. (It might be declared atschema level, at top level in a package, or nested to any deep level in a declare. begin. end block.)When I write Some Proc — without the trailing parentheses — I mean the schema levelcompilation unit itself.PL/SQL conditional compilationpage 5
10-November-2005www.oracle.com/technology/tech/pl sql/pdf/Plsql Conditional Compilation.pdfThere are three kinds of PL/SQL conditional compilation construct: the selectiondirective, the inquiry directive, and the error directive. Code 1 uses a
Oracle Database 10g Release 2 delivers a new PL/SQL language feature: conditional compilation. The feature is elegant and easy to understand. Conditional compilation delivers many benefits and is well known in programming environments other than PL/SQL1. Broadly speaking, it allows
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Oracle is a registered trademark and Developer/2000, Oracle8, Oracle Application Object Library, Oracle Alert, Oracle Financials, Oracle Quality, Oracle Workflow, Oracle Work in Process, SQL*Forms, SQL*Plus, SQL*AMX, SQL*Report, and SQL*ReportWriter are
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
Logical SQL statements understood by the Oracle BI Server. Logical SQL includes standard SQL, plus special functions (SQL extensions) such as AGO, TODATE, EVALUATE, and others. Logical SQL queries resolve to Presentation layer objects. This guide contains the following topics: About Logical SQL in Oracle Business Intelligence SQL Syntax .
SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact- SQL). T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capab
MS SQL Server: MS SQL Server 2017, MS SQL Server 2016, MS SQL Server 2014, MS SQL Server 2012, MS SQL Server 2008 R2, 2008, 2008 (64 bit), 2008 Express, MS SQL Server 2005, 2005 (64 bit), 2005 Express, MS SQL Server 2000, 2000 (64 bit), 7.0 and mixed formats. To install the software, follow the steps: 1. Double-click Stellar Repair for MS SQL.exe.
Server 2005 , SQL Server 2008 , SQL Server 2008 R2 , SQL Server 2012 , SQL Server 2014 , SQL Server 2005 Express Edition , SQL Server 2008 Express SQL Server 2008 R2 Express , SQL Server 2012 Express , SQL Server 2014 Express .NET Framework 4.0, .NET Framework 2.0,
Keywords: Korean, heritage language, multiliteracies, university-level language classroom, multimodal reading response Journal of Language and Literacy Education Vol. 11 Issue 2—Fall 2015 117 eritage language (HL) learners1 who are exposed to and speak a language other than English exclusively in their homes and communities exhibit relatively lower reading and writing skills compared to .
