Skip Headers

Oracle® Database Lite Developer's Guide Release 10.3 Part Number E12090-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 3 APIs for Client and Database Administration

The Mobile client database contains a subset of data stored in the Oracle database., which are synchronized with the Oracle database.

The following sections describe how you can programmatically configure the features for the back-end Oracle database.

- Section 3.1, "Deleting a Client Device"

- Section 3.2, "Register a Remote Oracle Database for Application Data"

- Section 3.3, "Create a Synonym for Remote Database Link Support For a Publication Item"

- Section 3.4, "Parent Tables Needed for Updateable Views"

- Section 3.5, "Manipulating Application Tables"

- Section 3.6, "Facilitating Schema Evolution"

- Section 3.7, "Set DBA or Operational Privileges for the Mobile Server"

- Section 3.8, "Datatype Conversion Between the Oracle Server and the Oracle Lite Database"

## 3.1 Deleting a Client Device

If you want to delete a device, use the `delete` method from the `Device` class. To retrieve the `Device` object, use either the `getDevice` or `getDeviceByName` methods, as demonstrated below.

If the device id is available, the following can be directly used:

    if (oracle.lite.resource.ResourceManager.getInstance() == null)
    oracle.lite.resource.ResourceManager.initialize(JDBC_URL, USER, PASSWORD);

    oracle.lite.resource.Device d =
     oracle.lite.resource.ResourceManager.getInstance().getDevice(deviceId);

    d.delete();

If the device id is not available, then you can provide the device name, which is shown on the Mobile Manager UI in the `oracle.lite.resource.User.getDeviceByName(deviceName)` method. Once retrieved, use the delete method of the Device object as demonstrated above.

## 3.2 Register a Remote Oracle Database for Application Data

By default, the Mobile repository metadata and the application schemas are present in the same database. However, it is possible to place the application schemas in a database other than the MAIN database where the Mobile repository exists. This can be an advantage from a performance or administrative viewpoint.

Thus, you can spread your application data across multiple databases.

Note:

We refer to the database where the application schema resides as remote because it is separate from the MAIN database that contains the Mobile repository. It does not mean that the database is geographically remote. It can be local or remote. For performance reasons, the Mobile Server must have connectivity to all databases involved in the synchronization—MAIN and remote.

This section describes how to register a remote Oracle database containing application schemas, using the `ConsolidatorManager` APIs. However, it is recommended that you use the Oracle Database Lite GUI tools for this task unless you have a specific need to use the API. For concepts and description of how to perform this with the Oracle Database Lite GUI tools, see Section 6.6, "Register or Deregister an Oracle Database for Application Data" in the Oracle Database Lite Administration and Deployment Guide.

To use an Oracle database other than the Oracle database used for the Mobile repository, perform the following:

1. Use the `apprepwizard` script to setup a remote application repository. See Section 3.2.1, "Set up a Remote Application Repository With the APPREPWIZARD Script" for details.

2. Register the Oracle database as described in Section 3.2.2, "Register or Deregister a Remote Oracle Database for Application Data".

3. When creating the publication and publication items, specify the name of the registered Oracle database that contains the application schemas. All data for a single application—that is, all publication items for the publication—must be contained in the same Oracle database.

### 3.2.1 Set up a Remote Application Repository With the APPREPWIZARD Script

Use the `apprepwizard` script to setup a remote application repository. This script creates and initializes an administrator schema with the same name as the adminstrator schema in the Main database. For example, if the administrator schema name in the Main database is `mobileadmin`, then the `apprepwizard` script will create a `mobileadmin` schema on the remote database.

The `apprepwizard` script is located in the `ORACLE_HOME``/Mobile/Server/admin`. The usage of this script is as follows:

    apprepwizard.bat <MAIN_Repository_Schema_Name> <MAIN_Repository_Schema_Password>
     <Application_Database_Administrator_User_Name>
     <Application_Database_Administrator_Password>
     <Application_Database_JDBC_URL> <Application_Database_Schema_Password>
     [<DB_name>]

