V. Programming the Oracle Database with Web Services
by Kuassi Mensah
Oracle Database Programming using Java and Web Services
- Copyright
- Oracle Database Related Book Titles:
- Foreword
- Preface
- Introduction
- Acknowledgments
- I. Java in the Database
- 1. Stored Procedures as Database Programming Model
- 1.1. Rationale for Stored Procedures
- 1.1.1. Simplifying Database Programming
- 1.1.2. Centrally Managed Data Logic
- 1.1.3. Performance: Run JDBC Applications Faster in the Database
- 1.1.4. Encapsulation
- 1.1.5. Security: Advanced Data Access Control
- 1.1.6. Resource Optimization
- 1.1.7. Low-Cost Deployment
- 1.1.8. Fully Utilize Database Capabilities
- 1.2. Obstacles to the Adoption of Stored Procedures
- 1.3. Languages for Stored Procedures
- 1.3.1. Proprietary Languages
- 1.3.2. Java for Stored Procedures
- Complete Programming Language
- Secure Language
- Type Safety
- Robustness
- Productivity: Rapid Design Features
- Using Java as a Procedural Language
- Standard Specifications for Java Stored Procedures
- POJO-like Programming Model
- Stored Procedures and O/R Mapping
- Cross-Database Portability
- Huge Class Library and Tools: Reduced Development Time and Costs
- Skills Reuse
- Java Database Connectivity and SQL Data Access
- Starting with Java
- 1.3.3. .NET Languages
- 1.4. PL/SQL or Java
- 1.1. Rationale for Stored Procedures
- 2. OracleJVM: Under the Hood
- 2.1. Design Goals and Architecture
- 2.1.1. Tight Integration with the RDBMS
- 2.1.2. J2SE Compatibility
- 2.1.3. How Is Java Stored in the Database?
- 2.1.4. Class Sharing
- 2.1.5. Interapplication Isolation (JSR 121)
- 2.1.6. Contrasting OracleJVM with the JDK VM
- 2.1.7. Resource Control
- 2.1.8. SQL Data Access from Java in the Database
- 2.1.9. DBMS_JAVA: The All-Purpose Tool for Administering OracleJVM
- 2.2. Java Memory Management
- 2.2.1. Key Memory Structures of the Oracle Database
- 2.2.2. Java Memory Allocation Techniques
- 2.2.3. Garbage Collection Techniques
- 2.2.4. Java Memory Areas
- 2.2.5. Shared Servers versus Dedicated Processes
- 2.2.6. The Javapool
- 2.2.7. Top-Level Calls and Recursive Calls
- 2.2.8. State Preservation across Calls and End-of-Call Migration
- 2.2.9. End-of-Call, VM Termination, and Session Termination
- 2.3. Security in OracleJVM
- 2.4. Java VM Life Cycle
- 2.5. Java Execution in the Database
- 2.6. The Native Java Compiler (NCOMP)
- 2.7. Conclusion
- 2.1. Design Goals and Architecture
- 3. Developing and Running Java in the Database
- 3.1. Developing Java in the Database
- 3.1.1. Turning JDBC Applications into Java Stored Procedures
- 3.1.2. Creating or Loading Java in the Database
- Method 1: Create Java Sources in the Database, Interactively
- Method 2: Uploading Client-Side Java Files into the Database
- The loadjava Utility
- Method 3: Uploading Server-Side Java Files into the Database
- Method 4: Using JDeveloper and Third-party IDE
- Method 5: Creating Java Objects from an Existing CLOB/BLOB/BFILE
- Method 6: Creating Java Objects from XML (XDB folder)
- Dumping the Java Class Bytecode
- Checking the Status of Java Classes in Your Schema
- 3.1.3. Removing Java Sources, Classes, and Resources from the Database
- 3.1.4. Setting/Querying Environment Variable and System Properties
- 3.1.5. The Java Compiler within the Database
- 3.2. Turning Java in the Database into Stored Procedures
- 3.3. Mapping SQL and PL/SQL Types to/from Java Types
- 3.3.1. Mapping Matrix
- 3.3.2. Code Segments for Mapping
- Setup
- Mapping java.sql.Date to/from SQL DATE
- Mapping oracle.sql.DATE to/from SQL DATE
- Mapping java.lang.Integer to/from SQL NUMBER
- Mapping oracle.sql.NUMBER to SQL NUMBER
- Mapping oracle.sql.CHAR to/from SQL CHAR
- Mapping oracle.sql.RAW to/from SQL RAW
- Setup for CLOB, BLOB, and BFILE Mapping
- Steps
- Mapping CLOB Locator to/from java.sql.Clob
- Mapping java.sql.Blob to/from SQL BLOB
- Mapping oracle.sql.BFILE to/from SQL BFILE
- Mapping User-Defined Object Types to oracle.sql.STRUCT
- Mapping REF of ADT Table Types
- Mapping REF Cursors to java.sql.ResultSet
- Mapping VARRAY of Scalar SQL Types
- Mapping Nested Tables of Scalar SQL Types
- Mapping SQLJ Object Types
- 3.4. Invoking Java in the Database
- 3.5. Managing Java in the Database
- 3.1. Developing Java in the Database
- 4. Pragmatic Applications Using Java in the Database
- 4.1. CNXO: Secure Credit Card Processing with Oracle and JSSE
- 4.2. Using J2EE and Java in the Database Together
- 4.3. JDBC Call-Out to Non-Oracle Databases
- 4.4. SAP Java Connector: Accessing the SAP System from the Oracle Database
- 4.5. Excel-like Expression Parser in the Database
- 4.5.1. Rationales for Custom Parsers in the Database
- 4.5.2. What Is the Mini-Parser?
- 4.5.3. Implementing the Mini-Parser
- Step 1: Describing the Mini-Parser
- The Functional Structure of the Mini-Parser
- Code Outline
- Step 2: Generating the Mini-Parser
- Generating a Lexical Analyzer
- Generating the Parser
- Step 3: Deploying the Mini-Parser in the Database
- Step 4: Making the Mini-Parser Invocable through SQL and PL/SQL
- Step 5: Use It
- 5. Database Scripting Using Non-Java Languages
- 5.1. Why Contemplate Non-Java Languages for the Database?
- 5.2. Database Scripting with OracleJVM—Just for Fun!
- 5.2.1. Proof of Concept #1: Running TCL (JACL) Scripts in the Database
- 5.2.2. Proof of Concept #2: Running Jython (Python) in the Database
- 5.2.3. Proof of Concept #3: Running Kawa (Scheme) in the Database
- 5.2.4. Proof of Concept #4: Running Groovy in the Database
- What Is Groovy?
- Groovy Enabling Your Database
- Loading the Groovy Runtime in the Database
- Running Groovy Scripts in the Database
- Running Groovy Scripts as a Dynamically Loaded Class
- Storing Groovy Scripts in Database Columns
- Integrating Groovy with SQL
- Conclusion
- II. Java Persistence and Java SQL Data Access
- 6. Introducing the JDBC Technology and Oracle’s Implementation
- 6.1. JDBC Primer
- 6.1.1. First Steps in JDBC
- 6.1.2. JDBC within J2SE and J2EE Environments
- 6.2. Overview of JDBC Specifications
- 6.2.1. Overview of JDBC 1.22 Specification (Where Things Started!)
- 6.2.2. Overview of JDBC 2.0 Specification (A Major Spec!)
- 6.2.3. Overview of JDBC 3.0 Specification
- 6.2.4. Overview of Upcoming JDBC 4.0 Specification
- 6.2.5. JDBC Standards Support in the Oracle JDBC Drivers
- Oracle 8i Release 8.1.5 JDBC Drivers
- Oracle 8i Release 8.1.6 JDBC Drivers
- Oracle 8i Release 8.1.7 JDBC Drivers
- Oracle 9i Release 1 (9.0.1) JDBC Drivers
- Oracle 9i Release 2 ( 9.2.0.x) JDBC Drivers
- Oracle Database 10g Release 1 (10.1.0.x) JDBC Drivers
- Oracle Database 10g Release 2 (10.2.0.x) JDBC Drivers
- 6.3. Architecture and Packaging of Oracle JDBC Drivers
- 6.1. JDBC Primer
- 7. URL, DataSource, Connection, and Statements
- 7.1. JDBC URL
- 7.2. DataSources
- 7.3. Connections and Connection Services
- 7.3.1. JDBC Connections and Oracle Extensions
- 7.3.2. Connection Caching: Implicit Connection Cache
- Implicit Access to the Cache
- Saving the Values of Connection Attributes
- Caching Heterogeneously Authenticated Connections
- Connection Retrieval Based on User-Defined Attributes
- Applying Connection Attributes to a Cached Connection
- Setting Unmatched Attribute Values
- Connection Retrieval Based on Attributes and Weights
- List of System and Connection Properties
- Implicit Connection Cache Code Sample
- 7.3.3. The Connection Cache Manager
- 7.3.4. RAC Events and Fast Application Notification
- 7.3.5. High Availability: Fast Connection Failover
- 7.3.6. Scalability: Connection Load Balancing
- 7.3.7. JDBC Support for Transparent Application Fail-over
- 7.3.8. Proxy Authentication
- 7.3.9. Connection Wrapping
- 7.3.10. JDBC Connections in Grid Environment
- 7.4. JDBC Statements and Oracle Extensions
- 8. SQL Data Access and Manipulation
- 8.1. Key Metadata in JDBC
- 8.2. Manipulating Oracle Data Types with JDBC
- Example:
- 8.2.1. Manipulating SQL Null Data
- 8.2.2. Manipulating Character Data Types
- 8.2.3. Oracle JDBC Support for Number Data Types
- 8.2.4. JDBC Support for Long and Raw Data Types
- 8.2.5. JDBC Support for SQL Datetime Data Types
- 8.2.6. JDBC Support for LOB Datatypes
- 8.2.7. JDBC Support for ROWID
- 8.2.8. JDBC Support for OPAQUE Type
- 8.2.9. JDBC Support for XMLType
- 8.2.10. JDBC Support for SQL Object Types and References Types
- 8.2.11. JDBC Support for User-Defined Collections
- 8.2.12. JDBC Support for Spatial Types
- 8.2.13. Unsupported Types
- 8.3. Result Set Support in Oracle JDBC
- 8.4. RowSet
- 8.4.1. Introducing the RowSet API
- 8.4.2. JDBCRowSet and OracleJDBCRowSet
- 8.4.3. CachedRowSet and OracleCachedRowSet
- 8.4.4. WebRowSet and OracleWebRowSet
- 8.4.5. FilteredRowSet and OracleFilteredRowSet
- 8.4.6. JoinRowSet and OracleJoinRowSet
- 8.5. Conclusion
- 9. JDBC Quality of Services and Best Practices
- 9.1. Transaction Services
- 9.2. Security Services
- 9.2.1. Oracle JDBC Support for SSL
- 9.3. Tips and Best Practices
- 9.4. Conclusion
- 6. Introducing the JDBC Technology and Oracle’s Implementation
- III. Oracle Database Programming with SQLJ
- 10. Introducing the SQLJ Technology and Oracle’s Implementation
- 11. The SQLJ Language and Oracle Extensions
- 11.1. Declaration Statements
- 11.2. Executable Statements
- 11.3. Expressions in SQLJ
- 11.4. Interoperability: Using SQLJ and JDBC Together
- 11.5. Conclusion
- 12. SQLJ Data Access and Best Practices
- 12.1. Manipulating Oracle SQL and PL/SQL Data Types with SQLJ
- 12.1.1. Oracle SQLJ Type-Mapping Summary
- 12.1.2. Column Definitions
- 12.1.3. Manipulating SQL Null Data with SQLJ
- 12.1.4. Manipulating Character Data Types with SQLJ
- 12.1.5. Oracle SQLJ Support for Number Data Types
- 12.1.6. SQLJ Streams, LONG, and RAW Data Types
- 12.1.7. SQLJ Support for SQL Datetime Data Types
- 12.1.8. SQLJ Support for SQL LOB Data Types
- Overview
- Using the DBMS_LOB Package
- Using Standard JDBC LOB API and oracle.sql LOB Extensions
- Using SQLJ Streaming
- LOB and BFILE Stored Function Results
- LOB and BFILE Host Variables ( SELECT INTO Targets)
- LOBs and BFILEs in Iterator Declarations
- LOB and BFILE HostVariables as Named Iterator
- LOB and BFILE Host Variables as Positional Iterator (FETCH INTO Targets)
- 12.1.9. SQLJ Support for Oracle SQL ROWID
- 12.1.10. SQLJ Support for OPAQUE Types
- 12.1.11. SQLJ Support for SQL Object Types and SQL References Types
- 12.1.12. Serialized Java Objects
- 12.1.13. SQLJ Support for User-Defined SQL Collections
- 12.1.14. PL/SQL Associative Array
- 12.1.15. Unsupported Types
- 12.2. SQLJ Best Practices
- 12.3. Conclusion
- 12.1. Manipulating Oracle SQL and PL/SQL Data Types with SQLJ
- IV. Oracle Database Programming with JPublisher
- 13. Abridged Oracle JPublisher
- 13.1. Why JPublisher?
- 13.2. Overview
- 13.3. JPublisher In Action
- 13.3.1. User-Defined SQL Object Types
- 13.3.2. SQL Object Reference Types (REF types)
- 13.3.3. REF Cursor Types and Subclassing
- 13.3.4. User-Defined SQL Collection Types
- 13.3.5. User-Defined OPAQUE Types
- 13.3.6. XMLType
- 13.3.7. PL/SQL Conversion Functions
- 13.3.8. PL/SQL RECORD Types
- 13.3.9. PL/SQL Table or Scalar Index-by-Table
- 13.3.10. Oracle Streams AQ
- 13.3.11. Java in the Database
- 13.4. Conclusion
- 13. Abridged Oracle JPublisher
- V. Programming the Oracle Database with Web Services
- 14. Web Services and SOA for DBA, Data Architects, and Others
- 15. Database as Web Services Provider Service
- 15.1. Rationales for Database as Web Services Provider
- 15.2. How Does Database as Web Services Provider Work?
- 15.3. Turning Oracle Database Functionality into Web Services
- 15.4. Assembling Database Web Services Using the Command-Line Tool
- Introducing the Web Services Assembler
- Generic Steps for Publishing Database Functionality Using Web Services Assembler
- 15.4.1. Assembling PL/SQL Web Services Using Web Services Assembler
- 15.4.2. Assembling Java in the Database as a Web Service
- 15.4.3. Assembling SQL Queries or SQL DML Statements as Web Services
- 15.4.4. Assembling Oracle Streams AQ as Web Services
- 15.5. Data Type Restrictions
- 15.6. Conclusion
- 16. Database as Web Services Consumer
- VI. Putting Everything Together
- 17. 360-Degree Programming the Oracle Database
- 17.1. TECSIS Systems: Custom Enterprise Integration Framework
- 17.2. Oracle interMedia
- 17.2.1. What Is Oracle interMedia?
- 17.2.2. How Does It Work?
- 17.2.3. Rationales for Storing Media Data in the Database
- 17.2.4. interMedia Powered by the Oracle Database Extensibility Framework
- 17.2.5. interMedia Powered by Java in the Database
- 17.2.6. Developing Feature-Rich Multimedia Applications Using interMedia
- A PL/SQL Example
- Creating a Table with an ORDImage Column
- Importing Images
- Selecting and Viewing Image Properties
- Creating Thumbnails and Changing Formats
- Exporting Images with ORDImage.export()
- A Java Example
- Establishing a Connection
- Uploading from a File
- Setting Properties
- Getting and Displaying Properties
- Processing an Image
- Downloading to a File
- A PL/SQL Example
- 17.3. British Columbia: Online Corporate Registration
- 17.4. Information Retrieval Using Oracle Text
- 17.5. Database-Driven Content Management System (DBPrism CMS)
- 17.6. Conclusion
- 17. 360-Degree Programming the Oracle Database
- 1. Stored Procedures as Database Programming Model
Unless you have been living in a cave for the last four or five years, you have repeatedly heard about Web services and service-oriented architecture (SOA) as the new trend in IT, which allows resource consolidation, virtualization, and provisioning for optimized use, thereby reducing IT costs.
In Part I, we looked at Java within the database. In Part II, II, and IV, we looked at the APIs for persisting Java states or retrieving SQL data; the resulting code may run either within the database (i.e., manipulate the data in situ) or as a Java applet, Java EE framework/component, or stand-alone Java SE application/framework connected to the database.
In Part V, we’ll switch our focus from programming the database with Java to programming the database with Web services. As we will see, the exact term would be “assembling database Web services” but the association of “database” and “Web services” immediately raises eyebrows and the following questions:
Chapter 14 introduces the Web services technology and the service-oriented architecture for database developers, DBAs, and data architects (those familiar with these concepts and technologies may skip this chapter). Chapter 15 describes how you can turn your Oracle database into a Web services provider. Chapter 16 describes how you can configure your Oracle database to invoke external Web services from SQL, PL/SQL, and Java in the database. For starters, let’s (re)visit the rationales for coupling Web services with the Oracle database.
Chances are you have already heard about Web services as additional means for accessing and manipulating data in the database and, conversely, as means for the database to reach external services. According to an Evans Data Corporation survey among database developers (Winter 2004), “More than two out of three respondents, 68%, say they are in the process of exposing or invoking their database operations through standard Web services mechanisms.”
As another sign of the growing importance of database Web services, the leading RDBMS vendors, including Oracle, DB2, and SQL Server, furnish support with their database in a more or less integrated fashion. A summary of their implementations, the pros and cons, and how these compare is beyond the scope of this book. However, Chapter 15 contrasts the current implementation of Oracle database Web services with alternative implementation.
Motivations for exposing Oracle database functionality as Web services:
Reuse existing database applications, such as PL/SQL packages and Java classes in the database, as Web services.
Implement your own database-centric Web services, such as catalog services (i.e., Amazon Web services) and search services (i.e., Google Web services, Yahoo Web services) using database functionality[1] (i.e., SQL, PL/SQL, Streams/AQ, and Java).
Furnish new database-centric Web services such as XML documents storage and retrieval e.g., client applications can insert new XML documents or retrieve existing ones from a central corporate database, edit locally, and finally store the updated document; and client applications can transmit audit and logging information to the database for archiving.
Turn your database into a first-class member of your SOA.
Motivations for invoking external Web services from the database:
Allow your Oracle database to consume data produced by external Web services.
Federate legacy/heterogeneous data; build new applications that combine data from multiple sources, including Web services.
Implement automated applications, such as parcel tracking, stock prices tracking and analysis, scientific data tracking and analysis, maintain/refresh policies and/or tax tables, and weather information tracking and analysis.
Invoke business operations implemented as external Web services, such as placing new orders based on defined thresholds (i.e., number of products in inventory), batch processing of orders shipment, and batch processing of orders payment (using valid authorization numbers).
[1] I am not implying that Yahoo, Google, and Amazon use database operations to implement their Web services.
-
No Comment