Skip Headers
Oracle® Database Lite Oracle Lite Client Guide
Release 10.3
Part Number E12548-02
Home
Book List
Index
Contact Us
Next
View PDF
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