Where each parameter is as follows:

- `MAIN_Repository_Schema_Name`: Provide the Mobile repository schema name, which exists on the Main database. The default is `MOBILEADMIN`.

- `MAIN_Repository_Schema_Password`: Provide the password for the Mobile repository administrator schema.

- `Application_Database_Administrator_User_Name`: Any user with administrator privileges at the application database. such as `SYSTEM`.

- `Application_Database_Administrator_Password`: Password of the administrator user for the application database.

- `Application_Database_JDBC_URL`: JDBC URL of the application database.

- `Application_Database_Schema_Password`: Password of the schema, which will be created at the application database. The username is the same as the Mobile repository schema name.

- `DB_Name`: Optionally, the user can provide a name to identify this database. This name is used in logging. By default, the log is sent to the console. If this name is provided as the last parameter, then the log is generated in the By default, the log is sent to the console. If the database name is provided as the last parameter, then the log is generated in the `ORACLE_HOME``/Mobile/Server/``<DB_NAME>``/apprepository.log` file.

This script installs silently. Thus, If you execute this script without any arguments, nothing is performed.

### 3.2.2 Register or Deregister a Remote Oracle Database for Application Data

Use the following `ConsolidatorManager` APIs to register, deregister, or alter the properties of the remote Oracle database:

    void registerDatabase(String name, Consolidator.DBProps props)
    void deRegisterDatabase(String name)
    void alterDatabase(String name, Consolidator.DBProps props)

Where:

- Name—An identifying name for the database where the application schema resides. Once defined, this name cannot be modified. This name must be unique across all registered database names.

- DBProps—A class that contains the JDBC URL, password and description, as follows:

      public static class DBProps {
       public String jdbcUrl;
       public String adminPassword;
       public String description;
      }

  - JDBC URL—The JDBC URL can be one of the following formats:

    * The URL for a single Oracle database has the following structure: `jdbc:oracle:thin:@<host>:<port>:<SID>`

    * The JDBC URL for an Oracle RAC database can have more than one address in it for multiple Oracle databases in the cluster and follows this URL structure:

        jdbc:oracle:thin:@(DESCRIPTION=
         (ADDRESS_LIST=
           (ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARY_NODE_HOSTNAME)(PORT=1521))
           (ADDRESS=(PROTOCOL=TCP)(HOST=SECONDARY_NODE_HOSTNAME)(PORT=1521))
         )
         (CONNECT_DATA=(SERVICE_NAME=DATABASE_SERVICENAME)))

  - Password—The administrator password is used to logon to the database. The administrator name is the same as what was defined for the main database.

    When defining, the password must conform to the following restrictions:

    - not case sensitive

    - cannot contain white space characters

    - maximum length of 28 characters

    - must begin with an alphabet

    - can contain only alphanumeric characters

    - cannot be an Oracle database reserved word

  - Description—A user-defined description to help identify this database.

Refer to the `ConsolidatorManager` Javadoc in the Oracle Database Lite API Specification for more details.

The following code example registers a database as `APP1`. The `registerDatabase` API stores access information for the application repository and provides a name so that publications, publication items, and MGP Jobs can be created against this repository. It does not define the administrator schema.

    Consolidator.DBProps props = new Consolidator.DBProps();
    props.jdbcUrl = "jdbc:oracle:thin:@apphost:1521:app1";
    props.description="App database 1"
    props.adminPassword = "secret";
    consMgr.registerDatabase("APP1", props);

The following code example deregisters the `APP1` database.

    consMgr.deRegisterDatabase("APP1");

You can retrieve the names of all of the registered databases with the `getDatabaseInstances` method, which is as follows:

    Map getDatabaseInstances()

The `Map` returned by `getDatabaseInstances` method contains a keyset of the application database names and the entry for each key is a `Consolidator.DBProps` class where the `adminPassword` is always null for security purposes.

