B Oracle Lite Database Utilities

This appendix describes how to use the following Oracle Lite database utilities for the Windows 32 and Windows CE platforms. Table B-1 lists all of the utility names:.

Table B-1 Database Tools and Utilities

Utility	Description
Section B.1, "The mSQL Tool"	Allows users to execute SQL statements against the Oracle Lite database.
Section B.2, "CREATEDB"	Use this to create your Oracle Lite database.
Section B.3, "REMOVEDB"	Use this to remove your Oracle Lite database.
Section B.4, "ENCRYPDB"	Use this to encrypt your Oracle Lite database.
Section B.5, "DECRYPDB"	Use this to decrypt your Oracle Lite database.
Section B.6, "MIGRATE"	Use this to migrate to Oracle Database Lite from a previous release.
Section B.7, "ODBC Administrator and the Oracle Database Lite ODBC Driver"	Use this to manage ODBC connections by creating data source names (DSNs) that associate the Oracle Database Lite ODBC Driver with the Oracle Database Lite that you want to access through the driver.
Section B.8, "ODBINFO"	Use this utility to find out the version number and volume ID of an Oracle Database Lite database.
Section B.9, "VALIDATEDB"	Use this to validate the structure of an Oracle Lite database and find any corruption of the database.
Section B.10, "Transferring Data Between a Database and an External File"	Use either the command-line tool or programmatic APIs to load data from an external file into a table in Oracle Database Lite, or to unload (dump) data from a table in Oracle Database Lite to an external file.
Support for Linguistic Sort	Allows databases to be created with linguistic sort capability enabled. See Section 2.11, "Support for Linguistic Sort" for more information.
dropjava	This is a command-line utility you can use to remove Java classes from Oracle Database Lite. For more information, see Section 11.3.1.4.1, "Using dropjava".
loadjava	This is a command-line utility you can use to load a Java class into Oracle Database Lite. For more information, see Section 11.3.1.1.1, "loadjava".

B.1 The mSQL Tool

Mobile SQL (mSQL) is a GUI-based application that runs on the client device (laptop, Palm OS, and Windows CE). It allows the user to execute SQL statements against the local database. It is both a developers tool and a code example. It allows users to access functionality provided by the ODBC and Oracle Database Lite OKAPI interfaces of the underlying Oracle Database Lite database engine.

The mSQL tool enables you to create, access, and manipulate Oracle Database Lite on Palm Computing platform devices. Using mSQL you can accomplish the following:

• Create databases

• View tables

• Execute SQL statements

The following sections describe how to use the mSQL tool on two platforms:

• Section B.1.1, "The mSQL Tool for Windows 32"

• Section B.1.2, "The mSQL Tool for Windows CE"

B.1.1 The mSQL Tool for Windows 32

The mSQL tool is an application that runs as a command-line interface. It allows the user to execute SQL statements against the local database. The mSQL tool accesses the database through JDBC. It is both a developers tool and a code example.

The following sections describe information relevant to database access, starting mSQL and mSQL commands:

• Section B.1.1.1, "Starting mSQL"

• Section B.1.1.2, "Populating your Database Using mSQL"

• Section B.1.1.3, "SET TERM {ON|OFF}"

• Section B.1.1.4, "SET TIMING {ON|OFF}"

• Section B.1.1.5, "SET VERIFY {ON|OFF}"

B.1.1.1 Starting mSQL

Start mSQL by opening the ORACLE_HOME\Mobile\SDK\Bin directory and double-clicking on the msql.exe file. This starts the command-line interface that accepts standard SQL commands. For more information, see the Oracle Database Lite SQL Reference.

B.1.1.2 Populating your Database Using mSQL

You can use SQL scripts to create tables and schema, and to insert data into tables. A SQL script is a text file, generally with a .sql extension, that contains SQL commands. You can run a SQL script from the mSQL prompt, as follows:

msql> @<ORACLE_HOME>\DBS\Poldemo.sql

You can also type:

msql> START <filename>

Note: You do not need to include the .sql file extension when running the script.

B.1.1.3 SET TERM {ON|OFF}

Controls the display of output generated by commands executed from a script. OFF suppresses the display so that you can spool output from a script without seeing the output on the screen. ON displays the output. TERM OFF does not affect output from commands you enter interactively.

B.1.1.4 SET TIMING {ON|OFF}

Controls the display of timing statistics. ON displays timing statistics on each SQL command. OFF suppresses timing of each command.

B.1.1.5 SET VERIFY {ON|OFF}

Controls whether to list the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values. ON lists the text; OFF suppresses the listing.

B.1.2 The mSQL Tool for Windows CE

The following sections describe start mSQL and how to access the database using mSQL for the Windows CE platform:

• Section B.1.2.1, "Database Access"

• Section B.1.2.2, "Starting mSQL"

• Section B.1.2.3, "Manage Snapshots Using mSQL"

B.1.2.1 Database Access

The mSQL tool accesses the database through both the ODBC and OKAPI interface. Most functions are performed through ODBC, but functions that ODBC cannot handle are implemented using OKAPI function calls.

B.1.2.2 Starting mSQL

Start mSQL by opening the <ORACLE_HOME>\Mobile\Sdk\WinCE, selecting the folder representing the version Windows CE, and selecting the processor on your device. Double-click on the msql.exe file. This starts the GUI which accepts standard SQL commands. For more information, see the Oracle Database Lite SQL Reference.

B.1.2.3 Manage Snapshots Using mSQL

The Oracle Lite database format is the same on Windows 32 and Windows CE platforms. Manage your snapshots, as follows:

1. Create and test your snapshots on Windows 32 using the Windows 32 mSQL command-line utility.

2. Copy the database to the Windows CE platform.

3. Use the Windows CE mSQL tool to manipulate the database that is on your device.

The mSQL tool enables the user to execute SQL statements against the local database and access functionality provided by the interfaces of the underlying Oracle Lite database engine.

