book

Oracle Database Programming using Java and Web Services

Name: Oracle Database Programming using Java and Web Services
Author: Kuassi Mensah
ISBN: 9780080525112

by Kuassi Mensah

April 2011

Intermediate to advanced

1120 pages

19h 6m

English

Digital Press

Read now

Unlock full access

Copyright
Dedication
Oracle Database Related Book Titles:
Foreword
Preface
Introduction
Server-Side Database Programming (Stored Procedures)Database Programming Using Java in the DatabaseDatabase Programming Using JDBC, SQLJ, and JPublisherThe Good Old JDBCJDBC and Grid ComputingSQLJ: Bundling SQL and Java TogetherJPublisher: The Java Developer’s Swiss Army Knife for Accessing the Oracle DatabaseDatabase Programming with Web ServicesPutting Everything Together: The “All-You-Can-Eat Buffet”
Acknowledgments
I. Java in the Database
1. Stored Procedures as Database Programming Model
1.1. Rationale for Stored Procedures1.1.1. Simplifying Database Programming1.1.2. Centrally Managed Data LogicAvoids Code DuplicationFosters Data Logic SharingImplementation Transparency1.1.3. Performance: Run JDBC Applications Faster in the DatabaseSetupThe Java AplicationRunning the Java Application as a Stand-alone JDBC ApplicationRunning the Java Application as a Java Stored Procedure1.1.4. Encapsulation1.1.5. Security: Advanced Data Access Control1.1.6. Resource Optimization1.1.7. Low-Cost Deployment1.1.8. Fully Utilize Database Capabilities1.2. Obstacles to the Adoption of Stored Procedures1.2.1. Lack of Portability across RDBMS Vendors1.2.2. Scalability1.2.3. Maintenance and Resilience to Schema Change1.2.4. Hard to Debug1.2.5. Weak Support for Complex Types1.3. Languages for Stored Procedures1.3.1. Proprietary LanguagesSeamless Integration with SQLIDE SupportPortability1.3.2. Java for Stored ProceduresComplete Programming LanguageSecure LanguageType SafetyRobustnessProductivity: Rapid Design FeaturesUsing Java as a Procedural LanguageStandard Specifications for Java Stored ProceduresPOJO-like Programming ModelStored Procedures and O/R MappingCross-Database PortabilityHuge Class Library and Tools: Reduced Development Time and CostsSkills ReuseJava Database Connectivity and SQL Data AccessStarting with Java1.3.3. .NET Languages1.4. PL/SQL or Java1.4.1. PL/SQL and Java!
2. OracleJVM: Under the Hood
2.1. Design Goals and Architecture2.1.1. Tight Integration with the RDBMS2.1.2. J2SE Compatibility2.1.3. How Is Java Stored in the Database?Where Is the Java Source Stored?What Are Java Resources and How Are These Used?Where Is the Java Bytecode Stored?How Are Java Archives Handled?2.1.4. Class SharingSharing JVM Runtime Classes and MetadataSharing User Application ClassesSharing “Interned Strings”Sharing “Constant States” (Class Hot Loading)Sharing Read-Only System Objects2.1.5. Interapplication Isolation (JSR 121)2.1.6. Contrasting OracleJVM with the JDK VMSession-Based Architecture and ScalabilityRobustnessThreadingJava OutputInvocation Mechanisms, Locating Classes, and Transitive ClosurePreferencesConsistent Behavior across PlatformsJava 2 Security2.1.7. Resource Control2.1.8. SQL Data Access from Java in the Database2.1.9. DBMS_JAVA: The All-Purpose Tool for Administering OracleJVMLongnames and Shortnames2.2. Java Memory Management2.2.1. Key Memory Structures of the Oracle Database2.2.2. Java Memory Allocation TechniquesFrontier consingBuddy allocation2.2.3. Garbage Collection TechniquesGenerational GCMark-sweep GCCopy GCReference-counting GCSelf-tuning GC2.2.4. Java Memory AreasSessionspaceOldspaceRunspaceStackspaceNewspaceSwapping and Pinning Libunits2.2.5. Shared Servers versus Dedicated ProcessesSession in Dedicated ProcessSession in Shared Server Process2.2.6. The Javapool2.2.7. Top-Level Calls and Recursive Calls2.2.8. State Preservation across Calls and End-of-Call Migration2.2.9. End-of-Call, VM Termination, and Session Termination2.3. Security in OracleJVM2.3.1. User Authentication2.3.2. Database-Schema Security2.3.3. Resolver Specification and Class-Resolution Security2.3.4. Login-User and Effective-User Security2.3.5. Java 2 SecurityPermissionsThe Java Security Manager2.3.6. Java 2 Security in OracleJVMSecurity PolicyQuerying the PolicyTableThe Security ManagerDetermining and Granting PermissionsRestricting PermissionRevoking PermissionDisabling PermissionsDeleting PermissionsSystem Classes Security2.3.7. OracleJVM Security Best Practices2.3.8. JNI Calls2.4. Java VM Life Cycle2.4.1. OracleJVM Install, Uninstall, and Reinstall2.4.2. Java VM Initialization and TerminationJava VM or Java Application InitializationJava VM Termination2.5. Java Execution in the Database2.5.1. The OracleJVM InterpreterThreaded InterpreterPerformance Optimization2.6. The Native Java Compiler (NCOMP)2.6.1. What Is NCOMP?2.6.2. Requirements and Design ChoicesPortabilityScalabilitySecurity/IntegrityJava Bytecode to C TranslationInteroperability between Interpreted Bytecode and Native Code2.6.3. The NCOMP ProcessCompilation and Linkage2.6.4. The NCOMP CommandForced NCOMPingNCOMPing nonloaded java classes2.6.5. The STATUSNC CommandClass-level CheckMethod-level Check2.6.6. Dumping Java Classes with NCOMP2.6.7. NCOMP Configuration and PlanningConfiguring C Compiler OptimizationsPlatform DependenciesNCOMP: Time PlanningNCOMP: Disk Space Planning2.6.8. NCOMP Performance Tips, Improper Use, and TroubleshootingPerformance-Tuning Tips for Java in the Database2.7. Conclusion
3. Developing and Running Java in the Database
3.1. Developing Java in the Database3.1.1. Turning JDBC Applications into Java Stored ProceduresJDBC ConnectionsTurning Instance Methods into Public Static Methods3.1.2. Creating or Loading Java in the DatabaseMethod 1: Create Java Sources in the Database, InteractivelyMethod 2: Uploading Client-Side Java Files into the DatabaseThe loadjava UtilityMethod 3: Uploading Server-Side Java Files into the DatabaseMethod 4: Using JDeveloper and Third-party IDEMethod 5: Creating Java Objects from an Existing CLOB/BLOB/BFILEMethod 6: Creating Java Objects from XML (XDB folder)Dumping the Java Class BytecodeChecking the Status of Java Classes in Your Schema3.1.3. Removing Java Sources, Classes, and Resources from the Database3.1.4. Setting/Querying Environment Variable and System PropertiesSetting Options for the Java Compiler in the DatabaseTurning the Bytecode Verifier ON/OFF3.1.5. The Java Compiler within the DatabaseInvoking the OracleJVM CompilerAutomatic Compilation and Java Classes Dependencies Management3.2. Turning Java in the Database into Stored Procedures3.2.1. Call Spec TypesTop-Level Call SpecPL/SQL Packaged Call SpecsObject Type Call Spec3.3. Mapping SQL and PL/SQL Types to/from Java Types3.3.1. Mapping Matrix3.3.2. Code Segments for MappingSetupMapping java.sql.Date to/from SQL DATEMapping oracle.sql.DATE to/from SQL DATEMapping java.lang.Integer to/from SQL NUMBERMapping oracle.sql.NUMBER to SQL NUMBERMapping oracle.sql.CHAR to/from SQL CHARMapping oracle.sql.RAW to/from SQL RAWSetup for CLOB, BLOB, and BFILE MappingStepsMapping CLOB Locator to/from java.sql.ClobMapping java.sql.Blob to/from SQL BLOBMapping oracle.sql.BFILE to/from SQL BFILEMapping User-Defined Object Types to oracle.sql.STRUCTMapping REF of ADT Table TypesMapping REF Cursors to java.sql.ResultSetMapping VARRAY of Scalar SQL TypesMapping Nested Tables of Scalar SQL TypesMapping SQLJ Object Types3.4. Invoking Java in the Database3.4.1. SetupSetting Up the Database TableReusing a JDBC Code in the Database3.4.2. Invoking Java in the Database Using OJVMJAVADescription3.4.3. Invoking Java in the Database through the PL/SQL WrapperStep 1: Publishing the PL/SQL wrapper (from a SQL*Plus session)Step 2: invoking the Java method through the PL/SQL wrapper (from a SQl*Plus session)3.4.4. Invoking Java in the Database through Client-side StubWhat Is the Problem with Call Specs?The SolutionHow Does Java Invocation through the Client Stub Work?Step 1: Generate the client stubStep 2: Define the client applicationStep 3: Compile the client applicationStep 4: Invoke Java in the database through the client stub3.4.5. Errors and Exceptions Handling3.5. Managing Java in the Database3.5.1. Java Audit3.5.2. Oracle Enterprise Manager (Database Control) Support for Java in the Database