### 3.2.3 Create Publication, Publication Item, Hints and Virtual Primary Keys on a Remote Database

You must have already registered the remote database before defining publications, publication items, hints, and virtual primary keys that use the application data schemas and tables on the remote database. In the `ConsolidatorManager` API calls, the registered name of the remote database is required.

Note:

The publication and publication item names are unique irrespective of where the data resides.

All publication items within a publication must be defined on tables within the same database.

The following example illustrates the creation of a publication and a publication item against a remote database registered as `APP1`. Refer to the `ConsolidatorManager` Javadoc in the Oracle Database Lite API Specification for more details.

    ConsolidatorManager consMgr = new ConsolidatorManager();
    consMgr.openConnection("mobileadmin", "mobileadmin",
     "oracle:jdbc:thin:@host1:1521:master");
    consMgr.createPublication( "PUB1","APP1",Consolidator.DFLT_CREATOR_ID,
     "ddb.%s", null);

    Consolidator.PubItemProps taskPIProps = new Consolidator.PubItemProps();
    taskPIProps.db_inst = "APP1"; // Remote App database name as registered
    taskPIProps.owner = "APPUSER1";
    taskPIProps.store = "TASKS";
    taskPIProps.refresh_mode = "F";
    taskPIProps.select_stmt = "select id, emp_id, cust_id, stat_id, notes
     from APPUSER1.TASKS";
    taskPIProps.cbk_owner = "MOBILEADMIN";
    taskPIProps.cbk_name = "TASKSPI_PKG";
    consMgr.createPublicationItem( "PI_1_TASKS", taskPIPProps);

    consMgr.addPublicationItem("PUB1", "PI_1_TASKS", null, null, "S", null, null);
    consMgr.createSubscription( "PUB1", "USER1");
    consMgr.instantiateSubscription("PUB1", "USER1");
    consMgr.closeConnection();

Other API calls for managing data collection queues, hints, and virtual primary keys that require the remote database name are shown below. Refer to the `ConsolidatorManager` Javadoc in the Oracle Database Lite API Specification for more details.

- Data Collection Queue

      void createDataCollectionQueue(String name, String db_inst,
         String owner, String store, String inq_cols, String pk_columns,
         boolean purgeClientAfterSync, boolean isOutView)

- Hint

      void parentHint(String db_inst, String owner, String store, String owner_d,
          String store_d)
      void dependencyHint(String db_inst, String owner, String store,
          String owner_d, String store_d)
      void removeDependencyHint(String db_inst, String owner, String store,
          String owner_d, String store_d)

- Virtual Primary Key

      public void createVirtualPKColumn(String db_inst, String owner,
          String store, String column)
      public void dropVirtualPKColumns(String db_inst, String owner,
          String store)

The APIs used for creating a publication and publication item is the same except for the addition of the remote database name. Following is an example that provides the remote database name, APP1, in bold for creating a publication and publication item:

    ConsolidatorManager consMgr = new ConsolidatorManager();
    consMgr.openConnection("mobileadmin", "mobileadmin", "oracle:jdbc:thin:@host1:1521:master");
    consMgr.createPublication( "PUB1","APP1",Consolidator.DFLT_CREATOR_ID,
           "ddb.%s", null);
    Consolidator.PubItemProps taskPIProps = new Consolidator.PubItemProps();
    taskPIProps.db_inst = "APP1"; // Remote APP instance name as registered
    taskPIProps.owner = "APPUSER1";
    taskPIProps.store = "TASKS";
    taskPIProps.refresh_mode = "F";
    taskPIProps.select_stmt = "select id, emp_id, cust_id, stat_id, notes from APPUSER1.TASKSî;
    taskPIProps.cbk_owner = "MOBILEADMIN";
    taskPIProps.cbk_name = "TASKSPI_PKG";
    consMgr.createPublicationItem( "PI_1_TASKS", taskPIPProps);
    consMgr.addPublicationItem("PUB1", "PI_1_TASKS", null, null, "S", null, null);
    consMgr.createSubscription( "PUB1", "USER1");
    consMgr.instantiateSubscription("PUB1", "USER1");
    consMgr.closeConnection();