B.2 CREATEDB

Description

Utility for creating a database.

Syntax

CREATEDB DataSourceName DatabaseName Database_SysUser_Password [[[VolID] DATABASE_SIZE] EXTENT_SIZE] [collation sequence]

Keywords and Parameters

DataSourceName

Data source name, used to look up the ODBC.TXT file for the default database directory.

Note: If you specify an invalid DSN, Oracle Database Lite ignores the DSN and creates the database in the current directory. To access this database through ODBC, you must create a DSN for the database that points to the directory in which the database resides. For instructions on adding a DSN, see Section B.7.1, "Adding a DSN Using the ODBC Administrator".

DatabaseName

Name of the database to be created. It can be a full path name or just the database name. If only the database name is given, the database is created under the Data Directory for the data source name specified in the ODBC.TXT file. The extension for the database name must always be .ODB. If a name without the .ODB is given, the .ODB is appended.

DATABASE_SysUser_Password

The database system user password.

VolID

When specified, the VolID is used as the database ID, instead of the database ID from the POLITE.INI file. The ID must be unique for each database. If you specify a volumn id, then you also specify the database and extent sizes. Thus, the createdb executable knows that the volume id, database size and extent size are being specified when three numbers are provided in a row.

Note: For the volume id, database size, and extent size, specify only the number; do not specify name=value. See the examples for more information.

DATABASE_SIZE

The database size in bytes. If you want to specify the database size, then you also must specify the volume id and extent size.

EXTENT_SIZE

An incremental amount of pages in a database file. When a database runs out of pages in the current file, it extends the file by this number of pages. If you want to specify the extent size, then you also must specify the volume id and database size.

COLLATION_SEQUENCE

This parameter is a string constant which creates the database as enabled for linguistic sorting when a value other than the default is used. A collation sequence specified here overrides a collation sequence set using the NLS_SORT [collation_sequence] parameter in the POLITE.INI file. The string can also be one of the options listed in Table B-2:

Table B-2 Collation Sequence Values

Collation Sequence	Description
BINARY	Default. Two strings are compared character by character and the characters are compared using their binary code value.
FRENCH	Two strings are compared according to the collation sequence of French. Supported by ISO 8859-1 or IBM-1252.
GERMAN	Two strings are compared according to the collation sequence of German. Supported by ISO 8859-1 or IBM-1252.
CZECH	Two strings are compared according to the collation sequence of Czech. Supported by ISO 8859-2 or IBM-1250.
XCZECH	Two strings are compared according to the collation sequence of Xczech. Supported by ISO 8859-2 or IBM-1250.

Note: There is no way to alter a collation sequence after the database is created.

Examples

Create the db1 database with DSN of polite and password manager: createdb polite db1 manager

Create the db2.odb database with DSN polite and password manager300: createdb polite c:\testdir\db2.odb manager300

Create polite database with DSN polite, password of manager, and a collation sequence of french: createdb polite polite manager french

Create polite database with DSN polite, password manager, volume id of 199, database size of 1000, and extent size of 1: createdb polite polite manager 199 1000 1

B.3 REMOVEDB

Description

Utility for deleting a database.

Syntax

REMOVEDB DataSourceName Database Name

Keywords and Parameters

DataSourceName

Data source name of the database you want to remove. The DSN can be a dummy argument such as none, in which case the database name must be a fully qualified filename.

DatabaseName

The name of the database to delete. It can be a full path name or just the database name. If only the database name is given, the database is deleted from the Data Directory for the data source name specified in the ODBC.INI file.

Examples

removedb polite db1

removedb none c:\testdir\db2.odb

B.4 ENCRYPDB

Description

Enables you to encrypt Oracle Database Lite with a password, which prevents unauthorized access to the database and encrypts the database, so that the data stored in the database files cannot be interpreted. To decrypt the database, see Section B.5, "DECRYPDB".

This tool is used by embedded applications to encrypt the database used by the application. To encrypt an Oracle Lite database used by a Mobile client, see the ENCRYPT_DB parameter in the POLITE.INI Appendix in the Oracle Database Lite Administration and Deployment Guide.

ENCRYPDB uses AES-128 encryption.

Syntax

ENCRYPDB DSN | NONE DBName [New_Password [Old_Password]]

Keywords and Parameters

• DSN—Data Source Name of Oracle Database Lite that you want to encrypt. If you specify NONE, DBName must be a fully qualified database name with the full path name (without the .ODB extension). If the DSN is a value other than NONE, then the name must appear as a data source name in the ODBC.TXT file.

• DBName—Name of the database to be encrypted. If DSN was specified as NONE, DBName must be entered with the full path name.

• New_Password and Old_Password—Optional, the password (or previously used password) for encrypting the database. This password can be 128 characters in length. If you do not enter a password, ENCRYPDB prompts you to enter one. Since both passwords are optional in the command line to invoke the utility, the command line could have three different forms:

  • No password given: If the database is already encrypted, then ENCRYPDB assumes that the user is trying to change the password of the database. It prompts the user for the old password once and new password twice, and encrypts the database using the new password. If the database is not already encrypted, ENCRYPDB prompts for the new password twice and encrypts the database using this new password.

  • One password given: This password is assumed to be the new password. If the database is already encrypted, ENCRYPDB prompts for the old password and encrypts the database using the new password.

  • Both passwords given: ENCRYPDB assumes that the first password is the new password and the second is the old password.

Comments

If you call this utility from another program, the possible values returned are listed in Table B-3:

Table B-3 ENCRYPDB Return Codes

Return Code	Description
EXIT_SUCCESS	Success
EXIT_USAGE	Command line arguments are not properly used or are in error
EXIT_PATH_TOO_LONG	Path is too long
EXIT_SYSCALL	I/O error while making new encrypted copy on disk
EXIT_BAD_PASSWD	Incorrect password supplied

