Oracle Database Programming using Java and Web Services

Oracle Database Programming using Java and Web Services

by Kuassi Mensah
Released April 2011
Publisher(s): Digital Press
ISBN: 9780080525112

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The traditional division of labor between the database (which only stores and manages SQL and XML data for fast, easy data search and retrieval) and the application server (which runs application or business logic, and presentation logic) is obsolete. Although the books primary focus is on programming the Oracle Database, the concepts and techniques provided apply to most RDBMS that support Java including Oracle, DB2, Sybase, MySQL, and PostgreSQL. This is the first book to cover new Java, JDBC, SQLJ, JPublisher and Web Services features in Oracle Database 10g Release 2 (the coverage starts with Oracle 9i Release 2). This book is a must-read for database developers audience (DBAs, database applications developers, data architects), Java developers (JDBC, SQLJ, J2EE, and OR Mapping frameworks), and to the emerging Web Services assemblers.

* Describes pragmatic solutions, advanced database applications, as well as provision of a wealth of code samples
* Addresses programming models which run within the database as well as programming models which run in middle-tier or client-tier against the database.
* Discusses languages for stored procedures: when to use proprietary languages such as PL/SQL and when to use standard languages such as Java; also running non-Java scripting languages in the database
* Describes the Java runtime in the Oracle database 10g (i.e., OracleJVM), its architecture, memory management, security management, threading, Java execution, the Native Compiler (i.e., NCOMP), how to make Java known to SQL and PL/SQL, data types mapping, how to call-out to external Web components, EJB components, ERP frameworks, and external databases.
* Describes JDBC programming and the new Oracle JDBC 10g features, its advanced connection services (pooling, failover, load-balancing, and the fast database event notification mechanism) for clustered databases (RAC) in Grid environments.
* Describes SQLJ programming and the latest Oracle SQLJ 10g features , contrasting it with JDBC
* Describes the latest Database Web services features, Web services concepts and Services Oriented Architecture (SOA) for DBA, the database as Web services provider and the database as Web services consumer.
* Abridged coverage of JPublisher 10g, a versatile complement to JDBC, SQLJ and Database Web Services.

