Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Send Us Your Comments

1 The Oracle Database Lite RDBMS

• 1.1 Oracle Lite Database Overview
• 1.2 Execution Models for Applications that Use the Oracle Lite Database
  • 1.2.1 Embedded Application in Single Process
  • 1.2.2 Mobile Option for a Client in a Single Process
  • 1.2.3 Multiple Processes Accessing the Same Database
  • 1.2.4 Multiple Embedded Application Clients Accessing Remote Database
  • 1.2.5 Multiple Clients Accessing Remote Database

2 System Requirements for the Oracle Lite Database as the Mobile Client

• 2.1 System Requirements for the Oracle Lite Database as the Mobile Client on Windows
  • 2.1.1 Hardware Requirements for the Oracle Lite Database as the Mobile Client on Windows
    • 2.1.1.1 What File System and External Memory Media Should You Use for Windows CE?
  • 2.1.2 Software Requirements for the Oracle Lite Database as the Mobile Client on Windows
    • 2.1.2.1 Certified Operating Systems and Other Software Requirements
    • 2.1.2.2 Supported and Certified Technologies for Windows Mobile Clients
    • 2.1.2.3 Supported Platforms for Oracle Database Lite WinCE
    • 2.1.2.4 Windows Mobile Client Notes
• 2.2 System Requirements for the Oracle Lite Database as the Mobile Client on Linux
  • 2.2.1 Certified Platforms and Supported Technologies for Linux Mobile Clients
  • 2.2.2 Software and Hardware Requirements for the Oracle Lite Database as the Mobile Client on Linux
  • 2.2.3 Setting Environment Variables Before Installing the Linux Mobile Client

3 Installing the Oracle Lite Database

• 3.1 Preparing the Device for a Mobile Application
• 3.2 Installing the Oracle Lite Mobile Client Software
  • 3.2.1 Installing Web-to-Go on Linux
  • 3.2.2 Installing Standard SDK WinCE 5.0 CAB Files for Your Mobile Client
    • 3.2.2.1 Defining the INI File
  • 3.2.3 Installing Tools CAB Files for Java, MSQL, and Utility Support
    • 3.2.3.1 Defining the Tools CAB as an Application in Packaging Wizard
    • 3.2.3.2 Assigning the Tools CAB to the User
• 3.3 Configuring for Default Sync When Installing the Client
• 3.4 Configuring the Client for Secure Socket Layer (SSL)
• 3.5 Specifying Whether the Client Uses a Static or Dynamic (DHCP) IP Address
• 3.6 Using Offline Instantiation to Distribute Multiple Mobile Clients

4 Building an Embedded Application

• 4.1 Creating the Oracle Lite Database
  • 4.1.1 Creating a Data Source Name with ODBC Administrator
    • 4.1.1.1 Creating DSN on a Windows System
    • 4.1.1.2 Creating DSN on a LINUX System
  • 4.1.2 Creating a New Oracle Lite Database
• 4.2 Creating Users for the Oracle Lite Database
  • 4.2.1 Pre-Defined Roles
  • 4.2.2 Building and Populating Demo Tables
• 4.3 Packaging Your Embedded Application With the Oracle Database Lite Runtime
  • 4.3.1 Packaging an Embedded Application on Windows
  • 4.3.2 Packaging an Embedded Application on Linux
• 4.4 Connecting to the Oracle Lite Database
• 4.5 Using Oracle Database Lite Samples
  • 4.5.1 Executing the Visual Basic Sample Application
    • 4.5.1.1 Open the Sample Application
    • 4.5.1.2 View and Manipulate the Data in the EMP Table

5 Building a Client/Server Environment

• 5.1 Overview of the Multi-User Service
• 5.2 Administration for the Multi-User Service on the Windows Platform
  • 5.2.1 Installation and Configuration on Windows
  • 5.2.2 Starting the Multi-User Service on Windows
  • 5.2.3 Stopping the Multi-User Service on Windows
  • 5.2.4 Querying the Multi-User Service on Windows