### 3.2.4 Using Callbacks on Remote Databases

The following sections describe how the synchronization callbacks, described in Section 2.7, "Customize What Occurs Before and After Synchronization Phases", must be handled for the remote database:

- Section 3.2.4.1, "Customize Callbacks on the Remote Database"

- Section 3.2.4.2, "Publication Item Level Callbacks for the MGP Apply/Compose Phases"

#### 3.2.4.1 Customize Callbacks on the Remote Database

The Customize callbacks, as described in Section 2.7.1, "Customize What Occurs Before and After Every Phase of Each Synchronization", are created to perform defined tasks before or after any phase of synchronization.

Most of the callbacks pertain to MGP processing. Since an MGP Job executes against a database, these callbacks are invoked separately by each job against the corresponding database. Callbacks that are not related to the MGP are invoked against the MAIN database. Thus, the callback PL/SQL package must be created on the MAIN database as well as on the appropriate remote databases.

#### 3.2.4.2 Publication Item Level Callbacks for the MGP Apply/Compose Phases

Define the MGP publication item level callbacks on the database against which the publication item is defined. Then, these can access the base tables on that database.

For full details on the MGP publication item level callbacks, see Section 2.7.2, "Customize What Occurs Before and After Compose/Apply Phases for a Single Publication Item".

#### 3.2.4.3 Customizing the Apply/Compose Phase for a Queue-Based Publication Item on a Remote Database

When you customize the apply/compose phase for a queue-based publication item, as described in Section 2.13.1, "Customizing Apply/Compose Phase of Synchronization with a Queue-Based Publication Item", then these packages must be defined on the database where the queue-based publication item base tables exist. Thus, if the base tables exist on a remote database, then the packages must be defined on the remote database.

## 3.3 Create a Synonym for Remote Database Link Support For a Publication Item

Publication items can be defined for database objects existing on remote databases outside of the Mobile Server repository. Local private synonyms of the remote objects can be created in the Oracle database. However, we recommend that you use the remote database functionality as described in Section 3.2, "Register a Remote Oracle Database for Application Data".

If you still decide to use database links for defining publication items on remote databases, then you can execute the following SQL script located in the <`ORACLE_HOME`>`\Mobile\server\admin\consolidator_rmt.sql` directory on the remote schema in order to create Consolidator Manager logging objects.

The synonyms should then be published using the `createPublicationItem` method of the `ConsolidatorManager` object. If the remote object is a view that needs to be published in updatable mode and/or fast-refresh mode, the remote parent table must also be published locally. Parent hints should be provided for the synonym of the remote view similar those used for local, updatable and/or fast refreshable views.

Two additional methods have been created, `dependencyHint` and `removeDependencyHint`, to deal with non-apparent dependencies introduced by publication of remote objects.

Remote links to the Oracle database must be established prior to attempting remote linking procedures, please refer to the Oracle SQL Reference for this information.

Note:

The performance of synchronization from remote databases is subject to network throughput and the performance of remote query processing. Because of this, remote data synchronization is best used for simple views or tables with limited amount of data.

The following sections describe how to manage remote links:

- Section 3.3.1, "Publishing Synonyms for the Remote Object Using CreatePublicationItem"

- Section 3.3.2, "Creating or Removing a Dependency Hint"

### 3.3.1 Publishing Synonyms for the Remote Object Using CreatePublicationItem

The `createPublicationItem` method creates a new, stand-alone publication item as a remote database object. If the URL string is used, the remote connection is established and closed automatically. If the connection is null or cannot be established, an exception is thrown. The remote connection information is used to create logging objects on the linked database and to extract metadata.

Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.

    consolidatorManager.createPublicationItem(

         "jdbc:oracle:oci8:@oracle.world",

         "P_SAMPLE1",

         "SAMPLE1",

         "PAYROLL_SYN",

         "F"

         "SELECT * FROM sample1.PAYROLL_SYN"+"WHERE SALARY >:CAP", null, null);

