Oracle9i Lite Developer's Guide for Windows 32
Release 5.0.2
Part No. A97672-01

Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Documentation Accessibility

1 Overview

1.1 Introduction

1.2 Oracle9i Lite Application Model and Architecture

1.2.1 Oracle Lite RDBMS

1.2.2 Mobile SQL (MSQL)

1.2.3 Mobile Sync

1.2.4 Mobile Server

1.3 Mobile Development Kit

1.4 Development Steps

1.4.1 Command Sequence

1.4.1.1 Step 1. Create TASK table on the server database

1.4.1.2 Step 2. Define a publication item and publish the application

1.4.1.3 Step 3. Create users and subscriptions

1.4.1.4 Step 4. Install the Oracle9i Lite client and the Mobile Field Service application and data

1.4.1.5 Step 5. Browse the TASK snapshot and update a row

1.4.1.6 Step 6. Synchronize the change with the server

1.4.1.7 Step 7. Check your changes on the server and modify a server record

1.4.1.8 Step 8. Synchronize again to get the server changes

1.4.1.9 Step 9. Develop your Mobile Field Service application using the Oracle Lite database

1.4.1.10 Step 10. Republish the application with the application program

2 The Oracle Lite RDBMS

2.1 Introduction

2.2 Development Interface

2.2.1 Development Interface Overview

2.2.1.3 Object Kernel API (OKAPI)

2.2.2 Mobile Sync Client Module Application Programming Interfaces (APIs)

2.2.3 Oracle Lite Load Utility (OLLOAD)

2.3 Using the Starter Database

2.4 Working with Your Database

2.4.1 Creating a New Database

2.4.2 Creating a Data Source Name with ODBC Administrator

2.4.3 Creating a New Database Using a Command-Line Utility

2.4.4 Connecting to a New Database

2.5 Creating Multiple Users

2.5.1 Pre-defined Roles

2.5.2 Creating Users

2.5.3 Dropping Users

2.5.4 Changing Passwords

2.5.5 Granting Roles

2.5.6 Granting Privileges

2.5.7 Revoking Roles

2.5.8 Revoking Privileges

2.5.9 Building Demo Tables

2.5.10 Populate Your Database Using Mobile SQL

2.5.11 Backing Up a Database

2.5.12 Encrypting and Decrypting a Database

2.6 Oracle Lite Database Transaction Support

2.6.1 Atomicity

2.6.2 Consistency

2.6.3 Isolation

2.6.3.1 Durability

2.6.3.2 Locking

2.6.3.3 Changing the Default Isolation Level

2.6.3.4 Supported Combinations of Isolation Levels and Cursor Types

2.6.4 Tuning the Application

2.7 Creating Snapshot Definitions

2.7.1 Creating a Snapshot Definition Declaratively

2.7.2 Creating the Snapshot Definition Programmatically

2.8 Using the Oracle Lite Samples

2.8.2 BLOB Manager Example Notes

2.8.3 Running the Visual Basic Sample Application

2.8.3.1 Open Visual Basic

2.8.3.2 View the Sample Application Tables and Data

2.8.3.3 Open the Sample Application

2.8.3.4 View and Manipulate the Data in the EMP Table

2.8.4 ODBC Examples

2.8.4.1 What the Examples Do

3 Synchronization

3.1.1 Synchronization Concepts

3.2 Synchronization Example

3.3 Synchronization Process

3.3.1 Fast Refresh Synchronization

3.3.1.1 Client Upload and Download Operations

3.3.1.2 Mobile Server Apply Operation

3.3.1.3 Mobile Server Compose Operation

3.3.2 Complete Refresh Synchronization

3.3.3 Synchronizing an Encrypted Database

3.4 Mobile Sync Application Programming Interfaces (APIs)

3.4.1 Java Interface

3.4.1.1 Sync Class

3.4.1.2 SyncException Class

3.4.1.3 SyncOption Class

3.4.1.4 Java Interface SyncParam Settings

3.4.1.5 Java Interface TransportParam Parameters

3.4.1.6 SyncProgressListener Interface

3.4.2 COM Interface

3.4.2.1 Features and Components

3.4.2.2 ISync Interface

3.4.2.3 ISyncOption Interface

3.4.2.4 COM Interface SyncParam Settings

3.4.2.5 COM Interface TransportParam Parameters

3.4.2.6 ISyncProgressListener Interface

3.4.3 C/C++ Interface

3.4.3.1 ocSessionInit

3.4.3.2 ocSessionTerm

3.4.3.3 ocSaveUserInfo

3.4.3.4 ocDoSynchronize

3.4.3.5 ocSetTableSyncFlag

3.4.3.6 C/C++ Data Structures

3.4.4 Selective Synchronization

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.6 Entering Database Information

4.7 Defining Snapshots For Replication

4.7.1 Creating New Snapshots

4.7.1.1 Creating Indexes for 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.8.6 Editing Applications

5 Consolidator

5.1 The Publish and Subscribe Model Using the Consolidator API

5.1.1 The Publish and Subscribe Model Step by Step

5.1.2 Comparing Consolidator Methods by Function

5.2 Using Consolidator to Define the Sample11.java Example

5.2.1 Sample11.java

5.2.2 Create Required Tables Using Standard JDBC

5.2.3 Connecting to Mobile Server

5.2.3.1 openConnection