Table of contents

  1. Copyright
    1. Dedication
  2. Oracle Database Related Book Titles:
  3. Foreword
  4. Preface
  5. Introduction
    1. Server-Side Database Programming (Stored Procedures)
      1. Database Programming Using Java in the Database
    2. Database Programming Using JDBC, SQLJ, and JPublisher
      1. The Good Old JDBC
      2. JDBC and Grid Computing
      3. SQLJ: Bundling SQL and Java Together
      4. JPublisher: The Java Developer’s Swiss Army Knife for Accessing the Oracle Database
    3. Database Programming with Web Services
    4. Putting Everything Together: The “All-You-Can-Eat Buffet”
  6. Acknowledgments
  7. I. Java in the Database
    1. 1. Stored Procedures as Database Programming Model
      1. 1.1. Rationale for Stored Procedures
        1. 1.1.1. Simplifying Database Programming
        2. 1.1.2. Centrally Managed Data Logic
          1. Avoids Code Duplication
          2. Fosters Data Logic Sharing
          3. Implementation Transparency
        3. 1.1.3. Performance: Run JDBC Applications Faster in the Database
          1. Setup
          2. The Java Aplication
          3. Running the Java Application as a Stand-alone JDBC Application
          4. Running the Java Application as a Java Stored Procedure
        4. 1.1.4. Encapsulation
        5. 1.1.5. Security: Advanced Data Access Control
        6. 1.1.6. Resource Optimization
        7. 1.1.7. Low-Cost Deployment
        8. 1.1.8. Fully Utilize Database Capabilities
      2. 1.2. Obstacles to the Adoption of Stored Procedures
        1. 1.2.1. Lack of Portability across RDBMS Vendors
        2. 1.2.2. Scalability
        3. 1.2.3. Maintenance and Resilience to Schema Change
        4. 1.2.4. Hard to Debug
        5. 1.2.5. Weak Support for Complex Types
      3. 1.3. Languages for Stored Procedures
        1. 1.3.1. Proprietary Languages
          1. Seamless Integration with SQL
          2. IDE Support
          3. Portability
        2. 1.3.2. Java for Stored Procedures
          1. Complete Programming Language
          2. Secure Language
          3. Type Safety
          4. Robustness
          5. Productivity: Rapid Design Features
          6. Using Java as a Procedural Language
          7. Standard Specifications for Java Stored Procedures
          8. POJO-like Programming Model
          9. Stored Procedures and O/R Mapping
          10. Cross-Database Portability
          11. Huge Class Library and Tools: Reduced Development Time and Costs
          12. Skills Reuse
          13. Java Database Connectivity and SQL Data Access
          14. Starting with Java
        3. 1.3.3. .NET Languages
      4. 1.4. PL/SQL or Java
        1. 1.4.1. PL/SQL and Java!
    2. 2. OracleJVM: Under the Hood
      1. 2.1. Design Goals and Architecture
        1. 2.1.1. Tight Integration with the RDBMS
        2. 2.1.2. J2SE Compatibility
        3. 2.1.3. How Is Java Stored in the Database?
          1. Where Is the Java Source Stored?
          2. What Are Java Resources and How Are These Used?
          3. Where Is the Java Bytecode Stored?
          4. How Are Java Archives Handled?
        4. 2.1.4. Class Sharing
          1. Sharing JVM Runtime Classes and Metadata
          2. Sharing User Application Classes
          3. Sharing “Interned Strings”
          4. Sharing “Constant States” (Class Hot Loading)
          5. Sharing Read-Only System Objects
        5. 2.1.5. Interapplication Isolation (JSR 121)
        6. 2.1.6. Contrasting OracleJVM with the JDK VM
          1. Session-Based Architecture and Scalability
          2. Robustness
          3. Threading
          4. Java Output
          5. Invocation Mechanisms, Locating Classes, and Transitive Closure
          6. Preferences
          7. Consistent Behavior across Platforms
          8. Java 2 Security
        7. 2.1.7. Resource Control
        8. 2.1.8. SQL Data Access from Java in the Database
        9. 2.1.9. DBMS_JAVA: The All-Purpose Tool for Administering OracleJVM
          1. Longnames and Shortnames
      2. 2.2. Java Memory Management
        1. 2.2.1. Key Memory Structures of the Oracle Database
        2. 2.2.2. Java Memory Allocation Techniques
          1. Frontier consing
          2. Buddy allocation
        3. 2.2.3. Garbage Collection Techniques
          1. Generational GC
          2. Mark-sweep GC
          3. Copy GC
          4. Reference-counting GC
          5. Self-tuning GC
        4. 2.2.4. Java Memory Areas
          1. Sessionspace
          2. Oldspace
          3. Runspace
          4. Stackspace
          5. Newspace
          6. Swapping and Pinning Libunits
        5. 2.2.5. Shared Servers versus Dedicated Processes
          1. Session in Dedicated Process
          2. Session in Shared Server Process
        6. 2.2.6. The Javapool
        7. 2.2.7. Top-Level Calls and Recursive Calls
        8. 2.2.8. State Preservation across Calls and End-of-Call Migration
        9. 2.2.9. End-of-Call, VM Termination, and Session Termination
      3. 2.3. Security in OracleJVM
        1. 2.3.1. User Authentication
        2. 2.3.2. Database-Schema Security
        3. 2.3.3. Resolver Specification and Class-Resolution Security
        4. 2.3.4. Login-User and Effective-User Security
        5. 2.3.5. Java 2 Security
          1. Permissions
          2. The Java Security Manager
        6. 2.3.6. Java 2 Security in OracleJVM
          1. Security Policy
          2. Querying the PolicyTable
          3. The Security Manager
          4. Determining and Granting Permissions
          5. Restricting Permission
          6. Revoking Permission
          7. Disabling Permissions
          8. Deleting Permissions
          9. System Classes Security
        7. 2.3.7. OracleJVM Security Best Practices
        8. 2.3.8. JNI Calls
      4. 2.4. Java VM Life Cycle
        1. 2.4.1. OracleJVM Install, Uninstall, and Reinstall
        2. 2.4.2. Java VM Initialization and Termination
          1. Java VM or Java Application Initialization
          2. Java VM Termination
      5. 2.5. Java Execution in the Database
        1. 2.5.1. The OracleJVM Interpreter
          1. Threaded Interpreter
          2. Performance Optimization
      6. 2.6. The Native Java Compiler (NCOMP)
        1. 2.6.1. What Is NCOMP?
        2. 2.6.2. Requirements and Design Choices
          1. Portability
          2. Scalability
          3. Security/Integrity
          4. Java Bytecode to C Translation
          5. Interoperability between Interpreted Bytecode and Native Code
        3. 2.6.3. The NCOMP Process
          1. Compilation and Linkage
        4. 2.6.4. The NCOMP Command
          1. Forced NCOMPing
          2. NCOMPing nonloaded java classes
        5. 2.6.5. The STATUSNC Command
          1. Class-level Check
          2. Method-level Check
        6. 2.6.6. Dumping Java Classes with NCOMP
        7. 2.6.7. NCOMP Configuration and Planning
          1. Configuring C Compiler Optimizations
          2. Platform Dependencies
          3. NCOMP: Time Planning
          4. NCOMP: Disk Space Planning
        8. 2.6.8. NCOMP Performance Tips, Improper Use, and Troubleshooting
          1. Performance-Tuning Tips for Java in the Database
      7. 2.7. Conclusion
    3. 3. Developing and Running Java in the Database
      1. 3.1. Developing Java in the Database
        1. 3.1.1. Turning JDBC Applications into Java Stored Procedures
          1. JDBC Connections
          2. Turning Instance Methods into Public Static Methods
        2. 3.1.2. Creating or Loading Java in the Database
          1. Method 1: Create Java Sources in the Database, Interactively
          2. Method 2: Uploading Client-Side Java Files into the Database
          3. The loadjava Utility
          4. Method 3: Uploading Server-Side Java Files into the Database
          5. Method 4: Using JDeveloper and Third-party IDE
          6. Method 5: Creating Java Objects from an Existing CLOB/BLOB/BFILE
          7. Method 6: Creating Java Objects from XML (XDB folder)
          8. Dumping the Java Class Bytecode
          9. Checking the Status of Java Classes in Your Schema
        3. 3.1.3. Removing Java Sources, Classes, and Resources from the Database
        4. 3.1.4. Setting/Querying Environment Variable and System Properties
          1. Setting Options for the Java Compiler in the Database
          2. Turning the Bytecode Verifier ON/OFF
        5. 3.1.5. The Java Compiler within the Database
          1. Invoking the OracleJVM Compiler
          2. Automatic Compilation and Java Classes Dependencies Management
      2. 3.2. Turning Java in the Database into Stored Procedures
        1. 3.2.1. Call Spec Types
          1. Top-Level Call Spec
          2. PL/SQL Packaged Call Specs
          3. Object Type Call Spec
      3. 3.3. Mapping SQL and PL/SQL Types to/from Java Types
        1. 3.3.1. Mapping Matrix
        2. 3.3.2. Code Segments for Mapping
          1. Setup
          2. Mapping java.sql.Date to/from SQL DATE
          3. Mapping oracle.sql.DATE to/from SQL DATE
          4. Mapping java.lang.Integer to/from SQL NUMBER
          5. Mapping oracle.sql.NUMBER to SQL NUMBER
          6. Mapping oracle.sql.CHAR to/from SQL CHAR
          7. Mapping oracle.sql.RAW to/from SQL RAW
          8. Setup for CLOB, BLOB, and BFILE Mapping
          9. Steps
          10. Mapping CLOB Locator to/from java.sql.Clob
          11. Mapping java.sql.Blob to/from SQL BLOB
          12. Mapping oracle.sql.BFILE to/from SQL BFILE
          13. Mapping User-Defined Object Types to oracle.sql.STRUCT
          14. Mapping REF of ADT Table Types
          15. Mapping REF Cursors to java.sql.ResultSet
          16. Mapping VARRAY of Scalar SQL Types
          17. Mapping Nested Tables of Scalar SQL Types
          18. Mapping SQLJ Object Types
      4. 3.4. Invoking Java in the Database
        1. 3.4.1. Setup
          1. Setting Up the Database Table
          2. Reusing a JDBC Code in the Database
        2. 3.4.2. Invoking Java in the Database Using OJVMJAVA
          1. Description
        3. 3.4.3. Invoking Java in the Database through the PL/SQL Wrapper
          1. Step 1: Publishing the PL/SQL wrapper (from a SQL*Plus session)
          2. Step 2: invoking the Java method through the PL/SQL wrapper (from a SQl*Plus session)
        4. 3.4.4. Invoking Java in the Database through Client-side Stub
          1. What Is the Problem with Call Specs?
          2. The Solution
          3. How Does Java Invocation through the Client Stub Work?
          4. Step 1: Generate the client stub
          5. Step 2: Define the client application
          6. Step 3: Compile the client application
          7. Step 4: Invoke Java in the database through the client stub
        5. 3.4.5. Errors and Exceptions Handling
      5. 3.5. Managing Java in the Database
        1. 3.5.1. Java Audit
        2. 3.5.2. Oracle Enterprise Manager (Database Control) Support for Java in the Database
    4. 4. Pragmatic Applications Using Java in the Database
      1. 4.1. CNXO: Secure Credit Card Processing with Oracle and JSSE
        1. Architecture Overview and Design
        2. Implementation Challenges
        3. JSSE-enabling Oracle 9i Release 2
        4. URLReader as Java Stored Procedure
          1. Step 1: URLReader.java
          2. Step 2: Create a PL/SQL Wrapper
        5. CNXO Conclusion
      2. 4.2. Using J2EE and Java in the Database Together
        1. 4.2.1. Auto-generating Primary Keys for BMP Entity Beans
          1. Mapping CMP Beans Finders Using Java Stored Procedures
        2. 4.2.2. Calling-out EJB from OracleJVM
          1. Rationales for EJB Call-Out
          2. Calling out a Stateless Bean in OC4J from Java DB using RMI/IIOP
            1. Step 1: Download, Install, and Start up OC4J
            2. Step 2: Deploying the Demo EJB to OC4J
            3. Step 3: Turning EJB Client into a Java Stored Procedure
            4. Step 4: Call out the EJB from within the Database
        3. 4.2.3. HTTP Call-Out: The Poor Man’s Cache Invalidation
        4. 4.2.4. JMS over Streams/AQ in the Database
          1. What Is Oracle Database Streams/AQ?
          2. JMS in OracleJVM
      3. 4.3. JDBC Call-Out to Non-Oracle Databases
        1. 4.3.1. Description and Rationales
        2. 4.3.2. How Does It Work?
          1. Step 1: Downloading and Uploading the Corresponding JDBC Drivers
          2. Step 2: Creating a Generic Java Program
          3. Step 3: Creating a Generic JDBC Call-out Application
      4. 4.4. SAP Java Connector: Accessing the SAP System from the Oracle Database
      5. 4.5. Excel-like Expression Parser in the Database
        1. 4.5.1. Rationales for Custom Parsers in the Database
        2. 4.5.2. What Is the Mini-Parser?
        3. 4.5.3. Implementing the Mini-Parser
          1. Step 1: Describing the Mini-Parser
          2. The Functional Structure of the Mini-Parser
          3. Code Outline
          4. Step 2: Generating the Mini-Parser
          5. Generating a Lexical Analyzer
          6. Generating the Parser
          7. Step 3: Deploying the Mini-Parser in the Database
          8. Step 4: Making the Mini-Parser Invocable through SQL and PL/SQL
          9. Step 5: Use It
    5. 5. Database Scripting Using Non-Java Languages
      1. 5.1. Why Contemplate Non-Java Languages for the Database?
        1. 5.1.1. Common Language Runtime in RDBMS
        2. 5.1.2. Scripting Languages Support in RDBMS
          1. Perl and PHP in MySQL
          2. Ruby Perl, Pythin, and TCL Plug-ins in PostgreSQL
      2. 5.2. Database Scripting with OracleJVM—Just for Fun!
        1. 5.2.1. Proof of Concept #1: Running TCL (JACL) Scripts in the Database
          1. JACL Enable Your Database
          2. Run TCL Scripts in Your Database
          3. Conclusions
        2. 5.2.2. Proof of Concept #2: Running Jython (Python) in the Database
          1. What Is Jython?
          2. Jython Enabling Your Database
          3. Running Jython Programs in the Database
          4. Conclusion
        3. 5.2.3. Proof of Concept #3: Running Kawa (Scheme) in the Database
          1. Scheme Enable Your Database (Scheme on you!)
          2. Running Scheme Programs in the Database
          3. Conclusions
        4. 5.2.4. Proof of Concept #4: Running Groovy in the Database
          1. What Is Groovy?
          2. Groovy Enabling Your Database
          3. Loading the Groovy Runtime in the Database
          4. Running Groovy Scripts in the Database
          5. Running Groovy Scripts as a Dynamically Loaded Class
          6. Storing Groovy Scripts in Database Columns
          7. Integrating Groovy with SQL
          8. Conclusion
  8. II. Java Persistence and Java SQL Data Access
    1. 6. Introducing the JDBC Technology and Oracle’s Implementation
      1. 6.1. JDBC Primer
        1. 6.1.1. First Steps in JDBC
          1. Step 1: Loading and Registering the Driver
          2. Step 2: Connecting to the Database
            1. Method 1: Using DriverManager Factory
            2. Method 2: Using OracleDataSource Factory
          3. Step 3: Creating a Statement
          4. Step 4: Executing a SQL Statement
          5. Step 5: Retrieving and Processing Result Sets
          6. Step 6: Closing Results Sets, Statements, and Connections
          7. A Basic but Complete JDBC Program
        2. 6.1.2. JDBC within J2SE and J2EE Environments
      2. 6.2. Overview of JDBC Specifications
        1. 6.2.1. Overview of JDBC 1.22 Specification (Where Things Started!)
          1. Key Features
        2. 6.2.2. Overview of JDBC 2.0 Specification (A Major Spec!)
          1. Features Introduced in the JDBC 2.1 Core API
          2. Features Introduced in the javax.sql JDBC 2.0 Optional Package
        3. 6.2.3. Overview of JDBC 3.0 Specification
        4. 6.2.4. Overview of Upcoming JDBC 4.0 Specification
        5. 6.2.5. JDBC Standards Support in the Oracle JDBC Drivers
          1. Oracle 8i Release 8.1.5 JDBC Drivers
          2. Oracle 8i Release 8.1.6 JDBC Drivers
          3. Oracle 8i Release 8.1.7 JDBC Drivers
          4. Oracle 9i Release 1 (9.0.1) JDBC Drivers
          5. Oracle 9i Release 2 ( 9.2.0.x) JDBC Drivers
          6. Oracle Database 10g Release 1 (10.1.0.x) JDBC Drivers
          7. Oracle Database 10g Release 2 (10.2.0.x) JDBC Drivers
      3. 6.3. Architecture and Packaging of Oracle JDBC Drivers
        1. 6.3.1. Rearchitected Oracle JDBC Drivers
          1. Faster Features Implementation and Reduction of Functional Gap
          2. The Main Packages of Oracle JDBC
          3. Performance Optimizations
        2. 6.3.2. Packaging of Oracle JDBC Drivers
          1. Type-4 Client-side JDBC (JDBC-Thin)
          2. Type-4 Server-side JDBC
          3. Type-2 Client-side JDBC (JDBC-OCI)
          4. Type-2 Server-side JDBC
          5. Additional JARS
          6. Instant Client (“Homeless” Oracle Client)
        3. 6.3.3. Features Differences Between Driver Types
        4. 6.3.4. JDBC Drivers and Database Interoperability
          1. How to Check the Version of the JDBC Drivers
    2. 7. URL, DataSource, Connection, and Statements
      1. 7.1. JDBC URL
      2. 7.2. DataSources
        1. 7.2.1. The OracleDataSource
        2. 7.2.2. DataSources and JNDI
          1. JNDI 101
          2. Using Oracle DataSources with Stand-alone JNDI
        3. Step 1
          1. Step 2
          2. Step 3
          3. Step 4
          4. Step 5
          5. Step 6
      3. 7.3. Connections and Connection Services
        1. 7.3.1. JDBC Connections and Oracle Extensions
        2. 7.3.2. Connection Caching: Implicit Connection Cache
          1. Implicit Access to the Cache
          2. Saving the Values of Connection Attributes
          3. Caching Heterogeneously Authenticated Connections
          4. Connection Retrieval Based on User-Defined Attributes
          5. Applying Connection Attributes to a Cached Connection
          6. Setting Unmatched Attribute Values
          7. Connection Retrieval Based on Attributes and Weights
          8. List of System and Connection Properties
          9. Implicit Connection Cache Code Sample
        3. 7.3.3. The Connection Cache Manager
          1. Managing and Maintaining Caches
          2. The Connection Cache Manager API
          3. Connection Cache Manager Callback
          4. releaseConnection
        4. 7.3.4. RAC Events and Fast Application Notification
          1. Step 4: Configure ONS on Each RAC Server Node
          2. Step 5: Configuring/Subscribing ONS Clients
          3. Testing ONS Operations
          4. Step 1
          5. Step 2
          6. Output
        5. 7.3.5. High Availability: Fast Connection Failover
          1. How Fast Connection Fail-over Works
          2. Configuring Fast Connection Fail-over
          3. Step 2: Enabling Fast Connection Fail-over
          4. Step 3: Catching and Retrying Connection Request
        6. 7.3.6. Scalability: Connection Load Balancing
          1. Enabling Runtime Connection Load Balancing
          2. Step 1
          3. Step 2
          4. Step 3
        7. 7.3.7. JDBC Support for Transparent Application Fail-over
          1. What Usually Works with TAF
          2. What Does Not Work with TAF
          3. TAF Configuration
          4. Customizing TAF Using OCI Callback and Fail-over Events
          5. OCI Fail-over Interface
          6. Graceful Shutdown
          7. FCF versus TAF
        8. 7.3.8. Proxy Authentication
          1. How Proxy Authentication Works
          2. JDBC Support for Proxy Authentication
          3. Caching Proxy Sessions
        9. 7.3.9. Connection Wrapping
        10. 7.3.10. JDBC Connections in Grid Environment
          1. Virtualizing the Database as a Service
          2. Fast Event Notification, Connection Fail-over, and Load Balancing
      4. 7.4. JDBC Statements and Oracle Extensions
        1. 7.4.1. JDBC Statement Types
        2. 7.4.2. Statement
          1. In a Nutshell
          2. Standard Statement API and Oracle Extensions
        3. 7.4.3. PreparedStatement
          1. In a Nutshell
          2. Steps 1 and 2
          3. Step 3
          4. Step 4
          5. The Standard PreparedStatement API and OraclePreparedStatement
          6. Performance
        4. 7.4.4. CallableStatement (Calling Stored Procedures)
          1. In a Nutshell
          2. Step 1
          3. Step 2
          4. Step 3
          5. Step 4
          6. Step 5
          7. The Standard API and OracleCallableStatement
          8. Performance
        5. 7.4.5. Retrieval of Auto-Generated Keys and DML with Returning
          1. JDBC 3.0 Auto-Generated Keys
          2. java.sql.DatabaseMetaData:
          3. java.sql.Statement:
          4. DML with RETURNING
        6. 7.4.6. Statement Caching
          1. In a Nutshell
          2. Implicit Statement Caching
          3. A Complete Example: ImplStmtCache.java
          4. Explicit Statement Caching
          5. A Complete Example: ExplStmtCache.java
        7. 7.4.7. DML Batching
          1. Standard Batch Updates
          2. Example 1
          3. Example 2: syntax
          4. Oracle Update Batching
          5. Example:
    3. 8. SQL Data Access and Manipulation
      1. 8.1. Key Metadata in JDBC
        1. 8.1.1. DatabaseMetaData: OracleDatabaseMetaData
        2. 8.1.2. ResultSetMetaData: OracleResultSetMetaData
        3. 8.1.3. ParameterMetaData
        4. 8.1.4. StructMetaData
      2. 8.2. Manipulating Oracle Data Types with JDBC
        1. Example:
        2. 8.2.1. Manipulating SQL Null Data
        3. 8.2.2. Manipulating Character Data Types
          1. CHAR, CHAR(n), CHAR(n BYTE), CHAR(n CHAR)
          2. VARCHAR2 (n), VARCHAR2(n BYTE), VARCHAR2 (n CHAR)
          3. NCHAR, NCHAR(n), NVARCHAR2 (n)
        4. 8.2.3. Oracle JDBC Support for Number Data Types
          1. NUMBER, NUMBER(p), NUMBER(p, s)
          2. BINARY_FLOAT and BINARY_DOUBLE
        5. 8.2.4. JDBC Support for Long and Raw Data Types
          1. SQL RAW(s)
          2. LONG and LONG RAW
        6. 8.2.5. JDBC Support for SQL Datetime Data Types
          1. DATE
          2. TIMESTAMP, TIMESTAMPTZ, TIMESTAMPLTZ
          3. INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND
        7. 8.2.6. JDBC Support for LOB Datatypes
          1. Overview
          2. Temporary LOB
          3. Examples of BLOBs Manipulation
            1. Reading BLOBs
            2. Writing BLOBs
          4. Examples of CLOB Manipulation
            1. Reading CLOB Data
            2. Writing CLOB Data
          5. Examples of BFILE Manipulation
            1. Prerequisites
            2. Reading BFILE Data
            3. Creating BFILE Data
        8. 8.2.7. JDBC Support for ROWID
        9. 8.2.8. JDBC Support for OPAQUE Type
          1. OPAQUE
          2. OPAQUE Descriptor
        10. 8.2.9. JDBC Support for XMLType
          1. XML DB and XML Type
          2. JDBC Support for XMLType
        11. 8.2.10. JDBC Support for SQL Object Types and References Types
          1. Type Map
          2. Weak Type Mapping: Struct and STRUCT
            1. Code Snippet
          3. Strong Type Mapping: SQLData and ORAData
            1. SQLData
            2. ORAData and ORADataFactory
          4. SQLJ Object Types
          5. REF
          6. Manipulating REF of ADT Table Types
          7. REF Cursors
        12. 8.2.11. JDBC Support for User-Defined Collections
          1. ARRAY, VARRAYs, and ArrayDescriptor
            1. VARRAY of NUMBER
            2. VARRAY of VARCHAR2
            3. VARRAY of DATE
          2. Nested Tables
            1. NESTED TABLE of NUMBER
            2. NESTED TABLE of VARCHAR2
            3. NESTED TABLE of DATE
          3. PL/SQL Associative Array
          4. PL/SQL Record and PL/SQL Boolean
        13. 8.2.12. JDBC Support for Spatial Types
        14. 8.2.13. Unsupported Types
      3. 8.3. Result Set Support in Oracle JDBC
        1. 8.3.1. The Result Set API in a Nutshell
          1. The Evolution of the Result Set API
          2. Methods Specified by the java.sql.ResultSet Interface
            1. Fetch Direction
            2. Fetch Size
        2. 8.3.2. The Oracle Result Set Interface
          1. Implementation History
        3. 8.3.3. Oracle JDBC Support for Scrollable Result Sets
          1. Scrollable Result Set Retrictions
            1. Scrolling the Result Sets
          2. The Oracle Result Set Cache Interface
        4. 8.3.4. Oracle JDBC Support for Updatable Result Sets
          1. Updatable Result Set Restrictions
          2. Updating Result Sets
          3. Deleting Result Sets
          4. Inserting Result Sets
        5. 8.3.5. Prefetching and Auto Refresh
          1. Row Prefetching
          2. Auto Refetch
        6. 8.3.6. Changes Detection and Visibility
          1. Visibility of Internal and External Changes
      4. 8.4. RowSet
        1. 8.4.1. Introducing the RowSet API
        2. 8.4.2. JDBCRowSet and OracleJDBCRowSet
        3. 8.4.3. CachedRowSet and OracleCachedRowSet
          1. Creating and Populating CachedRowSets
          2. Manipulating and Making Changes to Disconnected Data
          3. Event Listeners
        4. 8.4.4. WebRowSet and OracleWebRowSet
          1. Understanding the WebRowSet XML Schema
          2. Creating a WebRowSet Object and Dumping Its Contents to an XML Document
          3. Reading an XML Document into a WebRowSet Object
          4. Changing a WebRowSet Object and Synchronizing Back to the Datasource
            1. Inserting a New Row in a WebRowSet Object
            2. Deleting a Row in a WebRowSet Object
            3. Updating a Row in a WebRowSet
        5. 8.4.5. FilteredRowSet and OracleFilteredRowSet
          1. Define and Implement the Predicate Interface
          2. Creating a FilteredRowSet Object and Setting Properties
          3. Populating the FilteredRowSet
          4. Enabling Filters
          5. Retrieving Filtered Rows and Closing the FilteredRowSet Object
        6. 8.4.6. JoinRowSet and OracleJoinRowSet
          1. Creating an Empty Instance of JoinRowSet Object and Setting Properties
          2. Creating and Adding RowSet Objects to the JoinRowSet
          3. Defining the Match Column(s)
          4. Navigating and Consuming the JoinRowSet Objects
          5. Further Considerations
      5. 8.5. Conclusion
    4. 9. JDBC Quality of Services and Best Practices
      1. 9.1. Transaction Services
        1. 9.1.1. Transactions
        2. 9.1.2. AutoCommit
        3. 9.1.3. Transaction Isolation Levels
        4. 9.1.4. Transaction SavePoint Support
        5. 9.1.5. Global/Distributed Transaction
          1. The X/Open Distributed Transaction Model
          2. JDBC Support for Global Transactions
          3. JDBC XADataSource and XAConnection Interfaces
          4. Native XA Support in Oracle JDBC
          5. Two-Phase Commit Optimization
        6. 9.1.6. Connection Sharing between Local and Global Transactions
          1. NO_TXN -> LOCAL_TXN and LOCAL_TXN-> NO_TXN
          2. NO_TXN -> GLOBAL_TXN and GLOBAL_TXN -> NO_TXN
      2. 9.2. Security Services
        1. 9.2.1. Oracle JDBC Support for SSL
          1. SSL in a Nutshell
          2. Java Support for SSL (JSSE)
          3. Configuring and Using the Oracle JDBC Drivers with SSL
          4. Creating and Storing Server Certificates in an Oracle Wallet
          5. Creating a Wallet
          6. Creating a Certificate Request
          7. Obtaining and Importing a Certificate
            1. Saving the Wallet and Certificate
            2. Configure the Oracle Database for SSL
            3. Creating and Storing Client Certificates in a Truststore
            4. Creating the Client Certificate (Optional)
            5. Configuring the JDBC-Thin Driver for SSL
          8. Using JDBC-Thin with SSL
      3. 9.3. Tips and Best Practices
        1. 9.3.1. End-to-End Tracing
        2. 9.3.2. Common Errors
        3. 9.3.3. Optimizing Result Set Retrieval
        4. 9.3.4. Logging Service
          1. Logging Levels
          2. Loggers
          3. Tracing JDBC Using System Properties
      4. 9.4. Conclusion
  9. III. Oracle Database Programming with SQLJ
    1. 10. Introducing the SQLJ Technology and Oracle’s Implementation
      1. 10.1. Overview
        1. 10.1.1. What Is SQLJ?
          1. The SQLJ Specification
          2. The SQLJ Translator
          3. The SQLJ Runtime
          4. SQLJ Profiler
        2. 10.1.2. Why SQLJ?
          1. Benefits Brought by the Standard ISO SQLJ
            1. Simplicity
            2. Translation-Time Type Checking
            3. Interoperability with JDBC
          2. Benefits Brought by Oracle SQLJ Extensions
            1. Support for Dynamic SQL
            2. Compile-Time Performance Optimizations
          3. The Architecture and Packaging of Oracle SQLJ
        3. 10.1.3. The Oracle SQLJ Translator
          1. The SQLJ Parser, the Java Parser, and the SQL Parsers
          2. Basic Options
          3. Environment and Customizer Options
          4. Javac Options
          5. Advanced Options
          6. Semantics Checking and Offline Parsing
          7. Code Generation
            1. The Oracle Customizer
          8. Translator Exit Codes
        4. 10.1.4. The Oracle SQLJ Runtime
          1. Runtime Packaging
          2. Runtime Packages That Support the SQLJ API
            1. Packages That Support Standard SQLJ API
            2. Package(s) That Support Oracle SQLJ Extensions APIs
        5. 10.1.5. Environment Setup
        6. 10.1.6. SQLJ Primer
      2. 10.2. SQLJ in the Database
        1. Loading, Translating, and Publishing SQLJ in the Database
          1. Environment Check
          2. Loading SQLJ Sources, Classes, and Resources in the Database
          3. Setting Compiler Options
          4. Publishing SQLJ to SQL, PL/SQL, and JDBC
          5. Differences in Code and Behavior
    2. 11. The SQLJ Language and Oracle Extensions
      1. 11.1. Declaration Statements
        1. 11.1.1. Import Statements
        2. 11.1.2. Connection Contexts
          1. User-Defined Connection Context Interface
          2. DataSource
          3. Statement Caching
        3. 11.1.3. Execution Contexts
          1. Methods of the ExecutionContext Class
          2. Execution Contexts and Multithreading
        4. 11.1.4. Iterators
          1. Weakly Typed Iterators
          2. Strongly Typed Iterators
          3. Scrollable Iterators
            1. Scrollable Named Iterators
            2. Scrollable Positional Iterators
          4. Subclassing Iterator Classes
          5. Iterator Sensitivity
        5. 11.1.5. IMPLEMENTS Clause in Context Declarations
          1. Iterator Implementing User-Defined Interfaces
          2. Positioned Update/Delete Iterators
        6. 11.1.6. WITH Clause in Context Declarations
          1. Supported SQLJ Constants in WITH Clause
          2. Unsupported SQLJ Constants in WITH Clause
      2. 11.2. Executable Statements
        1. 11.2.1. Statement Clauses
          1. SELECT INTO Clause
          2. FETCH Clause
          3. COMMIT or ROLLBACK Clause
          4. SAVEPOINT Clause
          5. SET TRANSACTION Clause
          6. Procedure Clause
          7. SQL Clause
          8. PL/SQL Clause
        2. 11.2.2. Assignment Clauses
          1. Query Clause
          2. Function Clause
          3. Iterator Conversion Clause
        3. 11.2.3. Dynamic SQL
          1. Meta Bind Expressions
          2. Other Dynamic SQL Mechanisms
      3. 11.3. Expressions in SQLJ
        1. Host Variables
        2. Host Expressions
        3. 11.3.1. Context and Result Expressions
          1. Context Expressions
          2. Result Expressions
        4. 11.3.2. Expressions Evaluation
      4. 11.4. Interoperability: Using SQLJ and JDBC Together
        1. 11.4.1. JDBC to SQLJ Interoperability
          1. Leveraging Oracle JDBC Connection Services
        2. 11.4.2. SQLJ to JDBC Interoperability
      5. 11.5. Conclusion
    3. 12. SQLJ Data Access and Best Practices
      1. 12.1. Manipulating Oracle SQL and PL/SQL Data Types with SQLJ
        1. 12.1.1. Oracle SQLJ Type-Mapping Summary
        2. 12.1.2. Column Definitions
          1. Automatic Registration of Column Types and Sizes
          2. Enabling Column Definition
        3. 12.1.3. Manipulating SQL Null Data with SQLJ
        4. 12.1.4. Manipulating Character Data Types with SQLJ
          1. CHAR, CHAR(n), CHAR(n BYTE), CHAR(n CHAR) VARCHAR2 (n), VARCHAR2(n BYTE), VARCHAR2 (n CHAR)
          2. NCHAR, NCHAR(n), NVARCHAR2 (n)
        5. 12.1.5. Oracle SQLJ Support for Number Data Types
          1. NUMBER, NUMBER(p), NUMBER(p, s)
          2. BINARY_FLOAT and BINARY_DOUBLE
        6. 12.1.6. SQLJ Streams, LONG, and RAW Data Types
          1. SQL RAW(s)
          2. LONG, LONG RAW
          3. SQLJ Streams
          4. Using SQLJ Streams to Send and Retrieve Data to and from the Database
          5. SQLJ Stream Objects as Procedures Output Parameters and Function Return Values
        7. 12.1.7. SQLJ Support for SQL Datetime Data Types
          1. DATE
          2. TIMESTAMP, TIMESTAMPTZ, and TIMESTAMPLTZ
          3. INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND
        8. 12.1.8. SQLJ Support for SQL LOB Data Types
          1. Overview
          2. Using the DBMS_LOB Package
          3. Using Standard JDBC LOB API and oracle.sql LOB Extensions
          4. Using SQLJ Streaming
          5. LOB and BFILE Stored Function Results
          6. LOB and BFILE Host Variables ( SELECT INTO Targets)
          7. LOBs and BFILEs in Iterator Declarations
          8. LOB and BFILE HostVariables as Named Iterator
          9. LOB and BFILE Host Variables as Positional Iterator (FETCH INTO Targets)
        9. 12.1.9. SQLJ Support for Oracle SQL ROWID
        10. 12.1.10. SQLJ Support for OPAQUE Types
          1. XMLType
        11. 12.1.11. SQLJ Support for SQL Object Types and SQL References Types
          1. Type Map
          2. Weak Type Mapping: Struct and STRUCT
          3. Strong Type Mapping: SQLData and ORAData
          4. SQLJ Object Types
          5. SQL REF
          6. REF Cursors
        12. 12.1.12. Serialized Java Objects
          1. Serializing Java Objects Using typeMAP
          2. Serializing Java Objects Using ORAData
          3. Serializing Java Objects by Value
        13. 12.1.13. SQLJ Support for User-Defined SQL Collections
          1. VARRAYs
          2. Nested Tables
        14. 12.1.14. PL/SQL Associative Array
        15. 12.1.15. Unsupported Types
          1. DATALINK
          2. Named Parameters
          3. PL/SQL BOOLEAN and PL/SQL RECORD
      2. 12.2. SQLJ Best Practices
        1. 12.2.1. Row Prefetch
        2. 12.2.2. Statement Caching
        3. 12.2.3. Update Batching
      3. 12.3. Conclusion
  10. IV. Oracle Database Programming with JPublisher
    1. 13. Abridged Oracle JPublisher
      1. 13.1. Why JPublisher?
      2. 13.2. Overview
        1. Features
        2. 13.2.1. Environment Requirements
          1. Installing JPublisher
          2. Environment Checklist
        3. 13.2.2. JPublisher Options
          1. Java Environment
          2. Connection
          3. SQL Data Types Categories and Mapping
          4. Numeric Data Types Mapping
          5. Input Files
          6. Items to Publish
          7. Java Code Generation
          8. PL/SQL Code Generation
          9. Style Files for Mapping Java in the Database
          10. Type-Maps
          11. Input/Output
          12. Backward Compatibility
      3. 13.3. JPublisher In Action
        1. 13.3.1. User-Defined SQL Object Types
        2. 13.3.2. SQL Object Reference Types (REF types)
        3. 13.3.3. REF Cursor Types and Subclassing
        4. 13.3.4. User-Defined SQL Collection Types
        5. 13.3.5. User-Defined OPAQUE Types
        6. 13.3.6. XMLType
        7. 13.3.7. PL/SQL Conversion Functions
        8. 13.3.8. PL/SQL RECORD Types
        9. 13.3.9. PL/SQL Table or Scalar Index-by-Table
        10. 13.3.10. Oracle Streams AQ
        11. 13.3.11. Java in the Database
      4. 13.4. Conclusion
  11. V. Programming the Oracle Database with Web Services
    1. 14. Web Services and SOA for DBA, Data Architects, and Others
      1. 14.1. Web Services 101
        1. 14.1.1. Core Web Services Technologies
          1. XML
          2. WSDL
          3. SOAP
          4. REST
          5. UDDI
          6. Final Thoughts
      2. 14.2. Service-Oriented Architecture (SOA): The Bigger Picture
        1. SOA 101
      3. 14.3. Conclusion
    2. 15. Database as Web Services Provider Service
      1. 15.1. Rationales for Database as Web Services Provider
      2. 15.2. How Does Database as Web Services Provider Work?
        1. 15.2.1. Implementation and Packaging
        2. 15.2.2. How Does Oracle Database as Web Services Provider Work?
        3. 15.2.3. Web Services and SOA Features in Oracle Application Server 10.1.3
          1. Web Services Interoperability
          2. Web Services Security
          3. Web Services Management
          4. Web Services Reliable Messaging
          5. SOAP Message Auditing and Logging
          6. BPEL Integration
          7. Web Services Invocation Framework (WSIF) Support
          8. Other Web Services Features
      3. 15.3. Turning Oracle Database Functionality into Web Services
        1. 15.3.1. Type Conversions and Result Set Representation
          1. Type Conversion between SQL Types and XML Types
          2. SQL Result Sets Representations
        2. 15.3.2. Setting up the Oracle AS OC4J for Database as Web Services Provider
        3. 15.3.3. Assembling PL/SQL Web Services Using JDeveloper Wizard
          1. Setup
          2. Generating the PL/SQL Web Service
          3. Generating the PL/SQL Web Service Proxy
          4. PL/SQL Web Services Limitations with JDeveloper
      4. 15.4. Assembling Database Web Services Using the Command-Line Tool
        1. Introducing the Web Services Assembler
        2. Generic Steps for Publishing Database Functionality Using Web Services Assembler
        3. 15.4.1. Assembling PL/SQL Web Services Using Web Services Assembler
          1. Step 1a
          2. Step 1b
          3. Step 2 (Optional)
          4. Step 3 and 4
          5. Step 5
          6. Step 6 (Optional)
          7. Step 7
          8. Step 8
        4. 15.4.2. Assembling Java in the Database as a Web Service
          1. Prerequisites:
          2. Step 1a
          3. Step 1b
        5. 15.4.3. Assembling SQL Queries or SQL DML Statements as Web Services
          1. Assembling a SQL Query Web Service from a SQL Statement
          2. Step 1a
          3. Step 1b
        6. 15.4.4. Assembling Oracle Streams AQ as Web Services
          1. Assembling Streams AQ Web Services Using Web Services Assembler
          2. Step 1a
          3. Step 1b
          4. Step 5
          5. Accessing an Oracle AQ Queue with JMS
      5. 15.5. Data Type Restrictions
      6. 15.6. Conclusion
    3. 16. Database as Web Services Consumer
      1. 16.1. Rationales for Database as Web Services Consumer
      2. 16.2. How Database as Web Services Consumer Works
        1. 16.2.1. The Software Pieces
        2. 16.2.2. The Required Steps
      3. 16.3. Turning Your Oracle Database into a Web Service Consumer
        1. 16.3.1. Ensure That Java Is Installed in the Database
        2. 16.3.2. Installing JPublisher on the Client Machine
        3. 16.3.3. Installing the Web Services Call-Out Utility in Your Database
      4. 16.4. Database Web Services Call-Out Samples
        1. 16.4.1. Calling Out Google Search Web Service
        2. 16.4.2. Calling Out the Phone Verifier Web Service
      5. 16.5. Conclusion
  12. VI. Putting Everything Together
    1. 17. 360-Degree Programming the Oracle Database
      1. 17.1. TECSIS Systems: Custom Enterprise Integration Framework
        1. 17.1.1. About the Company
        2. 17.1.2. About the Application
        3. 17.1.3. Our Business and Technical Requirements
          1. Business Requirements
          2. Technical Requirements
          3. Design and Programming Choices
        4. 17.1.4. The Architecture of the Integration Framework
          1. Typical Use Cases
          2. Java Stored Procedures Calling External Systems
          3. External Systems Calling Java Stored Procedures
        5. 17.1.5. The Complete Picture
        6. 17.1.6. Conclusion
      2. 17.2. Oracle interMedia
        1. 17.2.1. What Is Oracle interMedia?
        2. 17.2.2. How Does It Work?
          1. interMedia Object Interface: New Database Types
          2. BLOB/BFILE Support
          3. interMedia Object Interface
        3. 17.2.3. Rationales for Storing Media Data in the Database
          1. Synchronization with Related Database Information: Just Another Data Type
          2. Robustness, Reliability, Availability, Security, Scalability
            1. Security
            2. Scalability
          3. Administration, Backup, Recovery/Disaster Recovery
          4. Search, Query, and Transactional Capabilities
          5. Simplicity and Reduced Development Cost
            1. Code Reduction: Built-in Operators
        4. 17.2.4. interMedia Powered by the Oracle Database Extensibility Framework
        5. 17.2.5. interMedia Powered by Java in the Database
          1. The Java Media Parser
          2. The Java Image Processor (based on JAI)
            1. Image Metadata Extraction
            2. Image Metadata Writing
            3. Audio and Video Metadata Extraction
            4. Image Processing and Image Format Conversion
            5. Oracle interMedia Java Classes
            6. Client-side JAI Applications
        6. 17.2.6. Developing Feature-Rich Multimedia Applications Using interMedia
          1. A PL/SQL Example
            1. Creating a Table with an ORDImage Column
            2. Importing Images
            3. Selecting and Viewing Image Properties
            4. Creating Thumbnails and Changing Formats
            5. Exporting Images with ORDImage.export()
            6. A Java Example
            7. Establishing a Connection
            8. Uploading from a File
            9. Setting Properties
            10. Getting and Displaying Properties
            11. Processing an Image
            12. Downloading to a File
      3. 17.3. British Columbia: Online Corporate Registration
        1. 17.3.1. Corporate Online: Background
        2. 17.3.2. How It Works
        3. 17.3.3. Architecture: Requirements and Design
          1. Requirement 1: How to Submit Only Committed Changes
          2. Requirement 2: Processing the Received Message
          3. Requirement 3: Database Web Services
        4. 17.3.4. Messaging across Tiers
        5. 17.3.5. Future Work
        6. 17.3.6. Conclusion
      4. 17.4. Information Retrieval Using Oracle Text
        1. 17.4.1. What Is Oracle Text?
        2. 17.4.2. Why Java in the Database?
        3. 17.4.3. Technical Features
          1. Index Types
          2. Query Operators
          3. Document Services
            1. Highlighting
            2. Markup
            3. Snippet
            4. Theme Extraction
            5. Gist
          4. Advanced Features
            1. Classification
            2. Classification Training
            3. Clustering
            4. Knowledge Base
            5. XML Support
        4. 17.4.4. Benefits of an Integrated Search Capability
        5. 17.4.5. Yapa
          1. Architecture and Implementation
          2. Database schema and indexing
          3. Classifier Outline
          4. Clustering
          5. User Interface
          6. Search Panel
          7. Structure View
          8. List
          9. Categories
          10. Cluster
          11. Content View
          12. Browse
          13. Configuring, Compiling, and Testing the Demo
        6. 17.4.6. Conclusion
      5. 17.5. Database-Driven Content Management System (DBPrism CMS)
        1. 17.5.1. DBPRISM CMS: Key Features and Benefits
        2. 17.5.2. The Architecture of DBPrism CMS
        3. 17.5.3. DBPrism CMS Internals
          1. How DBPrism CMS Works
          2. Complete Execution Flow
          3. Document Structure
          4. Generation of the XML Document
        4. 17.5.4. Extended Capabilities
          1. Searching for Nonpublic Pages
          2. Updated Pages
          3. Broken Links
        5. 17.5.5. Text Searching
        6. 17.5.6. Installing DBPRism CMS
          1. Configuration
          2. Four-Step Installation
        7. 17.5.7. Future Work
      6. 17.6. Conclusion

Product information

  • Title: Oracle Database Programming using Java and Web Services
  • Author(s): Kuassi Mensah
  • Release date: April 2011
  • Publisher(s): Digital Press
  • ISBN: 9780080525112