4. Pragmatic Applications Using Java in the Database
4.1. CNXO: Secure Credit Card Processing with Oracle and JSSEArchitecture Overview and DesignImplementation ChallengesJSSE-enabling Oracle 9i Release 2URLReader as Java Stored ProcedureStep 1: URLReader.javaStep 2: Create a PL/SQL WrapperCNXO Conclusion4.2. Using J2EE and Java in the Database Together4.2.1. Auto-generating Primary Keys for BMP Entity BeansMapping CMP Beans Finders Using Java Stored Procedures4.2.2. Calling-out EJB from OracleJVMRationales for EJB Call-OutCalling out a Stateless Bean in OC4J from Java DB using RMI/IIOPStep 1: Download, Install, and Start up OC4JStep 2: Deploying the Demo EJB to OC4JStep 3: Turning EJB Client into a Java Stored ProcedureStep 4: Call out the EJB from within the Database4.2.3. HTTP Call-Out: The Poor Man’s Cache Invalidation4.2.4. JMS over Streams/AQ in the DatabaseWhat Is Oracle Database Streams/AQ?JMS in OracleJVM4.3. JDBC Call-Out to Non-Oracle Databases4.3.1. Description and Rationales4.3.2. How Does It Work?Step 1: Downloading and Uploading the Corresponding JDBC DriversStep 2: Creating a Generic Java ProgramStep 3: Creating a Generic JDBC Call-out Application4.4. SAP Java Connector: Accessing the SAP System from the Oracle Database4.5. Excel-like Expression Parser in the Database4.5.1. Rationales for Custom Parsers in the Database4.5.2. What Is the Mini-Parser?4.5.3. Implementing the Mini-ParserStep 1: Describing the Mini-ParserThe Functional Structure of the Mini-ParserCode OutlineStep 2: Generating the Mini-ParserGenerating a Lexical AnalyzerGenerating the ParserStep 3: Deploying the Mini-Parser in the DatabaseStep 4: Making the Mini-Parser Invocable through SQL and PL/SQLStep 5: Use It
5. Database Scripting Using Non-Java Languages
5.1. Why Contemplate Non-Java Languages for the Database?5.1.1. Common Language Runtime in RDBMS5.1.2. Scripting Languages Support in RDBMSPerl and PHP in MySQLRuby Perl, Pythin, and TCL Plug-ins in PostgreSQL5.2. Database Scripting with OracleJVM—Just for Fun!5.2.1. Proof of Concept #1: Running TCL (JACL) Scripts in the DatabaseJACL Enable Your DatabaseRun TCL Scripts in Your DatabaseConclusions5.2.2. Proof of Concept #2: Running Jython (Python) in the DatabaseWhat Is Jython?Jython Enabling Your DatabaseRunning Jython Programs in the DatabaseConclusion5.2.3. Proof of Concept #3: Running Kawa (Scheme) in the DatabaseScheme Enable Your Database (Scheme on you!)Running Scheme Programs in the DatabaseConclusions5.2.4. Proof of Concept #4: Running Groovy in the DatabaseWhat Is Groovy?Groovy Enabling Your DatabaseLoading the Groovy Runtime in the DatabaseRunning Groovy Scripts in the DatabaseRunning Groovy Scripts as a Dynamically Loaded ClassStoring Groovy Scripts in Database ColumnsIntegrating Groovy with SQLConclusion
II. Java Persistence and Java SQL Data Access
6. Introducing the JDBC Technology and Oracle’s Implementation
6.1. JDBC Primer6.1.1. First Steps in JDBCStep 1: Loading and Registering the DriverStep 2: Connecting to the DatabaseMethod 1: Using DriverManager FactoryMethod 2: Using OracleDataSource FactoryStep 3: Creating a StatementStep 4: Executing a SQL StatementStep 5: Retrieving and Processing Result SetsStep 6: Closing Results Sets, Statements, and ConnectionsA Basic but Complete JDBC Program6.1.2. JDBC within J2SE and J2EE Environments6.2. Overview of JDBC Specifications6.2.1. Overview of JDBC 1.22 Specification (Where Things Started!)Key Features6.2.2. Overview of JDBC 2.0 Specification (A Major Spec!)Features Introduced in the JDBC 2.1 Core APIFeatures Introduced in the javax.sql JDBC 2.0 Optional Package6.2.3. Overview of JDBC 3.0 Specification6.2.4. Overview of Upcoming JDBC 4.0 Specification6.2.5. JDBC Standards Support in the Oracle JDBC DriversOracle 8i Release 8.1.5 JDBC DriversOracle 8i Release 8.1.6 JDBC DriversOracle 8i Release 8.1.7 JDBC DriversOracle 9i Release 1 (9.0.1) JDBC DriversOracle 9i Release 2 ( 9.2.0.x) JDBC DriversOracle Database 10g Release 1 (10.1.0.x) JDBC DriversOracle Database 10g Release 2 (10.2.0.x) JDBC Drivers6.3. Architecture and Packaging of Oracle JDBC Drivers6.3.1. Rearchitected Oracle JDBC DriversFaster Features Implementation and Reduction of Functional GapThe Main Packages of Oracle JDBCPerformance Optimizations6.3.2. Packaging of Oracle JDBC DriversType-4 Client-side JDBC (JDBC-Thin)Type-4 Server-side JDBCType-2 Client-side JDBC (JDBC-OCI)Type-2 Server-side JDBCAdditional JARSInstant Client (“Homeless” Oracle Client)6.3.3. Features Differences Between Driver Types6.3.4. JDBC Drivers and Database InteroperabilityHow to Check the Version of the JDBC Drivers
7. URL, DataSource, Connection, and Statements
7.1. JDBC URL7.2. DataSources7.2.1. The OracleDataSource7.2.2. DataSources and JNDIJNDI 101Using Oracle DataSources with Stand-alone JNDIStep 1Step 2Step 3Step 4Step 5Step 67.3. Connections and Connection Services7.3.1. JDBC Connections and Oracle Extensions7.3.2. Connection Caching: Implicit Connection CacheImplicit Access to the CacheSaving the Values of Connection AttributesCaching Heterogeneously Authenticated ConnectionsConnection Retrieval Based on User-Defined AttributesApplying Connection Attributes to a Cached ConnectionSetting Unmatched Attribute ValuesConnection Retrieval Based on Attributes and WeightsList of System and Connection PropertiesImplicit Connection Cache Code Sample7.3.3. The Connection Cache ManagerManaging and Maintaining CachesThe Connection Cache Manager APIConnection Cache Manager CallbackreleaseConnection7.3.4. RAC Events and Fast Application NotificationStep 4: Configure ONS on Each RAC Server NodeStep 5: Configuring/Subscribing ONS ClientsTesting ONS OperationsStep 1Step 2Output7.3.5. High Availability: Fast Connection FailoverHow Fast Connection Fail-over WorksConfiguring Fast Connection Fail-overStep 2: Enabling Fast Connection Fail-overStep 3: Catching and Retrying Connection Request7.3.6. Scalability: Connection Load BalancingEnabling Runtime Connection Load BalancingStep 1Step 2Step 37.3.7. JDBC Support for Transparent Application Fail-overWhat Usually Works with TAFWhat Does Not Work with TAFTAF ConfigurationCustomizing TAF Using OCI Callback and Fail-over EventsOCI Fail-over InterfaceGraceful ShutdownFCF versus TAF7.3.8. Proxy AuthenticationHow Proxy Authentication WorksJDBC Support for Proxy AuthenticationCaching Proxy Sessions7.3.9. Connection Wrapping7.3.10. JDBC Connections in Grid EnvironmentVirtualizing the Database as a ServiceFast Event Notification, Connection Fail-over, and Load Balancing7.4. JDBC Statements and Oracle Extensions7.4.1. JDBC Statement Types7.4.2. StatementIn a NutshellStandard Statement API and Oracle Extensions7.4.3. PreparedStatementIn a NutshellSteps 1 and 2Step 3Step 4The Standard PreparedStatement API and OraclePreparedStatementPerformance7.4.4. CallableStatement (Calling Stored Procedures)In a NutshellStep 1Step 2Step 3Step 4Step 5The Standard API and OracleCallableStatementPerformance7.4.5. Retrieval of Auto-Generated Keys and DML with ReturningJDBC 3.0 Auto-Generated Keysjava.sql.DatabaseMetaData:java.sql.Statement:DML with RETURNING7.4.6. Statement CachingIn a NutshellImplicit Statement CachingA Complete Example: ImplStmtCache.javaExplicit Statement CachingA Complete Example: ExplStmtCache.java7.4.7. DML BatchingStandard Batch UpdatesExample 1Example 2: syntaxOracle Update BatchingExample:
8. SQL Data Access and Manipulation
8.1. Key Metadata in JDBC8.1.1. DatabaseMetaData: OracleDatabaseMetaData8.1.2. ResultSetMetaData: OracleResultSetMetaData8.1.3. ParameterMetaData8.1.4. StructMetaData8.2. Manipulating Oracle Data Types with JDBCExample:8.2.1. Manipulating SQL Null Data8.2.2. Manipulating Character Data TypesCHAR, CHAR(n), CHAR(n BYTE), CHAR(n CHAR)VARCHAR2 (n), VARCHAR2(n BYTE), VARCHAR2 (n CHAR)NCHAR, NCHAR(n), NVARCHAR2 (n)8.2.3. Oracle JDBC Support for Number Data TypesNUMBER, NUMBER(p), NUMBER(p, s)BINARY_FLOAT and BINARY_DOUBLE8.2.4. JDBC Support for Long and Raw Data TypesSQL RAW(s)LONG and LONG RAW8.2.5. JDBC Support for SQL Datetime Data TypesDATETIMESTAMP, TIMESTAMPTZ, TIMESTAMPLTZINTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND8.2.6. JDBC Support for LOB DatatypesOverviewTemporary LOBExamples of BLOBs ManipulationReading BLOBsWriting BLOBsExamples of CLOB ManipulationReading CLOB DataWriting CLOB DataExamples of BFILE ManipulationPrerequisitesReading BFILE DataCreating BFILE Data8.2.7. JDBC Support for ROWID8.2.8. JDBC Support for OPAQUE TypeOPAQUEOPAQUE Descriptor8.2.9. JDBC Support for XMLTypeXML DB and XML TypeJDBC Support for XMLType8.2.10. JDBC Support for SQL Object Types and References TypesType MapWeak Type Mapping: Struct and STRUCTCode SnippetStrong Type Mapping: SQLData and ORADataSQLDataORAData and ORADataFactorySQLJ Object TypesREFManipulating REF of ADT Table TypesREF Cursors8.2.11. JDBC Support for User-Defined CollectionsARRAY, VARRAYs, and ArrayDescriptorVARRAY of NUMBERVARRAY of VARCHAR2VARRAY of DATENested TablesNESTED TABLE of NUMBERNESTED TABLE of VARCHAR2NESTED TABLE of DATEPL/SQL Associative ArrayPL/SQL Record and PL/SQL Boolean8.2.12. JDBC Support for Spatial Types8.2.13. Unsupported Types8.3. Result Set Support in Oracle JDBC8.3.1. The Result Set API in a NutshellThe Evolution of the Result Set APIMethods Specified by the java.sql.ResultSet InterfaceFetch DirectionFetch Size8.3.2. The Oracle Result Set InterfaceImplementation History8.3.3. Oracle JDBC Support for Scrollable Result SetsScrollable Result Set RetrictionsScrolling the Result SetsThe Oracle Result Set Cache Interface8.3.4. Oracle JDBC Support for Updatable Result SetsUpdatable Result Set RestrictionsUpdating Result SetsDeleting Result SetsInserting Result Sets8.3.5. Prefetching and Auto RefreshRow PrefetchingAuto Refetch8.3.6. Changes Detection and VisibilityVisibility of Internal and External Changes8.4. RowSet8.4.1. Introducing the RowSet API8.4.2. JDBCRowSet and OracleJDBCRowSet8.4.3. CachedRowSet and OracleCachedRowSetCreating and Populating CachedRowSetsManipulating and Making Changes to Disconnected DataEvent Listeners8.4.4. WebRowSet and OracleWebRowSetUnderstanding the WebRowSet XML SchemaCreating a WebRowSet Object and Dumping Its Contents to an XML DocumentReading an XML Document into a WebRowSet ObjectChanging a WebRowSet Object and Synchronizing Back to the DatasourceInserting a New Row in a WebRowSet ObjectDeleting a Row in a WebRowSet ObjectUpdating a Row in a WebRowSet8.4.5. FilteredRowSet and OracleFilteredRowSetDefine and Implement the Predicate InterfaceCreating a FilteredRowSet Object and Setting PropertiesPopulating the FilteredRowSetEnabling FiltersRetrieving Filtered Rows and Closing the FilteredRowSet Object8.4.6. JoinRowSet and OracleJoinRowSetCreating an Empty Instance of JoinRowSet Object and Setting PropertiesCreating and Adding RowSet Objects to the JoinRowSetDefining the Match Column(s)Navigating and Consuming the JoinRowSet ObjectsFurther Considerations8.5. Conclusion
9. JDBC Quality of Services and Best Practices
9.1. Transaction Services9.1.1. Transactions9.1.2. AutoCommit9.1.3. Transaction Isolation Levels9.1.4. Transaction SavePoint Support9.1.5. Global/Distributed TransactionThe X/Open Distributed Transaction ModelJDBC Support for Global TransactionsJDBC XADataSource and XAConnection InterfacesNative XA Support in Oracle JDBCTwo-Phase Commit Optimization9.1.6. Connection Sharing between Local and Global TransactionsNO_TXN -> LOCAL_TXN and LOCAL_TXN-> NO_TXNNO_TXN -> GLOBAL_TXN and GLOBAL_TXN -> NO_TXN9.2. Security Services9.2.1. Oracle JDBC Support for SSLSSL in a NutshellJava Support for SSL (JSSE)Configuring and Using the Oracle JDBC Drivers with SSLCreating and Storing Server Certificates in an Oracle WalletCreating a WalletCreating a Certificate RequestObtaining and Importing a CertificateSaving the Wallet and CertificateConfigure the Oracle Database for SSLCreating and Storing Client Certificates in a TruststoreCreating the Client Certificate (Optional)Configuring the JDBC-Thin Driver for SSLUsing JDBC-Thin with SSL9.3. Tips and Best Practices9.3.1. End-to-End Tracing9.3.2. Common Errors9.3.3. Optimizing Result Set Retrieval9.3.4. Logging ServiceLogging LevelsLoggersTracing JDBC Using System Properties9.4. Conclusion
III. Oracle Database Programming with SQLJ
10. Introducing the SQLJ Technology and Oracle’s Implementation
10.1. Overview10.1.1. What Is SQLJ?The SQLJ SpecificationThe SQLJ TranslatorThe SQLJ RuntimeSQLJ Profiler10.1.2. Why SQLJ?Benefits Brought by the Standard ISO SQLJSimplicityTranslation-Time Type CheckingInteroperability with JDBCBenefits Brought by Oracle SQLJ ExtensionsSupport for Dynamic SQLCompile-Time Performance OptimizationsThe Architecture and Packaging of Oracle SQLJ10.1.3. The Oracle SQLJ TranslatorThe SQLJ Parser, the Java Parser, and the SQL ParsersBasic OptionsEnvironment and Customizer OptionsJavac OptionsAdvanced OptionsSemantics Checking and Offline ParsingCode GenerationThe Oracle CustomizerTranslator Exit Codes10.1.4. The Oracle SQLJ RuntimeRuntime PackagingRuntime Packages That Support the SQLJ APIPackages That Support Standard SQLJ APIPackage(s) That Support Oracle SQLJ Extensions APIs10.1.5. Environment Setup10.1.6. SQLJ Primer10.2. SQLJ in the DatabaseLoading, Translating, and Publishing SQLJ in the DatabaseEnvironment CheckLoading SQLJ Sources, Classes, and Resources in the DatabaseSetting Compiler OptionsPublishing SQLJ to SQL, PL/SQL, and JDBCDifferences in Code and Behavior
11. The SQLJ Language and Oracle Extensions
11.1. Declaration Statements11.1.1. Import Statements11.1.2. Connection ContextsUser-Defined Connection Context InterfaceDataSourceStatement Caching11.1.3. Execution ContextsMethods of the ExecutionContext ClassExecution Contexts and Multithreading11.1.4. IteratorsWeakly Typed IteratorsStrongly Typed IteratorsScrollable IteratorsScrollable Named IteratorsScrollable Positional IteratorsSubclassing Iterator ClassesIterator Sensitivity11.1.5. IMPLEMENTS Clause in Context DeclarationsIterator Implementing User-Defined InterfacesPositioned Update/Delete Iterators11.1.6. WITH Clause in Context DeclarationsSupported SQLJ Constants in WITH ClauseUnsupported SQLJ Constants in WITH Clause11.2. Executable Statements11.2.1. Statement ClausesSELECT INTO ClauseFETCH ClauseCOMMIT or ROLLBACK ClauseSAVEPOINT ClauseSET TRANSACTION ClauseProcedure ClauseSQL ClausePL/SQL Clause11.2.2. Assignment ClausesQuery ClauseFunction ClauseIterator Conversion Clause11.2.3. Dynamic SQLMeta Bind ExpressionsOther Dynamic SQL Mechanisms11.3. Expressions in SQLJHost VariablesHost Expressions11.3.1. Context and Result ExpressionsContext ExpressionsResult Expressions11.3.2. Expressions Evaluation11.4. Interoperability: Using SQLJ and JDBC Together11.4.1. JDBC to SQLJ InteroperabilityLeveraging Oracle JDBC Connection Services11.4.2. SQLJ to JDBC Interoperability11.5. Conclusion
12. SQLJ Data Access and Best Practices
12.1. Manipulating Oracle SQL and PL/SQL Data Types with SQLJ12.1.1. Oracle SQLJ Type-Mapping Summary12.1.2. Column DefinitionsAutomatic Registration of Column Types and SizesEnabling Column Definition12.1.3. Manipulating SQL Null Data with SQLJ12.1.4. Manipulating Character Data Types with SQLJCHAR, CHAR(n), CHAR(n BYTE), CHAR(n CHAR) VARCHAR2 (n), VARCHAR2(n BYTE), VARCHAR2 (n CHAR)NCHAR, NCHAR(n), NVARCHAR2 (n)12.1.5. Oracle SQLJ Support for Number Data TypesNUMBER, NUMBER(p), NUMBER(p, s)BINARY_FLOAT and BINARY_DOUBLE12.1.6. SQLJ Streams, LONG, and RAW Data TypesSQL RAW(s)LONG, LONG RAWSQLJ StreamsUsing SQLJ Streams to Send and Retrieve Data to and from the DatabaseSQLJ Stream Objects as Procedures Output Parameters and Function Return Values12.1.7. SQLJ Support for SQL Datetime Data TypesDATETIMESTAMP, TIMESTAMPTZ, and TIMESTAMPLTZINTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND12.1.8. SQLJ Support for SQL LOB Data TypesOverviewUsing the DBMS_LOB PackageUsing Standard JDBC LOB API and oracle.sql LOB ExtensionsUsing SQLJ StreamingLOB and BFILE Stored Function ResultsLOB and BFILE Host Variables ( SELECT INTO Targets)LOBs and BFILEs in Iterator DeclarationsLOB and BFILE HostVariables as Named IteratorLOB and BFILE Host Variables as Positional Iterator (FETCH INTO Targets)12.1.9. SQLJ Support for Oracle SQL ROWID12.1.10. SQLJ Support for OPAQUE TypesXMLType12.1.11. SQLJ Support for SQL Object Types and SQL References TypesType MapWeak Type Mapping: Struct and STRUCTStrong Type Mapping: SQLData and ORADataSQLJ Object TypesSQL REFREF Cursors12.1.12. Serialized Java ObjectsSerializing Java Objects Using typeMAPSerializing Java Objects Using ORADataSerializing Java Objects by Value12.1.13. SQLJ Support for User-Defined SQL CollectionsVARRAYsNested Tables12.1.14. PL/SQL Associative Array12.1.15. Unsupported TypesDATALINKNamed ParametersPL/SQL BOOLEAN and PL/SQL RECORD12.2. SQLJ Best Practices12.2.1. Row Prefetch12.2.2. Statement Caching12.2.3. Update Batching12.3. Conclusion
IV. Oracle Database Programming with JPublisher
13. Abridged Oracle JPublisher
13.1. Why JPublisher?13.2. OverviewFeatures13.2.1. Environment RequirementsInstalling JPublisherEnvironment Checklist13.2.2. JPublisher OptionsJava EnvironmentConnectionSQL Data Types Categories and MappingNumeric Data Types MappingInput FilesItems to PublishJava Code GenerationPL/SQL Code GenerationStyle Files for Mapping Java in the DatabaseType-MapsInput/OutputBackward Compatibility13.3. JPublisher In Action13.3.1. User-Defined SQL Object Types13.3.2. SQL Object Reference Types (REF types)13.3.3. REF Cursor Types and Subclassing13.3.4. User-Defined SQL Collection Types13.3.5. User-Defined OPAQUE Types13.3.6. XMLType13.3.7. PL/SQL Conversion Functions13.3.8. PL/SQL RECORD Types13.3.9. PL/SQL Table or Scalar Index-by-Table13.3.10. Oracle Streams AQ13.3.11. Java in the Database13.4. Conclusion
V. Programming the Oracle Database with Web Services
14. Web Services and SOA for DBA, Data Architects, and Others
14.1. Web Services 10114.1.1. Core Web Services TechnologiesXMLWSDLSOAPRESTUDDIFinal Thoughts14.2. Service-Oriented Architecture (SOA): The Bigger PictureSOA 10114.3. Conclusion
15. Database as Web Services Provider Service
15.1. Rationales for Database as Web Services Provider15.2. How Does Database as Web Services Provider Work?15.2.1. Implementation and Packaging15.2.2. How Does Oracle Database as Web Services Provider Work?15.2.3. Web Services and SOA Features in Oracle Application Server 10.1.3Web Services InteroperabilityWeb Services SecurityWeb Services ManagementWeb Services Reliable MessagingSOAP Message Auditing and LoggingBPEL IntegrationWeb Services Invocation Framework (WSIF) SupportOther Web Services Features15.3. Turning Oracle Database Functionality into Web Services15.3.1. Type Conversions and Result Set RepresentationType Conversion between SQL Types and XML TypesSQL Result Sets Representations15.3.2. Setting up the Oracle AS OC4J for Database as Web Services Provider15.3.3. Assembling PL/SQL Web Services Using JDeveloper WizardSetupGenerating the PL/SQL Web ServiceGenerating the PL/SQL Web Service ProxyPL/SQL Web Services Limitations with JDeveloper15.4. Assembling Database Web Services Using the Command-Line ToolIntroducing the Web Services AssemblerGeneric Steps for Publishing Database Functionality Using Web Services Assembler15.4.1. Assembling PL/SQL Web Services Using Web Services AssemblerStep 1aStep 1bStep 2 (Optional)Step 3 and 4Step 5Step 6 (Optional)Step 7Step 815.4.2. Assembling Java in the Database as a Web ServicePrerequisites:Step 1aStep 1b15.4.3. Assembling SQL Queries or SQL DML Statements as Web ServicesAssembling a SQL Query Web Service from a SQL StatementStep 1aStep 1b15.4.4. Assembling Oracle Streams AQ as Web ServicesAssembling Streams AQ Web Services Using Web Services AssemblerStep 1aStep 1bStep 5Accessing an Oracle AQ Queue with JMS15.5. Data Type Restrictions15.6. Conclusion
16. Database as Web Services Consumer
16.1. Rationales for Database as Web Services Consumer16.2. How Database as Web Services Consumer Works16.2.1. The Software Pieces16.2.2. The Required Steps16.3. Turning Your Oracle Database into a Web Service Consumer16.3.1. Ensure That Java Is Installed in the Database16.3.2. Installing JPublisher on the Client Machine16.3.3. Installing the Web Services Call-Out Utility in Your Database16.4. Database Web Services Call-Out Samples16.4.1. Calling Out Google Search Web Service16.4.2. Calling Out the Phone Verifier Web Service16.5. Conclusion
VI. Putting Everything Together
17. 360-Degree Programming the Oracle Database
17.1. TECSIS Systems: Custom Enterprise Integration Framework17.1.1. About the Company17.1.2. About the Application17.1.3. Our Business and Technical RequirementsBusiness RequirementsTechnical RequirementsDesign and Programming Choices17.1.4. The Architecture of the Integration FrameworkTypical Use CasesJava Stored Procedures Calling External SystemsExternal Systems Calling Java Stored Procedures17.1.5. The Complete Picture17.1.6. Conclusion17.2. Oracle interMedia17.2.1. What Is Oracle interMedia?17.2.2. How Does It Work?interMedia Object Interface: New Database TypesBLOB/BFILE SupportinterMedia Object Interface17.2.3. Rationales for Storing Media Data in the DatabaseSynchronization with Related Database Information: Just Another Data TypeRobustness, Reliability, Availability, Security, ScalabilitySecurityScalabilityAdministration, Backup, Recovery/Disaster RecoverySearch, Query, and Transactional CapabilitiesSimplicity and Reduced Development CostCode Reduction: Built-in Operators17.2.4. interMedia Powered by the Oracle Database Extensibility Framework17.2.5. interMedia Powered by Java in the DatabaseThe Java Media ParserThe Java Image Processor (based on JAI)Image Metadata ExtractionImage Metadata WritingAudio and Video Metadata ExtractionImage Processing and Image Format ConversionOracle interMedia Java ClassesClient-side JAI Applications17.2.6. Developing Feature-Rich Multimedia Applications Using interMediaA PL/SQL ExampleCreating a Table with an ORDImage ColumnImporting ImagesSelecting and Viewing Image PropertiesCreating Thumbnails and Changing FormatsExporting Images with ORDImage.export()A Java ExampleEstablishing a ConnectionUploading from a FileSetting PropertiesGetting and Displaying PropertiesProcessing an ImageDownloading to a File17.3. British Columbia: Online Corporate Registration17.3.1. Corporate Online: Background17.3.2. How It Works17.3.3. Architecture: Requirements and DesignRequirement 1: How to Submit Only Committed ChangesRequirement 2: Processing the Received MessageRequirement 3: Database Web Services17.3.4. Messaging across Tiers17.3.5. Future Work17.3.6. Conclusion17.4. Information Retrieval Using Oracle Text17.4.1. What Is Oracle Text?17.4.2. Why Java in the Database?17.4.3. Technical FeaturesIndex TypesQuery OperatorsDocument ServicesHighlightingMarkupSnippetTheme ExtractionGistAdvanced FeaturesClassificationClassification TrainingClusteringKnowledge BaseXML Support17.4.4. Benefits of an Integrated Search Capability17.4.5. YapaArchitecture and ImplementationDatabase schema and indexingClassifier OutlineClusteringUser InterfaceSearch PanelStructure ViewListCategoriesClusterContent ViewBrowseConfiguring, Compiling, and Testing the Demo17.4.6. Conclusion17.5. Database-Driven Content Management System (DBPrism CMS)17.5.1. DBPRISM CMS: Key Features and Benefits17.5.2. The Architecture of DBPrism CMS17.5.3. DBPrism CMS InternalsHow DBPrism CMS WorksComplete Execution FlowDocument StructureGeneration of the XML Document17.5.4. Extended CapabilitiesSearching for Nonpublic PagesUpdated PagesBroken Links17.5.5. Text Searching17.5.6. Installing DBPRism CMSConfigurationFour-Step Installation17.5.7. Future Work17.6. Conclusion

Content preview from Oracle Database Programming using Java and Web Services

Chapter 12. SQLJ Data Access and Best Practices

In this chapter, we’ll look at Oracle SQL data manipulation using SQLJ and then best practices with SQLJ.

Manipulating Oracle SQL and PL/SQL Data Types with SQLJ

SQL and PL/SQL data are manipulated in SQLJ through SQLJ host variables/expressions, result expressions, and iterator accessors. This section explains the SQLJ mechanisms to manipulate SQL data, using frequent references to the corresponding sections in Chapter 8.

Oracle SQLJ Type-Mapping Summary

Because Oracle SQLJ leverages the Oracle JDBC drivers and its wrapper classes, the mapping of SQL data types in SQLJ is almost identical to JDBC mapping, as summarized in Table 3.2. However, there are additional SQLJ-specific mappings. See Table 12.1 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781555583293

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design