The default Oracle Database Lite (POLITE.ODB) is not encrypted. After encrypting an Oracle Database Lite, every user that attempts to establish a connection to the encrypted Oracle Database Lite must provide the valid password. If the password is not provided, Oracle Database Lite returns an error. An Oracle Database Lite database cannot be encrypted if there are any open connections to the database.

You should consider the following when encrypting and decrypting Oracle Database Lite:

• You cannot decrypt an encrypted database without the password. Make sure you back up your database in a secure place before you encrypt it. Another user of the same database can create a copy with a new user name for a user who loses their password, otherwise, there is no method to recover a database where the passwords are lost.

• After encrypting the database, you must include the password in the connect string to connect to the database.

• A password encrypts the entire database. It is not a user-specific password.

• Database encryption does not prevent a third party from removing an Oracle Lite Database. That is, removedb and rmdb remove a database without checking the password. Use tools that protect unauthorized users from manipulating your file system.

• ODBC applications that connect to an encrypted Oracle Database Lite database need to specify a valid password. It is customary to prompt for the password at runtime rather than to code it in the application. Most ODBC applications can use the SQLDriverConnect function with the DRIVER= option, rather than the SQLConnect function, if the applications require the Oracle Database Lite ODBC driver to prompt for the password at runtime.

• All sample applications provided with this release of Oracle Database Lite are designed to run against a database that is not encrypted.

• You can use DECRYPDB and ENCRYPDB (in this order) to change the password of a database. However, DECRYPDB creates an Oracle Database Lite database in plain text before ENCRYPDB encrypts it. This results in a database in plain text form, for a short period of time, and is not recommended.

• For encrypted databases, all user names and passwords are written to a file named DSN.OPW. Each user can then use the password as a "key" to unlock the .OPW file before the .ODB file is accessed. When you copy or back up the database, you should include the .OPW file.

B.5 DECRYPDB

Description

This tool allows you to decrypt an encrypted Oracle Lite database used with an embedded application. For more information, see Section B.4, "ENCRYPDB".

This tool is used by embedded applications to decrypt the database used by the application. To encrypt an Oracle Lite database used by a Mobile client, see the ENCRYPT_DB parameter in the POLITE.INI Appendix in the Oracle Database Lite Administration and Deployment Guide.

SYNTAX

DECRYPDB DSN | NONE DBName [Password]

Keywords and Parameters

DSN

Data Source Name of Oracle Database Lite that you want to decrypt. If you specify NONE, you must the enter the DBName with the full path name (without the .ODB extension).

DBName

Name of the database to be decrypted. If DSN was specified as NONE, the DBName must be entered with the full path name.

Password

Optional. The password used previously to encrypt Oracle Database Lite. If you do not enter the password, DECRYPDB prompts you to enter it.

Comments

An Oracle Database Lite database cannot be decrypted if there is any open connection to the database.

If you call this utility from another program, the possible values returned are listed in Table B-4:

Table B-4 DECRYPDB Return Codes

Return Code	Description
EXIT_SUCCESS	Success
EXIT_USAGE	Command line arguments are not properly used or are in error
EXIT_PATH_TOO_LONG	Path is too long
EXIT_SYSCALL	I/O error while making new decrypted copy on disk
EXIT_BAD_PASSWD	Incorrect password supplied

For more information, see the comments in Section B.4, "ENCRYPDB".

B.6 MIGRATE

Description

Utility for migrating a database from a previous version of Oracle Lite to Oracle Database Lite 10g. Before you use this utility, you must install the current release of Oracle Database Lite. Also, if your database is encrypted, you must first decrypt it before using this utility.

Syntax

MIGRATE DSN DBName

where DBName can be the database name or the database path and name.

Keywords and Parameters

DSN

Data source name of the database to migrate. This is used to look up the default database directory in the ODBC.INI file for the database name given in DBName. If the DSN has the value NONE the DBName should be a complete pathname of the database file.

DBName

The database name, or the path and database name, to migrate. If only the database name is specified, the database file must exist in the directory specified in the DataDirectory parameter (under the data source name) in the ODBC.INI file.

Comments

As mentioned in this section, you must install Oracle Database Lite before you use this utility.

Any messages generated by the MIGRATE utility are displayed on the screen in the command window.

Using this utility allows you to compress empty space in your existing Oracle Database Lite database.

This utility does not support the migration of Java Stored Procedures.

Examples

MIGRATE polite db1

MIGRATE none c:\testdir\db1.odb

B.7 ODBC Administrator and the Oracle Database Lite ODBC Driver

A Data Source Name (DSN) associates the Oracle Database Lite ODBC Driver with the Oracle Database Lite database that you want to access through the driver. The Oracle Database Lite installation process creates a default DSN, POLITE, for the Oracle Database Lite database. You can also create additional DSNs for the additional Oracle Database Lite databases that you create.

Microsoft provides the ODBC Administrator, a tool for managing the ODBC.INI file and associated registry entries in Windows 98/NT/2000/XP. The ODBC.INI file and the Windows registry store the DSN entries captured through the ODBC Administrator. Using the ODBC Administrator, you can relate a DSN to the Oracle Database Lite ODBC Driver.

Note: This document does not provide instructions on using the ODBC Administrator. See the ODBC Administrator tool online help for this information.

In the ODBC Administrator, in addition to the DSN, you must specify the parameters listed in Table B-5:

Table B-5 ODBC Administrator DSN Parameters