• 5.3 Administration for the Multi-User Service on the Linux Platform
  • 5.3.1 Starting and Stopping the Multi-User Service on Linux
  • 5.3.2 Querying the Multi-User Service on Linux
• 5.4 Debugging the Multi-User Service
• 5.5 Creating DSNs
• 5.6 Accessing the Database
• 5.7 Verifying the Connection Using mSQL

6 Managing Your Oracle Lite Mobile Client

• 6.1 Start the Mobile Client
• 6.2 Log on to Mobile Client Workspace
• 6.3 Synchronize or Execute Applications on the Mobile Client
• 6.4 Manage the Mobile Client
  • 6.4.1 Manage Your Clients Locally With the Mobile Client Workspace
    • 6.4.1.1 Instructions for Using the Mobile Client Workspace
    • 6.4.1.2 Execute Mobile Applications Installed on Your Mobile Client
    • 6.4.1.3 Customize the Mobile Client Workspace
    • 6.4.1.4 Schedule Data Synchronization Jobs
  • 6.4.2 Use the mSync GUI to Initiate Synchronization of Your Linux, WinCE, and Win32 Applications
    • 6.4.2.1 Network Options for MSync Tool
    • 6.4.2.2 Sync Options for MSync Tool
    • 6.4.2.3 Set User Context for Member
    • 6.4.2.4 Sync to a File Using File-Based Sync
    • 6.4.2.5 Use Mobile Client Tools on Linux
  • 6.4.3 Reset the Mobile User Password
  • 6.4.4 Use the Device Manager Client GUI to Manage the Client-Side Device
  • 6.4.5 Initiate Updates for the Oracle Lite Client
  • 6.4.6 Configure JAVA_HOME for Web-to-Go Clients
  • 6.4.7 Defragmentation and Reducing Size of the Client Application Databases
  • 6.4.8 Communicate Between the Internet and Intranet Through a Reverse Proxy

7 Managing the Oracle Lite Database

• 7.1 Moving Your Client Data Between an Oracle Lite Database and an External File
  • 7.1.1 Move Data Between an Oracle Lite Database and an External File Using Programmatic APIs
  • 7.1.2 Oracle Database Lite Load Utility (OLLOAD)
• 7.2 Backing Up an Oracle Lite Database
• 7.3 Encrypting a Database
• 7.4 Support for Linguistic Sort
  • 7.4.1 Creating Linguistic Sort Enabled Databases
  • 7.4.2 How Collation Works
  • 7.4.3 Collation Element Examples
    • 7.4.3.1 Sorting Normal Characters
    • 7.4.3.2 Reverse Sorting of French Accents
    • 7.4.3.3 Sorting Contracting Characters
    • 7.4.3.4 Sorting Expanding Characters
    • 7.4.3.5 Sorting Numeric Characters
• 7.5 Discovering Oracle Lite Database Version Number
• 7.6 Row Sort Limitations of the Oracle Lite Database
• 7.7 Troubleshooting the Source of a Checksum Error Against Database
• 7.8 Enable Tracing for the Oracle Lite Database
  • 7.8.1 Enabling Trace Output
  • 7.8.2 Description of Trace Information
    • 7.8.2.1 Table Name Output

8 Oracle Database Lite Data Access APIs

• 8.1 ODBC
• 8.2 JDBC
• 8.3 ADO.NET
• 8.4 SODA

9 ODBC Drivers

• 9.1 Supported ODBC Drivers for Oracle Database Lite
• 9.2 Executing the ODBC Examples
  • 9.2.1 ODBCTBL
  • 9.2.2 ODBCVIEW
  • 9.2.3 ODBCFUNC
  • 9.2.4 ODBCTYPE
  • 9.2.5 LONG

10 JDBC Programming

