This post is the first one of a set that I will write about Oracle Lite. I have been working with Oracle Lite and I am impressed. Not by the level of documentation, but by the functionality of the package. Oracle Lite gives you the ability to create data snapshots on laptops, PDA’s, mobile phones etc. The synchronisation mechanism (provided by the tool msync) will synchronise data bi-directionally between the snapshot and the main database server. Client applications that use the Oracle lite snapshot can also be distributed en synchronised by Oracle Lite’s synchronisation functionallity. This makes deployment of applications very simple. I have written and distributed a simple .Net client written in C++ this way. My application uses ODBC to access the database. JDBC is also provided. Unfortunately it’s not possible to access the database by using OCI which is my favorite while programming in C/C++.
Oracle Lite applications can be defined with the wtgpack tool. With this tool you can define the snapshots by entering select statements. Queries can be restricted by using bind variables. The actual values of these variables can be entered by the administrator on a user of group level. The wtgpack tool can be used to deploy the application on the Oracle Lite middle tier. Clients use this tier for their synchronisation or to install the initial Lite database software. Deployment with wtgpack can end up with a 401 of 500 error. Tool at the tips below when you have one of these errors because the description of the error will not help you at all. The wtgpack tool will be described in a future post.
Installation of Oracle Lite is easy. Just install the server part by using the supplied installer. Here are a number of apparently simple and easy tips that will make life easier when you start:
- Make sure to install patch set 1 after the installation of Oracle Lite 10.0.0.0.0. This way the deployment of an application defined by wtgpack will work much better and reliably.
- The tables that are synchronised MUST have a primary key. Oracle Lite can also work with virtual primary key (mandatory unique keys?). But my experience tells me to use real primary keys. wtgpack will error with a 401 or 500 error during deployment of the application when one or more primary keys are missing.
- Oracle Lite will create three tables per synchronised table in the schema that owns the tables. This messes up your database. It’s better to create an extra user holding synonyms to the synchronised tables. Synchronisation should be done via this extra user on the synonyms.
- The msync tool will not work on very large tables because it will only commit after all the records are copied to the Oracle Lite database. It’s best to modify polite.ini (it’s in the windows directory) before you perform the first msync. Add AUTO_COMMIT_COUNT=500 in the [SYNC] section of the file. This makes sure the during synchronisation a commit is performed after each 500 rows.
- wtgpack will display the tables that are used in the snapshot in a (for me) random order. Deployment of a snapshot will fail when there is a syntax error in the query; again a 401 or 500 error during deployment. Count the number of snapshots created in the log of OC4J. This way you can easily tell which table is failing because the snapshots are created in the order they are displayed in the wtgpack application.