Note:

Within the select statement, the parameter name for the data subset must be prefixed with a colon, for example `:CAP`.

### 3.3.2 Creating or Removing a Dependency Hint

Use the `dependencyHint` method to create a hint for a non-apparent dependency.

    Given remote view definition

            create payroll_view as

            select p.pid, e.name

            from payroll p, emp e

            where p.emp_id = e.emp_id;

    Execute locally

            create synonym v_payroll_syn for payroll_view@<remote_link_address>;

            create synonym t_emp_syn for emp@<remote_link_address>;

Where `<remote_link_address>` is the link established on the Oracle database. Use `dependencyHint` to indicate that the local synonym `v_payroll_syn` depends on the local synonym `t_emp_syn:`

    consolidatorManager.dependencyHint("SAMPLE1","V_PAYROLL_SYN","SAMPLE1","T_EMP_SYN");

Use the `removeDependencyHint` method to remove a hint for a non-apparent dependency.

Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.

## 3.4 Parent Tables Needed for Updateable Views

For a view to be updatable, it must have a parent table. A parent table can be any one of the view base tables in which a primary key is included in the view column list and is unique in the view row set. If you want to make a view updatable, provide the Mobile Server with the appropriate hint and the view parent table before you create a publication item on the view.

To make publication items based on a updatable view, use the following two mechanisms:

- Parent table hints

- `INSTEAD OF` triggers or DML procedure callouts

### 3.4.1 Creating a Parent Hint

Parent table hints define the parent table for a given view. Parent table hints are provided through the `parentHint` method of the Consolidator Manager object, as follows:

    consolidatorManager.parentHint("SAMPLE3","ADDROLRL4P","SAMPLE3","ADDRESS");

See the Javadoc in the Oracle Database Lite API Specification for more information.

### 3.4.2 INSTEAD OF Triggers

`INSTEAD OF` triggers are used to execute `INSTEAD OF INSERT`, `INSTEAD OF UPDATE`, or `INSTEAD OF DELETE` commands. `INSTEAD OF` triggers also map these DML commands into operations that are performed against the view base tables. `INSTEAD OF` triggers are a function of the Oracle database. See the Oracle database documentation for details on `INSTEAD OF` triggers.

## 3.5 Manipulating Application Tables

If you need to manipulate the application tables to create a secondary index or a virtual primary key, you can use `ConsolidatorManager` methods to programmatically perform these tasks in your application, as described in the following sections:

- Section 3.5.1, "Creating Secondary Indexes on Client Device"

- Section 3.5.2, "Virtual Primary Key"

### 3.5.1 Creating Secondary Indexes on Client Device

The first time a client synchronizes, the Mobile Server automatically enables a Mobile client to create the database objects on the client in the form of snapshots. By default, the primary key index of a table is automatically replicated from the server. You can create secondary indexes on a publication item through the Consolidator Manager APIs. See the Oracle Database Lite API Javadoc for specific API information. See Section 2.4.1.6, "Create Publication Item Indexes" for an example.

### 3.5.2 Virtual Primary Key

You can specify a virtual primary key for publication items where the base object does not have a primary key defined. This is useful if you want to create a fast refresh publication item on a table that does not have a primary key.

A virtual primary key must be unique and not null. A virtual primary key can consist of a single or multiple columns, where each column included in the virtual primary key must not null. If a null value is entered into any column of a virtual primary key, this results in an error. If the virtual primary key is on a single column, it must be unique; if the virtual primary key consists of a composite of multiple columns, then the composite must be unique.

If you want to create a virtual primary key for more than one column, then the API must be called separately for each column that you wish to assign to that virtual primary key.

Use the `createVirtualPKColumn` method to create a virtual primary key column.

    consolidatorManager.createVirtualPKColumn("SAMPLE1", "DEPT", "DEPT_ID");