• 10.1 JDBC Compliance
• 10.2 JDBC Environment Setup
• 10.3 JDBC Drivers to Use When Connecting to the Oracle Lite Database
  • 10.3.1 Type 2 Driver
  • 10.3.2 Type 4 (Pure Java) Driver Connection URL Syntax
• 10.4 DataSource Connection
• 10.5 Java Datatypes and JDBC Extensions
  • 10.5.1 Mapping Datatypes Between Java and Oracle
  • 10.5.2 Datatype Extensions
  • 10.5.3 Data Access Extensions
    • 10.5.3.1 Reading from a BLOB Sample Program
    • 10.5.3.2 Writing to a CLOB Sample Program
• 10.6 Limitations
• 10.7 New JDBC 2.0 Features
  • 10.7.1 Interface Connection
    • 10.7.1.1 Methods
  • 10.7.2 Interface Statement
  • 10.7.3 Interface ResultSet
    • 10.7.3.1 Fields
    • 10.7.3.2 Methods
    • 10.7.3.3 Methods that Return False
  • 10.7.4 Interface Database MetaData
    • 10.7.4.1 Methods
    • 10.7.4.2 Methods that Return False
  • 10.7.5 Interface ResultMetaData
    • 10.7.5.1 Methods
  • 10.7.6 Interface PreparedStatement
    • 10.7.6.1 Methods
• 10.8 J2ME Support
  • 10.8.1 JDBC Drivers for J2ME CDC and CLDC
    • 10.8.1.1 JDBC Driver for J2ME CDC
    • 10.8.1.2 JDBC Driver for J2ME CLDC
  • 10.8.2 J2ME Support for Windows CE
    • 10.8.2.1 Using IBM J9
    • 10.8.2.2 Using Creme 4.1

11 Oracle Database Lite ADO.NET Provider

• 11.1 Discussion of the Classes That Support the ADO.NET Provider
  • 11.1.1 Establish Connections With the OracleConnection Class
  • 11.1.2 Transaction Management
  • 11.1.3 Create Commands With the OracleCommand Class
  • 11.1.4 Maximize Performance Using Prepared Statements With the OracleParameter Class
    • 11.1.4.1 SQL String Parameter Syntax
  • 11.1.5 Large Object Support With the OracleBlob Class
    • 11.1.5.1 Using BLOB Objects in Parameterized SQL Statements
    • 11.1.5.2 Query Tables With BLOB Columns
    • 11.1.5.3 Read and Write Data to BLOB Objects
• 11.2 Limitations for the ADO.NET Provider
  • 11.2.1 Partial Data Returned with GetSchemaTable
  • 11.2.2 Creating Multiple DataReader Objects Can Invalidate Each Other
  • 11.2.3 Calling DataReader.GetString Twice Results in a DbNull Object
  • 11.2.4 Thread Safety

12 Using Simple Object Data Access (SODA)

• 12.1 Getting Started With SODA
  • 12.1.1 Overview of the SODA Classes
  • 12.1.2 Demonstrating Frequently-Used SODA Classes
• 12.2 Using SQL Queries in SODA Code for PocketPC Platforms
• 12.3 Virtual Columns and Object-Relational Mapping
• 12.4 Behavior of Reference-Counted and Copy-By-Assignment Objects
• 12.5 Another Library for Exceptions (ALE)
  • 12.5.1 Decorating Classes With ALE
  • 12.5.2 New Operator and ALE
  • 12.5.3 Global Variables
  • 12.5.4 Exceptions and Inheritance
  • 12.5.5 Using ALE with PocketPC ARM Compilers
  • 12.5.6 Troubleshooting ALE Runtime Errors
  • 12.5.7 Compiling Your Program With ALE
  • 12.5.8 ALE Code on Systems That Support Exceptions

13 Using Stored Procedures and Triggers

• 13.1 Overview of Stored Procedures and Triggers
• 13.2 Using Java Stored Procedures in Oracle Database Lite
  • 13.2.1 Load and Define Java Stored Procedures in an Oracle Lite Database