DSN Parameter	Description
Data Description	An optional description for the data source.
Database Directory	The path to the data directory where the database resides. This is an existing path.
Database	Oracle Database Lite database name to be created. Do not include the .ODB extension.
Default Isolation Level	Determines the degree to which operations in different transactions are visible to each other. For more information on the supported isolation levels, refer the Oracle Database Lite Developer’s Guide. The default level is "Read Committed".
Autocommit	Commits every database update operation in a transaction when that operation is performed. Autocommit values are Off and On. The default value is Off. Note: In the Microsoft ODBC SDK, the ODBC driver defaults to auto-commit mode. However, the default for Oracle Database Lite is manual-commit mode. In this environment, if you execute SQLEndTrans / SQLTransact call with SQL_COMMIT option using the ODBC driver, you receive a SQL_SUCCESS, because ODBC believes that auto-commit is on. However, no commit actually occurs, because ODBC transfers the transaction to Oracle Database Lite, whose default is manual-commit. You must configure the Microsoft ODBC Driver Manager to transfer control of the SQLEndTrans / SQLTransact API call to Oracle Database Lite by explicitly setting autocommit to OFF in ODBC. When you do this, ODBC does not try to autocommit, but gives control of the transaction to Oracle Database Lite. To set auto-commit to off, execute either the SQLSetConnectAtrr or SQLSetConnectOption method with SQL_AUTOCOMMIT_OFF as the value of the SQL_AUTOCOMMIT option. Then, the SQLEndTrans / SQLTransact calls will commit as defaulted within Oracle Database Lite. Thus, if you want auto-commit on, turn it on only within Oracle Database Lite.
Default Cursor Type	Forward Only: Default. A non-scrollable cursor which only moves forward but not backward through the result set. As a result, the cursor cannot go back to previously fetched rows. Dynamic: Capable of detecting changes to the membership, order, or values of a result set after the cursor is opened. If a dynamic cursor fetches rows that are subsequently deleted or updated by another application, it detects those changes when it fetches those rows again. Keyset Driven: Does not detect change to the membership or order of a result set, but detects changes to the values of rows in the result set. Static: Does not detect changes to the membership, order or values of a result set after the cursor is opened. If a static cursor fetches a row that is subsequently updated by another application, it does not detect the changes even if it fetches the row again.

For example, the DSN entry for POLITE in the ODBC.INI file may contain:

[POLITE]
Description=Oracle Lite Data Source
DataDirectory=C:\ORANT\OLDB40
Database=POLITE
IsolationLevel=Repeatable Read
CursorType=Dynamic

B.7.1 Adding a DSN Using the ODBC Administrator

To add a DSN using the ODBC Administrator:

1. Start the ODBC Administrator, either by selecting its icon in the Oracle Database Lite program group, or by typing the following at a DOS prompt:

   C:\>ODBCAD32

2. Click Add.

3. Double-click the Oracle Database Lite nn ODBC Driver, where nn is the release number, from the list of Installed ODBC Drivers.

4. Next, add the DSN name and define the parameters in the ODBC driver setup dialog. Refer the preceding table for help in defining the parameters.

B.7.2 Adding a DSN which points to Read-Only Media (CD-ROM)

1. Create the DSN as explained in Section B.7.1, "Adding a DSN Using the ODBC Administrator".

2. Add the following line to the new DSN in the ODBC.INI file:

   ReadOnly = True

   
   

   Note: You can define a DSN which points to a file on a CD-ROM. Simply point the DSN to the CD-ROM drive and directory and provide the file name of the database file. Then modify the ODBC.INI file to add the line ReadOnly=True to the data source definition. ODBC programmers can call the following before opening the database to enable this feature (instead of adding the line to the ODBC.INI file): SQLSetConnectOption( hdbc, SQL_ACCESS_MODE, SQL_MODE_READ_ONLY ) Setting a database file to read-only suppresses the creation of log files. Updates, insertions, deletions, or commits appear to work on the in-memory image of tables. However, when you commit, these changes are not written to the database file. If you exit your application, reconnect, and issue your query, you see your original data.

   
   

B.8 ODBINFO

Description

You can use ODBINFO to find out the version number and volume ID of an Oracle Database Lite database. ODBINFO can also display and set several parameters.

Syntax

To display current information without making any changes use the syntax:

odbinfo [-p passwd]DSN DBName

You can also use:

odbinfo [-p passwd] NONE dbpath\dbanme.odb

For example:

odbinfo -p tiger polite polite

odbinfo NONE c:\orant\oldb40\polite.odb

If your database is encrypted you need to include the password.

Parameters

To set or clear parameters, use one or more "+" or "-" parameter arguments before the DSN or NONE. For example:

odbinfo +reuseoid -pagelog -fsync polite polite

You can use the parameters listed in Table B-6 with the ODBINFO utility:

Table B-6 ODBINFO Parameters

Parameter	Description
pagelog	By default, a commit backs up modified database pages to filename.plg before actually writing the changes to filename.odb. If an application or the operating system experiences a failure during a commit, the transaction is cleanly rolled back during the next connect. If -pagelog is specified, no backup is created and the database can become corrupted if a failure occurs.
fsync	Oracle Database Lite generally forces the operating system to write all the modified buffers associated with the database back to disk during a commit. If this option is disabled (-fsync), the operating system can keep the changes in memory until a later time. If the system (but not the application) crashes before the buffers are flushed, the database can become corrupted. Using odbinfo -fsync -pagelog improves the performance of applications that use many small transactions (with autocommit on) or ones with massive updates. However, if the database is corrupted, there is no straightforward way to repair it or recover the data. Therefore these two options should only be cleared during initial loading of the database, if (1) the .ODB file is backed up on regular basis, or (2) the data in the database can be recovered from some other source. Using this option has no effect on applications that seldom update the database. Setting the transaction isolation level to SINGLE USER has more impact in this case.
reuseoid	By default, Oracle Database Lite does not reuse the ROWID of any row that exists in a table until the table is dropped. The "Slot Deleted" error is returned when accessing a deleted object. This uses two bytes of storage for each deleted object, causing performance and disk space usage to degrade over time if rows are constantly inserted and deleted. If you use odbinfo +reuseoid, new rows can reuse ROWIDs of previously deleted rows. However, this may not free all the space in a table that already has many deleted objects. For best results, you should set this option immediately after you create your database. This option is safe for pure relational applications. However, SQL applications that use ROWID and OKAPI applications that use direct pointers between objects need to verify that all references to an object are set to NULL before the object is deleted. Otherwise, dangling references may eventually point to some other, unrelated object.
compress	This option (which is "on" by default) enables run-length compression of objects. Run-length compression takes very little CPU time, so you should only deselect (-compress) this option if: Operating system-level file compression is used, such as DriveSpace or a NTFS compressed attribute. In this case not compressing the same data twice provides a better compression ratio. Most objects in the database are frequently updated to a highly compressible state (for example, all columns set to NULL), and the data cannot be compressed well (such as binary columns with random data). In these cases, using this option (+compress) can result in highly fragmented tables. Changing this option does not compress or decompress any existing objects in the database.