Use the `dropVirtualPKColumns` method to drop a virtual primary key.

    consolidatorManager.dropVirtualPKColumns("SAMPLE1", "DEPT");

Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.

## 3.6 Facilitating Schema Evolution

You can use schema evolution when adding or altering a column in the application tables for updatable publication items. You do not use schema evolution for read-only publication items.

If you do alter the schema, then the client receives a complete refresh on the modified publication item, but not for the entire publication.

Note:

You should stop all synchronization events and MGP activity during a schema evolution.

The following types of schema modifications are supported:

- Add new columns

- Change the type of a column—You can only modify the type of a column in accordance to the Oracle Database limitations. In addition, you CANNOT modify a primary key or virtual primary key column

- Increase the width of a column

Note:

You cannot modify the definition of any primary key or virtual primary key using this method. Instead, use the directions provided in Section 3.6.1, "Schema Evolution Involving a Primary Key".

For facilitating schema evolution, perform the following:

1. If necessary, modify the table in the back-end Oracle database.

2. Modify the publication item directly on the production Mobile repository through MDW or the `alterPublicationItem` API. Modifying the SQL query of the publication item causes the schema evolution to occur.

   A schema evolution only occurs if the SQL query is modified. If the SQL query does not change, then the evolution does not occur. If your modification only touched the table, then you must modify the SQL query by adding an additional space to force the schema evolution to occur.

   Note:

   If you decide to republish the application to a different Mobile repository, then update the publication definition in the packaging wizard.

3. Once you alter the SQL query, then either use Mobile Manager to refresh the metadata cache or restart the Mobile Server. To refresh the metadata cache through the Mobile Server, select Data Synchronization->Administration->Reset Metadata Cache or execute the `resetCache` method of the `ConsolidatorManager` class.

Note:

Use of the high priority flag during synchronization will override any schema evolution, as a result, the new table definition will not come to the client.

When you modify the table in the Mobile repository, the client snapshot is no longer. Thus—by default—a complete refresh occurs the next time you synchronize, because a new snapshot must be created on the client.

### 3.6.1 Schema Evolution Involving a Primary Key

What if you want to perform a schema evolution that does include a modification to the primary key. Normally, you would drop the entire publication and recreate it. However, there is a way that you can modify the primary key constraint and recreate the publication item without dropping the entire publication.

The following steps describe how to remove the primary key constraint, add a new column and identify it as the primary or virtual primary key and then recreate the publication item. The steps below must be followed in the order listed:

1. Using MDW, remove the publication item from the publication and drop the publication item from the repository.

2. Modify the table in the back-end Oracle database, as described in the following steps:

   1. Drop the Primary Key constraint. For example, if `table1` has primary key constraint of `pk_constraint`, then drop this constraint, as follows:

          alter table table1 drop constraint pk_constraint;

   2. Add a new column to perform as the new primary key or virtual primary key, as follows:

          alter table table1 add my_new_col number(5,0) not null;

   3. Populate the new column with values that can be used (solely or as part of) the new the primary key or virtual primary key.

   4. Alter the table to create a primary key or virtual primary key constraint on the new column. If you want to create the primary key constraint on the new column `my_new_col` for `table1`, use the `ALTER TABLE` SQL command. If you want to define a virtual primary key on `my_new_col` for `table1`, use MDW.

3. In MDW, create a new publication item for `table1`. This should be a duplicate of the previously dropped publication item, but with teh new column included. When creating the publication item, verify the my_new_col appears as teh primary key.

4. Add the publication item to the publication.

5. Reset the Metadata Cache using the Mobile Manager by selecting Data Synchronization -> Administration -> Reset Metadata Cache.

6. Verify in the Parent Table Primary Key and Base table Primary Key fields in the Publication Item detail screen in the Mobile Manager that the new primary key is in effect.

7. Synchronize on the existing client device to bring down the new publication.

8. After the synchronization is complete, then verify that the new column is present and that it is functioning as the primary key on the client device.