• 13.3 Creating Java Stored Procedures
  • 13.3.1 Using Load and Define for Java Stored Procedures
    • 13.3.1.1 Loading Java Stored Procedure Classes Into the Oracle Lite Database
    • 13.3.1.2 Defining Stored Procedures to SQL Using Create Function or Create Procedure
    • 13.3.1.3 Calling Defined Stored Procedures
    • 13.3.1.4 Dropping Defined Stored Procedures
    • 13.3.1.5 Example Using the Load and Define Model
  • 13.3.2 Using Attach to Define the Java Stored Procedure
    • 13.3.2.1 Attaching a Java Class to a Table
    • 13.3.2.2 Table-Level Stored Procedures
    • 13.3.2.3 Row-Level Stored Procedures
    • 13.3.2.4 Calling Attached Stored Procedures
    • 13.3.2.5 Dropping Attached Stored Procedures
    • 13.3.2.6 Example of An Attached Java Stored Procedure
  • 13.3.3 Calling Java Stored Procedures From a Multithreaded C or C++ Application
• 13.4 Using Triggers With Java Stored Procedures
  • 13.4.1 Statement-Level vs. Row-Level Triggers
  • 13.4.2 Creating Triggers
    • 13.4.2.1 Enabling and Disabling Triggers
  • 13.4.3 Dropping Triggers
  • 13.4.4 Trigger Example Using the Attach Method
  • 13.4.5 Trigger Arguments
  • 13.4.6 Trigger Arguments Example Using Create Procedure
• 13.5 Tutorial for a Java Stored Procedure Invoked By a Trigger
  • 13.5.1 Start mSQL
  • 13.5.2 Create a Table
  • 13.5.3 Create a Java Class
  • 13.5.4 Load the Java Class File
  • 13.5.5 Define the Stored Procedure
  • 13.5.6 Populate the Database
  • 13.5.7 Execute the Procedure
  • 13.5.8 Verify the Email Address
  • 13.5.9 Create a Trigger
    • 13.5.9.1 Testing the Trigger
    • 13.5.9.2 Verify the Email Address
  • 13.5.10 Commit or Roll Back
• 13.6 Converting Datatypes Between Java and SQL For Stored Procedures
  • 13.6.1 Declaring Parameters for Java Stored Procedures
  • 13.6.2 Using Stored Procedures to Return Multiple Rows
    • 13.6.2.1 Returning Multiple Rows in ODBC
    • 13.6.2.2 Example
• 13.7 Executing Java Stored Procedures from JDBC
  • 13.7.1 Using the executeQuery Method
  • 13.7.2 Using a Callable Statement
• 13.8 Using C++ Stored Procedures
  • 13.8.1 Creating C++ Stored Procedures
    • 13.8.1.1 C++ Stored Procedure Include File and Procedure Definition
    • 13.8.1.2 Access SODA Objects Within Your C++ Stored Procedure
  • 13.8.2 Building Your C++ Stored Procedures
    • 13.8.2.1 Linking in Appropriate Libraries
    • 13.8.2.2 Automatically Build Your Stored Procedure
    • 13.8.2.3 Manually Building Your Stored Procedure
  • 13.8.3 Define Your C++ Stored Procedure
  • 13.8.4 C++ Stored Procedure Example
    • 13.8.4.1 C++ Stored Procedure and Trigger Example One
    • 13.8.4.2 C++ Stored Procedure and Trigger Example Two
    • 13.8.4.3 JDBC Calling a C++ Stored Procedure Example