B.9 VALIDATEDB

Description

This command-line tool validates the structures within the database file and if the database structure is found to be corrupted, lists the errors found in a file designated by the user. The tool checks the following:

• Objects - Header information for database objects. Flags are checked for consistency in case the object was moved or compressed. Object length is checked against a valid range. If the object is a BLOB, the object's frames are checked against the volume page bitmap.

• Index page entries - Checks that the creation of an index page entry results in the correct number of nodes or list of object identifiers.

• Index pages - Checks that all key values on the page are sorted. All objects contained on the page are validated. Page descriptor information such as the number of objects, the number of free bytes, and the number of entries are checked against the actual objects on the page.

• Groups - As each page is validated, the group descriptor information is checked against the actual number of pages and objects.

• Indexes - All the pages are validated against the btree. The tool also validates all page pointers. All levels of the btree are checked to validate that key values are in the sorted order as a whole. For leaf elements of the btree, all OIDs from the leaf page entries are checked for consistency with the actual group objects.

Syntax

validatedb DSName DBName [-p password] [-t schemaname.tablename] -file outputfilename

Keywords and Parameters

DSName

The data source name. This can also be NONE if no DSN is present.

DBName

If there is a DSN present, this is the database file name (without the .odb extension) if it is different from the default filename for the DSN. If there is no DSN, then VALIDATEDB uses the current directory unless the full path is specified. If there is a log file in the same directory as the database file, it is also validated.

password

Password for an encrypted database.

schemaname

Optional schema name. The default schema name is used unless this is specified.

tablename

Optional table name. The specified table is validated along with all of its indexes. If no table name is specified, the entire database is validated.

outputfilename

Optional filename for the text file where all errors and other related information revealed by VALIDATEDB are saved. The default is stdout.

Examples

validatedb polite polite -t emp -file out.txt

B.9.1 Using the VALIDATEDB Utility for the Palm Platform

The following sections describe how to use the validatedb utility:

• Section B.9.1.1, "Overview"

• Section B.9.1.2, "Installing VALIDATEDB"

• Section B.9.1.3, "Running VALIDATEDB"

• Section B.9.1.4, "Sending Corrupted Databases"

• Section B.9.1.5, "Sending Companion Databases"

B.9.1.1 Overview

In some cases, the Oracle Database Lite database on Palm may become corrupted. It can be caused by hardware problems or bugs in the database code. Using the validatedb utility, you can inspect and diagnose database corruptions. As database users and application developers, you can execute the validatedb utility to check the database for consistency. The Oracle Database Lite development group uses the validatedb utility to diagnose the extent of corruption and fixes the problem.

B.9.1.2 Installing VALIDATEDB

To install the validatedb utility, install a single file named validatedb.prc that is located in the Lite\Runtime directory. Install the PRC file on your Palm device or emulator using the HotSync application.

B.9.1.3 Running VALIDATEDB

To run the validatedb utility, perform the following steps.

1. Click the validatedb icon. The main validatedb form appears. As Table B-7 describes, the validatedb form contains the following items.

   Table B-7 validatedb Form Description

   Item	Description
   Oracle Database Lite database list	List of Oracle Database Lite databases installed on the device (or emulator).
   Log to Desktop	If selected, logs database information to your desktop.

   
   

2. To validate a database, choose the Oracle Database Lite database from the list displayed. To validate all databases, click Validate All.

   
   

   Note: You will be prompted for the password of each encrypted database that requires validation.

   
   

After validation, the validatedb utility displays one of the following alerts:

• No Errors Found—No corruption has been detected.

• CorruptedDB—The utility has detected some corruption and the databases need to be sent to Oracle Support for further investigation.

• System Fatal—The utility has detected system fatal alerts and the databases need to be sent to Oracle Support for further investigation. You should reset the device if you receive a system fatal alert.

• Assertion - The utility has detected some errors and the databases need to be sent to Oracle Support for further investigation.

Note: Do not select the "Log to Desktop" box as it is used by Oracle Support to log further debugging information to a desktop computer. If you select this box, the validatedb utility stops functioning and does not respond.

B.9.1.4 Sending Corrupted Databases

If the validatedb utility detects corruption in the database, it is mandatory that you send all such databases to Oracle Support. To send corrupt databases, click BackupAll. This command sets up the backup flag for all Oracle Database Lite databases on the device. During the next HotSync instance, the Oracle Database Lite databases are backed up on the desktop computer. After running the HotSync application, you will find these databases in the directory of the HotSync manager named Palm\<HotSync user name>\Backup.

B.9.1.5 Sending Companion Databases

Send along the following companion databases along with the corrupted database to Oracle Support:

• All databases which appear in the list on the validatedb form.

• Databases that start with the same name as the ones on the list, but contain the extension $1, $2, ...,. For example, OrdersODB$1.PDB, OrdersODB$2.PDB, and so on. These are Oracle Database Lite extensions for large databases.

• okSysDB.PDB

• okTransLog.PDB

To correct corrupted databases, Oracle Support retrieves further debugging information using the validatedb utility for diagnosis.

B.10 Transferring Data Between a Database and an External File

You can transfer data between an external file and the Oracle Lite database through either a command-line tool or programmatic APIs, as described in the following sections:

