Oracle9i SQLJ Developer's Guide and Reference

This manual is intended for anyone with an interest in SQLJ programming but assumes at least some prior knowledge of the following:■ Java■ SQL■ Oracle PL/SQL■ JDBC■ Oracle databasesAlthough general knowledge of SQL and JDBC is sufficient, any knowledge of Oracle-specific SQL and JDBC features woidd be helpful as well.See "Related Documents" on page xix below for the names of Oracle documents that discuss SQL and JDBC. Send Us Your Comments......Page 15 Preface......Page 17 1 Overview......Page 25 Basic Concepts......Page 26 Java and SQLJ versus PL/SQL......Page 27 SQLJ Translator and SQLJ Runtime......Page 28 SQLJ Profiles......Page 29 Overview of Oracle Extensions to the SQLJ Standard......Page 31 Translation Steps......Page 33 Summary of Translator Input and Output......Page 36 Runtime Processing......Page 39 Running SQLJ in Applets......Page 40 Introduction to SQLJ in the Server......Page 44 Using SQLJ with Oracle Lite......Page 46 SQLJ in JDeveloper and Other IDEs......Page 48 Windows Considerations......Page 49 2 Getting Started......Page 51 Assumptions About Your Environment......Page 52 Requirements for Using Oracle SQLJ......Page 53 Supported JDK Versions......Page 54 Oracle SQLJ Backwards Compatibility......Page 56 Oracle JVM Configuration......Page 57 Set the Path and Classpath......Page 58 Verify Installation of sqljutl Package......Page 60 Set Up the Runtime Connection......Page 61 Verify the JDBC Driver......Page 63 Verify the SQLJ Translator Connection to the Database......Page 64 3 Basic Language Features......Page 67 Rules for SQLJ Declarations......Page 68 Iterator Declarations......Page 69 Connection Context Declarations......Page 70 Declaration IMPLEMENTS Clause......Page 71 Declaration WITH Clause......Page 72 Rules for SQLJ Executable Statements......Page 75 SQLJ Clauses......Page 76 Specifying Connection Context Instances and Execution Context Instances......Page 77 Executable Statement Examples......Page 78 PL/SQL Blocks in Executable Statements......Page 80 Overview of Host Expressions......Page 81 Basic Host Expression Syntax......Page 82 Examples of Host Expressions......Page 84 Overview of Result Expressions and Context Expressions......Page 86 Evaluation of Java Expressions at Runtime......Page 87 Examples of Evaluation of Java Expressions at Runtime......Page 89 Restrictions on Host Expressions......Page 98 SELECT INTO Syntax......Page 99 Examples with Host Expressions in SELECT-List......Page 100 SELECT INTO Error Conditions......Page 101 Iterator Concepts......Page 102 Named Iterators Versus Positional Iterators Versus Result Set Iterators......Page 106 Using Named Iterators......Page 108 Using Positional Iterators......Page 113 Using Iterators and Result Sets as Host Variables......Page 117 Using Iterators and Result Sets as Iterator Columns......Page 120 Assignment Statements (SET)......Page 123 Calling Stored Procedures......Page 125 Calling Stored Functions......Page 126 Using Iterators and Result Sets as Stored Function Returns......Page 128 4 Key Programming Considerations......Page 131 Overview of the Oracle JDBC Drivers......Page 132 Driver Selection for Translation......Page 134 Driver Selection and Registration for Runtime......Page 135 Single Connection or Multiple Connections Using DefaultContext......Page 136 Closing Connections......Page 140 More About the Oracle Class......Page 142 More About the DefaultContext Class......Page 144 Connection for Translation......Page 147 Connection for Customization......Page 148 Wrapper Classes for Null-Handling......Page 149 Examples of Null-Handling......Page 150 SQLJ and JDBC Exception-Handling Requirements......Page 152 Processing Exceptions......Page 153 Using SQLException Subclasses......Page 155 Automatic Commits versus Manual Commits......Page 156 Specifying Auto-Commit as You Define a Connection......Page 157 Using Manual COMMIT and ROLLBACK......Page 158 Effect of Commits and Rollbacks on Iterators and Result Sets......Page 159 Import Required Classes......Page 160 Set Up Exception Handling......Page 161 Set Up Host Variables, Execute SQLJ Clause, Process Results......Page 162 Example of Single-Row Query using SELECT INTO......Page 163 Set Up a Named Iterator......Page 164 Example of Multiple-Row Query Using Named Iterator......Page 165 Naming Requirements and Restrictions......Page 168 Statement Caching Methods......Page 171 5 Type Support......Page 173 Summary of Supported Types......Page 174 Supported Types and Requirements for JDBC 2.0......Page 179 Unsupported Types......Page 180 Wrapping PL/SQL BOOLEAN, RECORD, and TABLE Types......Page 181 Backwards Compatibility for Previous Oracle JDBC Releases......Page 182 General Use of SQLJ Streams......Page 184 Using SQLJ Streams to Send Data......Page 185 Retrieving Data into Streams—Precautions......Page 188 Using SQLJ Streams to Retrieve Data......Page 189 Processing SQLJ Streams......Page 191 Examples of Retrieving and Processing Stream Data......Page 192 SQLJ Stream Objects as Output Parameters and Function Return Values......Page 194 Stream Class Methods......Page 196 Support for JDBC 2.0 LOB Types and Oracle Type Extensions......Page 197 Support for BLOB, CLOB, and BFILE......Page 198 Support for Oracle ROWID......Page 205 Support for Oracle REF CURSOR Types......Page 208 Extended Support for BigDecimal......Page 210 6 Objects and Collections......Page 211 Introduction to Objects and Collections......Page 212 Oracle Collection Fundamentals......Page 214 Object and Collection Datatypes......Page 215 Custom Java Class Interface Specifications......Page 216 Custom Java Class Support for Object Methods......Page 220 Custom Java Class Requirements......Page 221 Compiling Custom Java Classes......Page 227 Additional Uses for ORAData Implementations......Page 228 Creating Object Types......Page 230 Creating Collection Types......Page 232 What JPublisher Produces......Page 235 Generating Custom Java Classes......Page 239 JPublisher INPUT Files and Properties Files......Page 249 Creating Custom Java Classes and Specifying Member Names......Page 251 JPublisher Implementation of Wrapper Methods......Page 252 JPublisher Custom Java Class Examples......Page 253 Extending Classes Generated by JPublisher......Page 257 Strongly Typed Objects and References in SQLJ Executable Statements......Page 263 Selecting Objects and Object References into Iterator Columns......Page 264 Updating an Object......Page 265 Updating an Object Reference......Page 267 Strongly Typed Collections in SQLJ Executable Statements......Page 269 Inserting a Row that Includes a Nested Table......Page 270 Selecting a Nested Table into a Host Expression......Page 271 Manipulating a Nested Table Using TABLE Syntax......Page 273 Selecting Data from a Nested Table Using a Nested Iterator......Page 274 Selecting a VARRAY into a Host Expression......Page 276 Inserting a Row that Includes a VARRAY......Page 277 Serializing Java Classes to RAW and BLOB Columns......Page 278 SerializableDatum—An ORAData Implementation......Page 281 SerializableDatum in SQLJ Applications......Page 284 SerializableDatum (Complete Class)......Page 285 Support for Weakly Typed Objects, References, and Collections......Page 287 Restrictions on Weakly Typed Objects, References, and Collections......Page 288 7 Advanced Language Features......Page 289 Connection Context Concepts......Page 290 Connection Context Logistics......Page 292 More About Declaring and Using a Connection Context Class......Page 293 Example of Multiple Connection Contexts......Page 295 Implementation and Functionality of Connection Context Classes......Page 297 Use of the IMPLEMENTS Clause in Connection Context Declarations......Page 299 Semantics-Checking of Your Connection Context Usage......Page 300 Data Source Support......Page 301 Relation of Execution Contexts to Connection Contexts......Page 304 Creating and Specifying Execution Context Instances......Page 305 Execution Context Synchronization......Page 306 ExecutionContext Methods......Page 307 Relation of Execution Contexts to Multithreading......Page 311 Multithreading in SQLJ......Page 313 Implementation and Functionality of Iterator Classes......Page 315 Use of the IMPLEMENTS Clause in Iterator Declarations......Page 316 Support for Subclassing of Iterator Classes......Page 317 Scrollable Iterators......Page 318 SET TRANSACTION Syntax......Page 326 Isolation Level Settings......Page 327 Using JDBC Connection Class Methods......Page 328 SQLJ Connection Context and JDBC Connection Interoperability......Page 330 SQLJ Iterator and JDBC Result Set Interoperability......Page 335 Meta Bind Expressions......Page 340 SQLJ Dynamic SQL Examples......Page 343 8 Translator Command Line and Options......Page 347 Translator Command Line and Properties Files......Page 348 SQLJ Options, Flags, and Prefixes......Page 349 Command-Line Syntax and Operations......Page 356 Properties Files for Option Settings......Page 360 Order of Precedence of Option Settings......Page 364 Basic Options for Command Line Only......Page 366 Options for Output Files and Directories......Page 373 Connection Options......Page 377 Reporting and Line-Mapping Options......Page 388 Options for Code Generation, Column Optimizations, and Parameter Optimizations......Page 395 Prefixes that Pass Option Settings to Other Executables......Page 402 Flags for Special Processing......Page 407 Semantics-Checking Options......Page 412 Java and Compiler Options......Page 418 Customization Options......Page 426 9 Translator and Runtime Functionality......Page 429 Semantics-Checking......Page 430 Code Generation......Page 433 Java Compilation......Page 437 Profile Customization......Page 439 Translator Error, Warning, and Information Messages......Page 441 Translator Exit Codes......Page 444 SQLJ Runtime......Page 445 Runtime Packages......Page 446 Categories of Runtime Errors......Page 447 Character Encoding and Language Support......Page 449 SQLJ and Java Settings for Character Encoding and Language Support......Page 452 Oracle SQLJ Extended Globalization Support......Page 455 Manipulation Outside of SQLJ for Globalization Support......Page 459 10 Profiles and Customization......Page 461 Creation of a Profile During Code Generation......Page 462 Sample Profile Entry......Page 463 Overview of the Customizer Harness and Customizers......Page 465 Steps in the Customization Process......Page 466 Creation and Registration of a Profile Customization......Page 467 Customization Error and Status Messages......Page 469 Functionality of a Customized Profile at Runtime......Page 470 Advantages and Disadvantages of Oracle-Specific Code Generation......Page 471 Environment Requirements for Oracle-Specific Code Generation......Page 472 Coding Considerations and Limitations with Oracle-Specific Code Generation......Page 473 Translator/Customizer Usage Changes with Oracle-Specific Code Generation......Page 475 Server-Side Considerations with Oracle-Specific Code Generation......Page 476 Overview of Customizer Harness Options......Page 477 General Customizer Harness Options......Page 479 Customizer Harness Options for Connections......Page 483 Customizer Harness Options that Invoke Specialized Customizers......Page 485 Overview of Customizer-Specific Options......Page 488 Oracle Customizer Options......Page 489 SQLJ Options for Profile Customization......Page 500 JAR File Requirements......Page 501 JAR File Results......Page 502 Invoking SQLCheckerCustomizer with the Customizer Harness verify Option......Page 503 SQLCheckerCustomizer Options......Page 504 11 SQLJ in the Server......Page 507 Introduction to Server-Side SQLJ......Page 508 Database Connections within the Server......Page 509 Coding Issues within the Server......Page 510 Default Output Device in the Server......Page 511 Name Resolution in the Server......Page 512 SQL Names Versus Java Names......Page 513 Loading Classes and Resources into the Server......Page 514 Loaded Class and Resource Schema Objects......Page 516 Summary: Running a Client Application in the Server......Page 519 Loading SQLJ Source and Translating in the Server......Page 521 Loading SQLJ Source Code into the Server......Page 522 Option Support in the Server Embedded Translator......Page 524 Loaded Source and Generated Class and Resource Schema Objects......Page 527 Error Output from the Server Embedded Translator......Page 530 Publishing the Application After Loading Source Files......Page 531 Dropping Java Schema Objects......Page 532 Recursive SQLJ Calls in the Server......Page 533 Verifying that Code is Running in the Server......Page 535 Enterprise JavaBeans......Page 536 CORBA Server Objects......Page 537 12 Sample Applications......Page 539 Demo Directories......Page 540 SQLJ Translator Properties File......Page 541 Named Iterator—NamedIterDemo.sqlj......Page 544 Positional Iterator—PosIterDemo.sqlj......Page 548 Host Expressions—ExprDemo.sqlj......Page 552 Definition of Object and Collection Types......Page 559 Oracle Objects—ObjectDemo.sqlj......Page 566 Oracle Nested Tables—NestedDemo1.sqlj and NestedDemo2.sqlj......Page 575 Oracle VARRAYs—VarrayDemo1.sqlj and VarrayDemo2.sqlj......Page 583 General Use of ORAData—BetterDate.java......Page 587 REF CURSOR—RefCursDemo.sqlj......Page 592 Multithreading—MultiThreadDemo.sqlj......Page 595 Interoperability with JDBC—JDBCInteropDemo.sqlj......Page 597 Multiple Connection Contexts—MultiSchemaDemo.sqlj......Page 599 Data Manipulation and Multiple Connection Contexts—QueryDemo.sqlj......Page 600 Subclassing Iterators—SubclassIterDemo.sqlj......Page 603 Dynamic SQL—DynamicDemo.sqlj......Page 606 Prefetch Demo—PrefetchDemo.sqlj......Page 616 Update Batching—BatchDemo.sqlj......Page 621 Generic Applet HTML Page—Applet.html......Page 625 Generic Applet SQLJ Source—AppletMain.sqlj......Page 626 SQLJ in the Server—ServerDemo.sqlj......Page 632 JDBC Version of the Sample Code......Page 633 SQLJ Version of the Sample Code......Page 637 A Performance and Debugging......Page 639 Performance Enhancement Features......Page 640 Row Prefetching......Page 641 Statement Caching......Page 642 Update Batching......Page 649 Column Definitions......Page 660 Parameter Size Definitions......Page 661 Overview of Auditors and Code Layers......Page 664 Invoking AuditorInstaller with the Customizer Harness debug Option......Page 665 AuditorInstaller Runtime Output......Page 666 AuditorInstaller Options......Page 667 Full Command-Line Examples......Page 671 SQLJ -linemap Flag......Page 672 Developing and Debugging in JDeveloper......Page 673 B SQLJ Error Messages......Page 675 Translation Time Messages......Page 676 Runtime Messages......Page 721 Index......Page 731