5.2.4 Creating Publications

5.2.4.1 CreatePublication

5.2.5 Creating Publication Items

5.2.5.1 CreatePublicationItem

5.2.5.2 Defining Publication Items for Updatable Multi-table Views

5.2.5.3 Data Subsetting

5.2.6 Defining User Subscription Parameters to Publications

5.2.6.1 SetSubscriptionParameter

5.2.7 Creating Publication Item Indexes

5.2.7.1 CreatePublicationItemIndex

5.2.7.2 Define Client Indexes

5.2.8 Adding Publication Items to the Publication

5.2.8.1 AddPublicationItem

5.2.8.2 Defining Conflict Rules

5.2.8.3 Using Table Weight

5.2.9 Creating Users

5.2.9.1 createUser

5.2.10 Drop User

5.2.10.1 dropUser

5.2.11 Subscribing Users to a Publication

5.2.11.1 CreateSubscription

5.2.12 Instantiating the Subscription

5.2.12.1 InstantiateSubscription

5.3 Other Standard Consolidator Functionality

5.3.1 Client Device Database DDL Operations

5.3.2 Change Password

5.3.2.1 setPassword

5.3.3 Creating Sequences

5.3.3.1 CreateSequence

5.3.4 Partitioning Sequences for Clients

5.3.4.1 CreateSequencePartition

5.3.5 Remote Database Link Support

5.3.5.1 Publishing Synonyms for the Remote Object Using CreatePublicationItem

5.3.5.2 Creating a Dependency Hint

5.3.5.3 Remove a Dependency Hint

5.4 Advanced Features for Customizing Consolidator

5.4.1 Compose Phase Customization Using MyCompose

5.4.1.1 Extending MyCompose as a User Defined Sub-Class

5.4.1.2 Primary MyCompose Methods

5.4.1.3 Subsidiary MyCompose Methods

5.4.1.4 Consolidator API Methods for Registering MyCompose Sub-Classes

5.4.2 Sync Discovery API

5.4.2.1 getDownloadInfo Method

5.4.2.2 DownloadInfo Class Access Methods

5.4.2.3 PublicationSize Class

5.4.3 Map Table Partition APIs

5.4.3.1 Create a Map Table Partition

5.4.3.2 Add Map Table Partitions

5.4.3.3 Drop a Map Table Partition

5.4.3.4 Drop All Map Table Partitions

5.4.3.5 Merge Map Table Partitions

5.4.4 Modifying a Publication Item Using AlterPublicationItem

5.4.4.1 Alter Publication Item

5.4.5 Fast Refresh and Update Operation for Multi-Table Publications (Views)

5.4.5.1 Updatable Parent Tables

5.4.5.2 Using Parent Table Hints and INSTEAD OF Triggers

5.4.5.3 Fast Refresh for Views

5.4.5.4 Complete Refresh for Views

5.4.6 Virtual Primary Key

5.4.6.1 Create Virtual Primary Key Column

5.4.6.2 Drop Virtual Primary Key Column

5.4.7 Caching Publication Item Queries

5.4.7.1 Enabling Publication Item Query Caching

5.4.7.2 Disabling Publication Item Query Caching

5.4.8 Binding User-Defined PL/SQL Procedures

5.4.9 Queue Interface for Customizing Replication

5.4.9.1 Queue Interface Operation

5.4.9.2 Queue Creation

5.4.9.3 Queue Interface PL/SQL Procedure

5.4.9.4 CreateQueuePublicationItem API

5.4.9.5 Defining a PL/SQL Package Outside the Repository

5.4.10 Null Sync Callout

5.4.11 Foreign Key Constraints in Updatable Publication Items

5.4.11.1 Foreign Key Constraint Violation Example

5.4.11.2 Avoiding Constraint Violations with BeforeApply and After Apply

5.4.11.3 Avoiding Constraint Violations with Table Weights

5.4.12 Callback Customization for Before and After Compose/Apply

5.4.13 Callback Customization for DML Operations

5.4.13.1 DML Procedure Example

5.4.14 Restricting Predicate

5.5 Synchronization Errors and Conflicts

5.5.1 Versioning

5.5.2 Winning Rules

5.5.3 Resolving Conflicts Using the Error Queue

5.5.3.1 Execute Transaction

5.5.3.2 Purge Transaction

5.6 Mapping Datatypes Between the Oracle Server and Clients

5.6.1 Oracle Lite Datatypes

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.8 ReverseJoinOrder

A.2.9 SharedAddress

A.2.10 SuggestedSharedAddress

A.2.11 SQLCompatibility

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_PARTITIONS

B.1.8 ALL_SEQUENCES

B.1.9 ALL_SYNONYMS

B.1.10 ALL_TABLES

B.1.11 ALL_TAB_COLUMNS

B.1.12 ALL_TAB_COMMENTS

B.1.13 ALL_USERS

B.1.14 ALL_VIEWS

B.1.16 COLUMN_PRIVILEGES

B.1.17 DATABASE_PARAMETERS

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.4.1 Synchronizing with an Encrypted Oracle Lite Database

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.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.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.3 File Format

E.3.1 Header Format

E.3.2 Parameters

E.3.3 Data Format

E.3.3.1 CSV Format

E.3.3.2 FixedAscii Format

E.4 Limitations

Glossary

Index

Copyright © 2002 Oracle Corporation
All rights reserved