• 13.9 Using .Net Stored Procedures
  • 13.9.1 Creating the .Net Source for Your Stored Procedure
    • 13.9.1.1 Defining Methods, Imports and Namespace
    • 13.9.1.2 Access and Modify Database Using .Net Extension Classes In Stored Procedures
    • 13.9.1.3 Access and Modify Database Using OracleSPManager Inside Triggers
  • 13.9.2 Building Your .Net Stored Procedures
  • 13.9.3 Define Your .Net Stored Procedure
    • 13.9.3.1 Create the .Net Class Object in the Oracle Lite Database
    • 13.9.3.2 Define Methods With a Call Specification
  • 13.9.4 Dropping .Net Stored Procedures
  • 13.9.5 .Net Stored Procedure Example
    • 13.9.5.1 .Net Stored Procedure and Trigger Example One
    • 13.9.5.2 .Net Stored Procedure and Trigger Example Two
• 13.10 Loading and Defining C, C++ or C# Stored Procedures

14 Configure Security for the Oracle Lite Database

• 14.1 Providing Security for the Mobile Client
• 14.2 Encrypting the Oracle Lite Database
  • 14.2.1 Configuring for Automatic Encryption of the Oracle Lite Database
  • 14.2.2 Create a Command to Initiate Automatic Encryption of the Oracle Lite Database
  • 14.2.3 Execute EncrypDB Command to Encrypt Database
• 14.3 Providing Your Own Encryption Module for the Client Oracle Lite Database
  • 14.3.1 Encryption Module APIs
    • 14.3.1.1 Initialize the Encryption Module
    • 14.3.1.2 Delete Encryption Context
    • 14.3.1.3 Create the Encryption Key
    • 14.3.1.4 Encrypt Data
    • 14.3.1.5 Decrypt Data
  • 14.3.2 Plug-In Custom Encryption Module
• 14.4 Pre-Configure Branch Office Passwords

15 Oracle Database Lite Transaction Support

• 15.1 Locking
• 15.2 What Are the Transaction Isolation Levels?
• 15.3 Configuring the Isolation Level
• 15.4 Supported Combinations of Isolation Levels and Cursor Types

16 Improving SQL Query Performance for the Oracle Lite Database

• 16.1 Determining Performance of Client SQL Queries With the EXPLAIN PLAN
• 16.2 Determine SQL Query Execution Through Oracle Database Lite Tracing
• 16.3 Optimizing SQL Queries for the Oracle Lite Database
  • 16.3.1 Optimizing Single-Table Queries
  • 16.3.2 Optimizing Join Queries
    • 16.3.2.1 Create an Index on the Join Column(s) of the Inner Table
    • 16.3.2.2 Bypassing the Query Optimizer
  • 16.3.3 Optimizing with Order By and Group By Clauses
    • 16.3.3.1 IN Subquery Conversion
    • 16.3.3.2 ORDER BY Optimization with No GROUP BY
    • 16.3.3.3 GROUP BY Optimization with No ORDER BY
    • 16.3.3.4 ORDER BY Optimization with GROUP BY
    • 16.3.3.5 Cache Subquery Results
  • 16.3.4 Advanced Optimization Techniques for SQL Queries in Oracle Database Lite
    • 16.3.4.1 Oracle Lite Database Application Architecture
    • 16.3.4.2 Overview of SQL Runtime
    • 16.3.4.3 Execution Plan Generation
    • 16.3.4.4 Query Execution Engine
    • 16.3.4.5 Optimization Tips
    • 16.3.4.6 Glossary
    • 16.3.4.7 References

A POLITE.INI Parameters for the Oracle Lite Database

• A.1 POLITE.INI File Overview
• A.2 All Databases Section
  • A.2.1 CACHE_SIZE
  • A.2.2 DATA_DIRECTORY
  • A.2.3 DATABASE_ID
  • A.2.4 DB_CHAR_ENCODING
  • A.2.5 EXTERNAL_ENCRYPTION_DLL
  • A.2.6 FLUSH_AFTER_WRITE
  • A.2.7 MAX_INDEX_COLUMNS
  • A.2.8 MAX_ROWS
  • A.2.9 MESSAGE_FILE
  • A.2.10 NLS_DATE_FORMAT
    • A.2.10.1 Date Format
    • A.2.10.2 Date Format Examples
  • A.2.11 NLS_LOCALE
  • A.2.12 NLS_SORT
  • A.2.13 OLITE_SERVER_LOG
  • A.2.14 OLITE_SERVER_TRACE
  • A.2.15 OLITE_SQL_TRACE
  • A.2.16 OLITE_WRITE_VERIFY
  • A.2.17 OLITE_READ_VERIFY
  • A.2.18 SQLCOMPATIBILITY
  • A.2.19 TEMP_DB
  • A.2.20 TEMP_DIR
  • A.2.21 SERVICE_PORT
  • A.2.22 SERVICE_WDIR
  • A.2.23 PAGE_FILL_FACTOR