## 3.7 Set DBA or Operational Privileges for the Mobile Server

You can set either DBA or operational privileges for the Mobile Server with the following Consolidator Manager API:

    void setMobilePrivileges( String dba_schema, String dba_pass, int type )
            throws ConsolidatorException

where the input parameter are as follows:

- `dba_schema`—The DBA schema name

- `dba_pass`—The DBA password

- `type`—Define the user by setting this parameter to either `Consolidator.DBA` or `Consolidator.OPER`

If you specify Consolidator.DBA, then the privileges needed are those necessary for granting DBA privileges that are required for publish/subscribe functions of the Mobile Server.

If you specify `Consolidator.OPER` type, then the privileges needed are those necessary for executing the Mobile Server without any schema modifications. The OPER is given DML and select access to publication item base objects, version, log, and error queue tables.

The Mobile Server privileges are modified using the `C$MOBILE_PRIVILEGES` PL/SQL package, which is created for you automatically after the first time you use the `setMobilePrivileges` procedure. After the package is created, the Mobile Server privileges can be administered from SQL or from this Java API.

## 3.8 Datatype Conversion Between the Oracle Server and the Oracle Lite Database

Before you publish your application, create the tables for your applications in the Oracle database. Thus, when the first synchronization occurs for an Oracle Lite Mobile client, Oracle Database Lite takes the Oracle database datatypes and converts them to corresponding allowed datatypes in the Oracle Lite database on the client. Table 3-1 lists the Oracle database datatypes in the left column and displays how the datatype can be mapped to the Oracle Lite database datatypes across the top row.

Note:

For Oracle Database Lite Datatypes, see Appendix E, "Oracle Database Lite Datatypes" in the Oracle Database Lite SQL Reference.

Table 3-1 Conversion of Oracle Database Datatypes to Oracle Database Lite Datatypes

Oracle Database Lite Datatypes | 1 B | 2 B | 4 B | Float | Double | Number | Date Time | Long Var Binary | Varchar | Char | BLOB | CLOB
--- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ---
INTEGER |  |  | X |  |  |  |  |  |  |  |  |
VARCHAR2 |  |  |  |  |  |  |  |  | X |  |  |
VARCHAR |  |  |  |  |  |  |  |  | X |  |  |
CHAR |  |  |  |  |  |  |  |  |  | X |  |
SMALLINT |  | X |  |  |  |  |  |  |  |  |  |
FLOAT |  |  |  | X |  |  |  |  |  |  |  |
DOUBLE PRECISION |  |  |  |  | X |  |  |  |  |  |  |
NUMBER |  | X | X |  |  | X |  |  |  |  |  |
DATE |  |  |  |  |  |  | X |  |  |  |  |
LONG RAW |  |  |  |  |  |  |  | X |  |  |  |
LONG |  |  |  |  |  |  |  |  | X |  |  |
BLOB |  |  |  |  |  |  |  |  |  |  | X |
CLOB |  |  |  |  |  |  |  |  |  |  |  | X

Note:

Oracle Database Lite does not support creating publication items for synchronization on a table with object type columns, even if the publication item query does not include any of the object type columns. However, it is possible to define a view which selects only columns of supported data types and then create a publication item using the view definition.

"X" indicates that the datatype can be mapped to this Oracle Lite database datatype. To save on space, signed 1 byte represents TINYINT, signed 2 byte represents SMALLINT, and signed 4 byte represents INTEGER.

For conversion of the NUMBER datatype, if the precision is less than 5, then the number maps to a signed 2 byte (`SMALLINT`) datatype. If the precision is less than 10, then it maps to a signed 4 bytes (`INTEGER`) datatype. Even though the numbers are not equivalent on the client and the server, we still guarantee that valid numbers from the server will transfer to the client, and invalid numbers from the client are rejected by the server.

While the `TIMESTAMP` data type is supported; the `TIMESTAMP WITH TIME ZONE` is not supported for publication items.