Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

1 Overview

1.1 Introduction

1.1.1 Oracle Lite DBMS

1.1.2 Mobile Sync

1.1.3 Mobile Server

1.1.4 Mobile SQL

1.1.5 Samples

1.2 Development Interface

1.2.1 Development Interface Overview

1.2.1.1 JDBC

1.2.1.2 ODBC

1.2.1.3 Object Kernel API (OKAPI)

1.2.2 Mobile Sync API

1.2.3 Oracle Lite Loading Utility (OLLOAD)

1.3 Using the Starter Database

1.4 Working with Your Database

1.4.1 Creating a New Database

1.4.2 Creating a Data Source Name with ODBC Administrator

1.4.3 Creating a New Database Using a Command-Line Utility

1.4.4 Connecting to a New Database

1.5 Creating Multiple Users

1.5.1 Pre-defined Roles

1.5.2 Creating Users

1.5.3 Dropping Users

1.5.4 Changing Passwords

1.5.5 Granting Roles

1.5.6 Granting Privileges

1.5.7 Revoking Roles

1.5.8 Revoking Privileges

1.5.9 Building Demo Tables

1.5.10 Populate Your Database Using Mobile SQL

1.5.11 Backing Up a Database

1.5.12 Encrypting and Decrypting a Database

1.6 Oracle Lite Database Transaction Support

1.6.1 Atomicity

1.6.2 Consistency

1.6.3 Isolation

1.6.3.1 Durability

1.6.3.2 Locking

1.6.3.3 Changing the Default Isolation Level

1.6.3.4 Supported Combinations of Isolation Levels and Cursor Types

1.6.4 Tuning the Application

1.7 Creating Snapshot Definitions

1.7.1 Creating a Snapshot Definition Declaratively

1.7.2 Creating the Snapshot Definition Programmatically

2 Using the Oracle Lite Samples

2.1 Overview

2.2 BLOB Manager Example Notes

2.3 Running the Visual Basic Sample Application

2.3.1 Open Visual Basic

2.3.2 View the Sample Application Tables and Data

2.3.3 Open the Sample Application

2.3.4 View and Manipulate the Data in the EMP Table

2.4 ODBC Examples

2.4.1 What the Examples Do

2.4.1.1 odbctbl

2.4.1.2 odbcview

2.4.1.3 odbcfunc

2.4.1.4 odbctype

2.4.1.5 long

3 Synchronization

3.1 Overview

3.1.1 Publication and Subscription

3.1.2 Client Device Database DDL Operations

3.1.3 Database Support

3.1.4 Binding User-Defined PL/SQL Packages

3.1.5 Special Characters Allowed in Database Base Objects

3.1.6 Fast Refresh and Update Operation for Views

3.1.6.1 Updatable Parent Tables

3.1.6.2 Using Parent Table Hints and INSTEAD OF Triggers

3.1.6.3 Fast Refresh for Views

3.1.6.4 Complete Refresh for Views

3.1.7 Foreign Key Constraints in Updatable Publication Items

3.1.7.1 Foreign Key Constraint Violation Example

3.1.7.2 Avoiding Constraint Violations with BeforeApply and After Apply

3.1.7.3 Avoiding Constraint Violations with Table Weights

3.1.8 Replication Errors and Conflicts

3.1.8.1 Versioning

3.1.8.2 Winning Rules

3.2 Mapping Datatypes Between the Oracle Server and Clients

3.2.1 Oracle Lite Datatypes

3.3 The Publish and Subscribe Model

3.3.1 API Reference

3.3.2 Connecting to Mobile Server

3.3.3 User Functions of the Resource Manager Class

3.3.3.1 Create User

3.3.3.2 Change Password

3.3.3.3 Drop User

3.3.4 Creating Publications

3.3.4.1 Defining Publication Items

3.3.4.2 Data Subsetting

3.3.5 Creating Publication Items

3.3.5.1 Null Sync Callout

3.3.6 Retrieving Publication Item Names

3.3.7 Creating Publication Item Indexes

3.3.7.1 Define Client Indexes

3.3.8 Adding Publication Items to the Publication

3.3.8.1 Read-only Publication Items

3.3.8.2 Defining Conflict Rules

3.3.8.3 Using Table Weight

3.3.9 Subscribing Users to a Publication

3.3.10 Creating Sequences

3.3.11 Partitioning Sequences for Clients

3.3.12 Defining Client Subscription Parameters to Publications

3.3.13 Instantiating the Subscription

3.3.14 Schema Evolution Using Alter Publication Item

3.3.14.1 Alter Publication Item

3.4 Publish and Subscribe Method Specialized Functions

3.4.1 Caching Publication Item Queries

3.4.1.1 Enabling Publication Item Query Caching

3.4.1.2 Disabling Publication Item Query Caching

3.4.2 Selective Synchronization

3.4.3 Callback Customization Using Compose and Apply

3.4.4 Defining Customized DML Operations

3.4.4.1 PL/SQL Code Example

3.4.5 Virtual Primary Key

3.4.5.1 Create Virtual Primary Key Column

3.4.5.2 Drop Virtual Primary Key Column

3.4.6 Restricting Predicate

3.4.7 Resolving Conflicts Using the Error Queue

3.4.7.1 Execute Transaction

3.4.7.2 Purge Transaction

3.5 Setting Up the Mobile Sync for Windows

3.5.1 Configuring Transport