• A.3 Sample POLITE.INI File

B Catalog Views for the Oracle Lite Client

• B.1 ALL_COL_COMMENTS
• B.2 ALL_CONSTRAINTS
• B.3 ALL_CONS_COLUMNS
• B.4 ALL_DEPENDENCIES
• B.5 ALL_INDEXES
• B.6 ALL_IND_COLUMNS
• B.7 ALL_OBJECTS
• B.8 ALL_PRIVILEGES
• B.9 ALL_SEQUENCES
• B.10 ALL_SYNONYMS
• B.11 ALL_TABLES
• B.12 ALL_TAB_COLUMNS
• B.13 ALL_TAB_COMMENTS
• B.14 ALL_USERS
• B.15 ALL_VIEWS
• B.16 POL__ALLOBJ
• B.17 POL__COLUSAGE
• B.18 POL__COMMENT
• B.19 POL__CONS
• B.20 POL__DATABASE_PARAMETERS
• B.21 POL__INDICES
• B.22 POL__INDICESDT
• B.23 POL__PROCEDURES
• B.24 POL__PROCEDURE_COLUMNS
• B.25 POL__SCHEMATA
• B.26 POL__SEQ
• B.27 POL__SYNONYM
• B.28 POL__TBLCONS
• B.29 POL__TBLUSAGE
• B.30 POL__TRIGGERS
• B.31 POL__VIEWS
• B.32 POL__USERS

C Oracle Lite Database Utilities

• C.1 The mSQL Tool
  • C.1.1 The mSQL Tool for Windows 32
    • C.1.1.1 Starting mSQL
    • C.1.1.2 Populating your Database Using mSQL
    • C.1.1.3 SET TERM {ON|OFF}
    • C.1.1.4 SET TIMING {ON|OFF}
    • C.1.1.5 SET VERIFY {ON|OFF}
    • C.1.1.6 SET AUTO {ON|OFF}
    • C.1.1.7 DESC <table_name>
    • C.1.1.8 DIR
  • C.1.2 The mSQL Tool for Windows CE
    • C.1.2.1 The mSQL GUI Tool
    • C.1.2.2 Manage Snapshots Using mSQL
• C.2 CREATEDB
• C.3 REMOVEDB
• C.4 ENCRYPDB
• C.5 DECRYPDB
• C.6 BACKUPDB
• C.7 DefragDB to Defragment and Reduce Size of the Oracle Lite Database
  • C.7.1 Execute DefragDB
  • C.7.2 Pause or Cancel Defragmentation
  • C.7.3 Execute DefragDB With Command-Line
• C.8 ODBC Administrator and the Oracle Database Lite ODBC Driver
  • C.8.1 Adding a DSN Using the ODBC Administrator
  • C.8.2 Adding a DSN which points to Read-Only Media (CD-ROM)
• C.9 ODBINFO
• C.10 VALIDATEDB
• C.11 Transferring Data Between a Database and an External File
  • C.11.1 OLLOAD
    • C.11.1.1 Syntax
    • C.11.1.2 Keywords and Parameters
  • C.11.2 Oracle Database Lite Load Application Programming Interfaces (APIs)
    • C.11.2.1 Overview
    • C.11.2.2 Oracle Database Lite Load APIs
    • C.11.2.3 File Format
    • C.11.2.4 Limitations

Index