• Section B.10.1, "OLLOAD"

• Section B.10.2, "Oracle Database Lite Load Application Programming Interfaces (APIs)"

B.10.1 OLLOAD

The Oracle Database Lite Load Utility (OLLOAD) is a command-line tool, which enables you to load data from an external file into a table in Oracle Database Lite or to unload (dump) data from a table in Oracle Database Lite to an external file. Unlike SQL*Loader, OLLOAD does not use a control file in which you supply all data parameters and format information on the command-line.

When loading data, OLLOAD takes an input file that contains one record per line with a separator character between fields. The default field separator is a comma (,). These records can also include fields with values that are quoted strings. The default value is single quote ('). For more information on data parsing, see "Data Parsing".

B.10.1.1 Syntax

Loading a Datafile

To load a datafile, use the following syntax.

olload [options] -load dbpath tbl [col1 col2 ...] [<datafile]

Unloading (dump) to an Outfile

olload [options] -dump dbpath tbl [col1 col2 ...] [>outfile]

B.10.1.2 Keywords and Parameters

This section describes keywords and parameters that are available for the OLLOAD utility.

[options]

For a list of options, see Section B.10.1.2.1, "Options".

-load

To use the load utility.

-dump

To use the unload (dump) utility.

dbpath

The path to the Oracle Database Lite (.odb) file.

tbl

The table name. OLLOAD first attempts to find a table name in the user-specified case. If this fails, it searches for the upper-case of the user-specified name.

Note: The default user is SYSTEM. To specify an OLLOAD operation for another user name's tables, prefix the tbl parameter with the user name and a dot (.).

col1 col2

The column names. OLLOAD first attempts to find a column name in the user-specified case. If this fails, it searches for the upper-case of the user-specified name.

[datafile] [outfile]

The source or destination file for the load or unload operations. If you do not specify a datafile or outfile, OLLOAD displays the output on the screen.

B.10.1.2.1 Options

This section describes keyword and parameter options that are available for the OLLOAD utility.

-sep character

The field separator. If you do not specify this option, OLLOAD assumes that the separator character is a comma (,).

-quote character

The quote character. If you do not specify this option, OLLOAD assumes that the quote character is a single quote (').

-file filename

Use this option when loading and unloading data to specify the source or destination file name. When loading data, filename specifies the source file to load into Oracle Database Lite. When unloading (dumping) data, it is the destination file for the unloaded data.

Note: To unload data from Oracle Database Lite and load (or pipe) it to another Oracle Database Lite, do not specify a file name for this option. For a description of sample syntax, see "Examples".

-log logfile

Specify this option if you want to produce a log file listing rows that OLLOAD could not insert during load. If you do not specify a log file, loading stops at the first error.

-passwd passwd

The connection password for an encrypted database. You need to supply this password so that loading and unloading can occur.

-nosingle

Specify this option when you do not want to use single user mode. This degrades performance but allows other connections to the database.

-readonly

Specify this option when unloading data from a read-only Oracle Database Lite, for example, one located on a CD-ROM.

-commit count

Use this option if you want OLLOAD to commit after processing a specified number of rows. The default is 10000. OLLOAD prints an asterisk (*) to the screen each time it commits the specified number of rows. To disable the commit operation specify 0.

-mark count

Use this option if you want OLLOAD to print a dot on the screen after processing the specified number of records. The default is 1000. To disable this feature specify 0.

Data Parsing

Table B-8 lists examples for OLLOAD data parsing.

Table B-8 Data Parsing Examples

Input	Data	Explanation
'Redwood Shores, CA'	Redwood Shores CA	Enclosing the input string in quotes preserves spaces and punctuations within a string.
'O"Brien'	O'Brien	Represent a single quote with its escape sequence, two single quotes.
fire fly	firefly	Spaces in data that is not quoted is ignored.
,	NULL,NULL	Empty fields are NULL.
1,,3	1,NULL,3,NULL	Empty fields are NULL.
	[no row inserted]	Completely empty lines are ignored.

If there are more values than database columns, extra values are ignored. Any missing values at the end of the line are set to NULL.

OLLOAD Utility Restrictions

OLLOAD does not support tab-delimited input files and LONG datatypes.

Examples

olload -quote \" -file p_kakaku.csv -load c:\orant\oldb40\polite.odb skkm01

olload -dump c:\orant\oldb40\polite.odb emp empno ename | olload -load myfile.odb myemp

B.10.2 Oracle Database Lite Load Application Programming Interfaces (APIs)

This document describes the Oracle Database Lite Load APIs. Each section of this document presents a different topic. These topics include:

• Section B.10.2.1, "Overview"

• Section B.10.2.2, "Oracle Database Lite Load APIs"

• Section B.10.2.3, "File Format"

• Section B.10.2.4, "Limitations"

B.10.2.1 Overview

The Oracle Database Lite Load APIs allow you to load data from an external file into a table in Oracle Database Lite, or to unload (dump) data from a table in Oracle Database Lite to an external file. For information on using the command line tool OLLOAD, see Section B.10.1, "OLLOAD". You can use the API calls presented in this document to make your own customizations.

B.10.2.2 Oracle Database Lite Load APIs

The Oracle Database Lite Load APIs include:

• Section B.10.2.2.1, "Connecting to the Database: olConnect"

• Section B.10.2.2.2, "Disconnecting from the Database: olDisconnect"

• Section B.10.2.2.3, "Deleting All Rows from a Table: olTruncate"

• Section B.10.2.2.4, "Setting Parameters for Load and Dump Operations: olSet"

• Section B.10.2.2.5, "Loading Data: olLoad"

• Section B.10.2.2.6, "Dumping Data: olDump"

The normal mechanism for unloading and loading a table is as follows:

1. Declare local variable, DBHandle.

2. Connect to the database using olConnect.

3. Optionally, set parameters for load or unload.

4. Dump or load the data using olDump or olLoad. You may optionally delete all rows from a table by calling olTruncate.

5. Disconnect from the database using olDisconnect.

B.10.2.2.1 Connecting to the Database: olConnect

Use this API to connect to the database. This is the first API that you have to call. It creates a load and unload context that is used in subsequent APIs to influence the load and unload behavior. This returns an initialized database handle DBHandle.

Syntax

olError olConnect (char *database_path, char *password, DBHandle &dbh);

The arguments for olConnect are listed in Table B-9:

Table B-9 olConnect Arguments

Argument	Description
database_path	The full path to the database file (directory path and filename).
password	The password used for the encrypted database, for any other database the password = NULL.
dbh	The application handle for the current database connection. This allows multiple database connections for one application thread (each connection has a different handle).

Return Values

(short) integer error code

Values from -1 to -8999 are used for the error codes returned by the database, values from -9000 and below are used for olLoad-specific error codes.

B.10.2.2.2 Disconnecting from the Database: olDisconnect

Disconnects from the database.

Syntax

olError olDisconnect (DBHandle dbh);

The arguments for olDisconnect are listed in Table B-10:

Table B-10 olDisconnect Arguments

Argument	Description
dbh	The current application handle.

Return Value

(short) integer error code

B.10.2.2.3 Deleting All Rows from a Table: olTruncate

This API can be used to delete all rows from an existing table.

Note: Records removed from the server through a truncate command will not be removed from the client unless a complete refresh is triggered. The truncate command is considered a DDL operation. Consequently, the necessary DML triggers do not fire and therefore the operations are not logged for fast refresh.

Syntax

olError olTruncate (DBHandle  dbh, char* table );

The arguments for olTruncate are listed in Table B-11:

Table B-11 olTruncate Arguments

Argument	Description
dbh	The current application handle.
tablename	The name of the table in the form: owner_name.table_name. where owner_name is the name of the owner of the table.

Return Value

(short) integer error code

B.10.2.2.4 Setting Parameters for Load and Dump Operations: olSet

This is an optional API. This sets optional parameters for load and unload.

Syntax

olError olSet (DBHandle  dbh, char * parameter_name, char *parameter_value);

The arguments for olSet are listed in Table B-12:

Table B-12 olSet Arguments

Argument	Description
dbh	The current application handle.
parameter_name	The name of the given parameter. This is not case sensitive. See Section B.10.2.3.2, "Parameters" for a list of parameter names and their default values.
parameter_value	The value to be set. This is not case sensitive for most parameters.

Return Value

(short) integer error code

B.10.2.2.5 Loading Data: olLoad

OlLoad loads data from a file into a table using current parameter settings.

Syntax

olError olLoad (DBHandle dbh, char *table, char *file);

The arguments for olLoad are listed in Table B-13:

Table B-13 olLoad Arguments

Argument	Description
dbh	The current application handle.
table	The table information in the form: owner_name.table_name(col1,col2,...) where col1,col2,... is the list of column names to load. This allows you to load and dump certain columns instead of the entire table. If the entire table is to be dumped, the column list need not be specified.
file	The path to the file from which loading takes place.

Note: If table = NULL, olLoad tries to find the table description in the file header.

Return Value

(short) integer error code

B.10.2.2.6 Dumping Data: olDump

OlDump dumps data from a table into a file using current parameter settings.

Syntax

olError olDump (DBHandle dbh, char *table, char *file);

The arguments for olDump are listed in Table B-14:

Table B-14 olDump Arguments

Argument	Description
dbh	The current application handle.
table	The table information in the same form as olLoad.
file	The file to which dump data is written.

Return Value

(short) integer error code

B.10.2.2.7 Compiling

The declarations for the DBHandle, parameter constants and flags, and error message codes are given in the file olloader.h in the ORACLE_HOME\Mobile\SDK\include directory. For compilation of your product include olloader.h in your main source file.

B.10.2.2.8 Linking

Linking use the file olloader40.dll and the library file olloader40.lib. Include these files in your project settings.

B.10.2.3 File Format

The Oracle Database Lite Load APIs support three file formats FIXEDASCII, BINARY and CSV. Each file contains an optional header followed by zero or more rows of data.

B.10.2.3.1 Header Format

The header has the following format (comments are in bold):

$$OL_BH$$ [begins header]

VERSION=xx.xx.xx.xx   [version number]

TABLE=T1(C1, C2, ...)... [table name with list of column names dumped] 

FILEFORMAT=FIXEDASCII

SEPARATOR=,

[any other parameters in the parameter list can be listed here]

$$OL_EH$$ [ends header]

The following is a header example:

$$OL_BH$$

VERSION=01.01.01.01

TABLE=T1(EMPNO,SALARY)

FILEFORMAT=BINARY

BITARRAY=TRUE

HEADER=TRUE

RDONLY=FALSE

LOGFILE=

COMMITCOUNT=-1

NOSINGLE=TRUE

$$OL_EH$$

The header lines can be in any order and all lines except $$OL_BH$$ and $$OL_EH$$ can be considered optional. Although, during the dump, if the header flag is on, table information and all parameter settings are dumped into the header.

When executing load, parameter information in the header overwrites current parameter settings. If the table argument in olLoad is NULL, the table name and list of columns in the header prevails, otherwise the table argument of olLoad prevails over the header.

B.10.2.3.2 Parameters

Header file parameters listed in Table B-15 are not case sensitive.

Table B-15 Parameters

Parameter	Description
FILEFORMAT	Input and output file format. The following formats are supported: FixedASCII - text file with fixed field width for each datatype. CSV – comma separated values format. Binary - binary file format. These key word values are not case sensitive.
SEPARATOR	The separator between the values (one character), comma by default.
QUOTECHAR	The quote character for the string datatype values in the file, single quote (') by default.
LOGFILE	The log file name. NULL by default (no log file produced and loading stops at the first error).
NOSINGLE	FALSE for single user mode (the default), or TRUE for no single user mode.
READONLY	FALSE (the default). TRUE to dump the data from read-only database (such as CD-ROM).
COMMITCOUNT	The number of rows processed after which olLoad, olDump, and olTruncate commit. The default value is -1, not to commit at all. Value 0 commits at the end of the operation, and values above 0 commit after the specified number of rows.
HEADER	FALSE (the default). TRUE to create a header in the beginning of the file during olDump.
BITARRAY	TRUE (the default) to support writing and reading nulls in binary format. During the dump, a bit array with the null information is dumped before each row. For FALSE olDump provides an error trying to write nulls in binary.
NONULL	TRUE (the default) when trying to read or write nulls olLoad and olDump return an error. When the flag is set to FALSE nulls are supported, including binary format since the default BITARRAY value is TRUE.
DATEFORMAT	The string for which date and timestamp columns should be written into the file and read from the file in FIXED ASCII and CSV formats. Such formats as "YYYYMMDD", "YYYY-MM-DD", and "YYYY/MM/DD" are supported. The default value is empty string (which can also be set using NULL), and the default date format is "YYYY-MM-DD". (In Oracle mode, date is treated the same as timestamp so that the date format is the default timestamp format which is "YYYY-MM-DD HH:MM:SS.SSSSSS".)

B.10.2.3.3 Data Format

The data format can be comma separated value (CSV), fixed ASCII, or binary. The following cases apply:

• CSV Format: Each row of the table is represented as a separate line in the file. Each line is separated by a carriage return and a line feed character on the Windows platform. Each value in the row is separated by a separator character which by default is a comma.

  Each value is also quoted by a quote character. Nulls are represented by an empty quoted string " ". The number of quoted strings in the file should be the same as the number of columns in the table, olLoad gives an error otherwise.

• FixedAscii Format: Each row of the table is represented as a separate line in the file. Each line is separated by a carriage return and a line feed character on the Windows platform. Each line is of the same size. The datatype of a column governs its format or representation in the file. Nulls are represented by a string of n '\0' (null) characters, where n is the fixed size of the field. Table B-16 describes data representation for each data type. The total record length for each line in the file should be the same as the sum of field lengths (precision) of each column, otherwise olLoad returns an error.

Table B-16 Datatypes

Datatype	Description
CHAR(n)	Length of the field in n characters. Data is left aligned and padded with blanks on the right.
VARCHAR(n)	Length of the field in n characters. Data is left aligned. It is padded with a null byte ('\0').
NUMERIC(p,s)	The default mode: length of the field is p+1 characters if scale s is zero or is not present. Otherwise, the length of the field is (p+2) characters. The value is right aligned in the output field. Format is optional negative sign, followed by zeros if required, followed by significant digits. If there is no negative sign, then '0' instead, for example, Number(5,2) 12.3 -> ' 012.30' -12.3 -> '-012.30' 1.23 -> ' 001.23' -1.23 -> '-001.23' The custom mode: the field length is one less: p if scale is not present, or zero and p+1 otherwise. The actual number stored in the file is of type NUMERIC(p-1, s). Correspondingly, olDump gives an error trying to insert a number within the range of NUMERIC(p, s), but out of the range of NUMERIC(p-1, s). Therefore, the first character in the NUMERIC field must be '0' or '-'; olLoad gives an error otherwise.
DECIMAL(p,s)	The same as NUMERIC(p,s).
INTEGER	Length of the field is 11 characters. A negative sign or space followed by 10 digits. Leading digits are filled with zeros.
SMALLINT	Field length is 6 characters. Minus sign or space followed by 5 digits.
FLOAT	Field length is 23 characters. In Oracle mode, it is minus sign or space, followed by leading zeroes, followed by some number of digits, followed by dot, followed by some number of digits. For example: 0 -> ' 0000000000000000000000' -12.34 -> '-0000000000000000012.34' In SQL92 mode the E (exponent) is always present and there is only 1 digit before the decimal point. For example: 0 -> ' 00000000000000000000E0' -12.34 -> '-000000000000001.234E10'
REAL	The same format as for double precision except that the total field length is only 16 characters instead of 23.
DOUBLE PRECISION	Field length is 23 characters. Minus sign or space followed by 22 characters which are digits, dot, or E, floating point number followed by E, followed by the exponent digits. In Oracle mode, if the number is small enough to fit in the field without using the exponent, E is not used. In SQL92 mode, E is always used. There is always one meaningful digit before the floating point, except 0. For example, in SQL92 mode: 0 -> ' 00000000000000000000E0' -1.79E10 -> '-0000000000000001.79E10' 12 -> ' 00000000000000001.2E10' For example, in Oracle mode: 1.2E75 -> ' 00000000000000001.2E75' -1.33333 -> '-0000000000000001.33333' -1.79E10 -> '-0000000000017900000000'
DATE	In SQL92 mode: YYYY-MM-DD, 10 characters long, for example: October 1, 1999 -> 1999-10-01 In Oracle mode the date is dumped as timestamp. If it is not the default date format parameter, the date format corresponds to the specified date format string, for example: DATEFORMAT = "YYYYMMDD" October 1, 1999 -> 19991001
TIME	HH:MM:SS, 8 characters long, for example: 5:01:58 p.m. is 17:01:58
TIMESTAMP	Date format, space, time format, dot, 6 digits after dot (precision of microseconds), total length of 26 characters: YYYY-MM-DD HH:MM:SS.SSSSSS If it is not the default date format parameter, the timestamp format corresponds to the specified date format string. If no time is specified in the date format string, the time information in the timestamp is omitted when dumping into a file.

B.10.2.4 Limitations

Currently olLoad does not support the following features:

• Columns of the datatype Interval, Time with time zone, Timestamp with time zone, BLOB, and CLOB.

• Binary data is not supported.

• The only "var" type supported is varchar.