3.5.2 Testing Synchronization

3.6 Programming with the OCAPI.DLL

3.6.1 Mobile Sync COM API

3.6.1.1 Features and Components

3.6.1.2 ISync Interface

3.6.1.3 ISyncOption Interface

3.6.1.4 ISyncProgressListener Interface

3.6.2 Mobile Sync API

3.6.2.1 ocSessionInit

3.6.2.2 ocSessionTerm

3.6.2.3 ocSaveUserInfo

3.6.2.4 ocDoSynchronize

3.6.2.5 ocSetTableSyncFlag

3.6.3 Mobile Sync API Data Structures

3.6.3.1 ocEnv

3.7 Mobile Server System Catalog Views

4 Using the Packaging Wizard

4.1 Packaging Wizard Overview

4.2 Starting the Packaging Wizard

4.3 Platform Selection

4.4 Naming New Applications

4.4.1 Locating Platform Files

4.5 Listing Application Files

4.5.1 Sorting

4.5.2 Filters

4.6 Entering Database Information

4.7 Defining Snapshots For Replication

4.7.1 Creating New Snapshots

4.7.2 Importing Snapshots

4.7.3 Editing Snapshots

4.8 Completing the Application

4.8.1 The Application File

4.8.2 Creating JAR Files

4.8.3 Creating SQL Files

4.8.4 Restarting the Packaging Wizard

4.8.5 Publishing the Application

4.9 Editing Applications

A POLITE.INI Database Parameters

A.1 POLITE.INI File Overview

A.2 POLITE.INI Parameters

A.2.1 CacheSize

A.2.2 DatabaseID

A.2.3 DbCharEncoding

A.2.4 MAXINDEXCOLUMNS

A.2.5 NLS_Date_Format

A.2.5.1 Date Format

A.2.5.2 Date Format Examples

A.2.6 NLS_Locale

A.2.7 NLS_Sort

A.2.8 ReverseJoinOrder

A.2.9 SharedAddress

A.2.10 SuggestedSharedAddress

A.2.11 SQLCompatibility

A.2.12 TempDB

A.2.13 TempDir

A.3 Sample POLITE.INI File

B System Catalog Views

B.1 Oracle Lite Database Catalog Views

B.1.1 ALL_COL_COMMENTS

B.1.2 ALL_CONSTRAINTS

B.1.3 ALL_CONS_COLUMNS

B.1.4 ALL_INDEXES

B.1.5 ALL_IND_COLUMNS

B.1.6 ALL_OBJECTS

B.1.7 ALL_SEQUENCES

B.1.8 ALL_SYNONYMS

B.1.9 ALL_TABLES

B.1.10 ALL_TAB_COLUMNS

B.1.11 ALL_TAB_COMMENTS

B.1.12 ALL_USERS

B.1.13 ALL_VIEWS

B.1.14 CAT

B.1.15 COLUMN_PRIVILEGES

B.1.16 DATABASE_PARAMETERS

B.1.17 DUAL

B.1.18 SNAPSHOTS

B.1.19 TABLE_PRIVILEGES

B.1.20 USER_OBJECTS

C Database Tools and Utilities

C.1 Support for Linguistic Sort

C.1.1 Creating Linguistic Sort Enabled Databases

C.1.2 How Collation Works

C.1.3 Collation Element Examples

C.1.3.1 Sorting of Normal Characters

C.1.3.2 Reverse Sorting of French Accents

C.1.3.3 Sorting of Contracting Characters

C.1.3.4 Sorting of Expanding Characters

C.1.3.5 Sorting of Numeric Characters

C.2 CREATEDB

C.3 DECRYPDB

C.4 ENCRYPDB

C.5 MIGRATE

C.6 Mobile SQL

C.6.1 Database Access

C.6.2 Starting Mobile SQL

C.7 ODBC Administrator and the Oracle Lite ODBC Driver

C.7.1 Adding a DSN Using the ODBC Administrator

C.7.2 Adding a DSN which Points to Read-Only Media (CD-ROM)

C.8 ODBINFO

C.9 OLLOAD

C.10 REMOVEDB

C.11 VALIDATEDB

D Optimizing SQL Queries

D.1 Optimizing Single-Table Queries

D.2 Optimizing Join Queries

D.2.1 Create an Index on the Join Column(s) of the Inner Table

D.2.2 Bypassing the Query Optimizer

D.3 Optimizing with Order By and Group By Clauses

D.3.1 IN subquery conversion

D.3.2 ORDER BY optimization with no GROUP BY

D.3.3 GROUP BY optimization with no ORDER BY

D.3.4 ORDER BY optimization with GROUP BY

D.3.5 Cache subquery results

E Oracle Lite Loading Utility API

E.1 Overview

E.2 Oracle Lite Load Utility APIs

E.2.1 Connecting to the Database: olConnect

E.2.2 Disconnecting from the Database: olDisconnect

E.2.3 Deleting All Rows from a Table: olTruncate

E.2.4 Setting Parameters for Load and Dump Operations: olSet

E.2.5 Loading Data: olLoad

E.2.6 Dumping Data: olDump

E.2.7 Compiling

E.2.8 Linking

E.2.9 Parameters

E.3 File Format

E.3.1 Header Format

E.3.2 Data Format

E.3.2.1 CSV Format

E.3.2.2 FixedAscii Format

E.4 Limitations

Glossary

Index