Skip Headers
Oracle® Database Lite Tools and Utilities Guide
10g (10.0.0)
Part No. B12263-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

3 Database Tools and Utilities For Win32 and WinCE Platforms

This chapter describes how to use the following database utilities for the Windows 32 and Windows CE platforms. As described in Table 3-1, the utility names are listed in alphabetical order.

Table 3-1 Database Tools and Utilities

Utility Description
MSQL Allows users to execute SQL statements against the local database.
ValidateDB Allows you to inspect and diagnose database corruptions.
Support for Linguistic Sort Allows databases to be created with linguistic sort capability enabled.
CREATEDB Use this to create Oracle Database Lite databases.
DECRYPDB Use this to decrypt your Oracle Database Lite databases.
dropjava This is a command-line utility you can use to remove Java classes from Oracle Database Lite. For more information, see the Oracle Database Lite Developer’s Guide for Java.
ENCRYPDB Use this to encrypt your Oracle Database Lite.
loadjava This is a command-line utility you can use to load a Java class into Oracle Database Lite. For more information, see the Oracle Database Lite Developer’s Guide for Java.
MIGRATE Use this to migrate to Oracle Database Lite from a previous release.
Mobile SQL Mobile SQL is a command line interface that allows you to connect to Oracle Database Lite databases.
ODBC Administrator and the Oracle 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.
ODBINFO Use this utility to find out the version number and volume ID of an Oracle Database Lite database.
OLLOAD Use this command-line tool 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.
REMOVEDB Use this to remove Oracle Database Lite databases.
VALIDATEDB Use this to validate the structure of an Oracle Lite database.

3.1 MQSL

Mobile SQL 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.

Mobile SQL allows you to create, access, and manipulate Oracle Database Lite on Palm Computing platform devices. Using Mobile SQL you can accomplish the following:

The following sections describe how to use the MSQL tool. Topics include:

3.1.1 MSQL for Windows 32

The following sections describe how to populate your database using Mobile SQL and use Mobile SQL. Topics include:

3.1.1.1 Populating your Database Using Mobile SQL

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 Mobile SQL prompt by typing:

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

You can also type:

SQL> START <filename>


Note:

You do not need to include the .sqlz file extension when running the script.

3.1.1.2 Mobile SQL

Mobile SQL is an application that runs as a command line interface. 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 following sections describe information relevant to database access, starting Mobile SQL and Mobile SQL commands. Topics include:

3.1.1.3 Database Access

Mobile SQL 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.

3.1.1.4 Starting Mobile SQL

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

3.1.1.5 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.

3.1.1.6 SET TIMING {ON|OFF}

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

3.1.1.7 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.

3.1.2 MSQL for Windows CE

The Oracle Database Lite database format is the same for Windows 32 and Windows CE. You can create and test your snapshots on Windows 32 using the Windows 32 MSQL command line. You can then copy the database to the Windows CE platform. Use the Windows CE MSQL to manipulate the database that is on your device.

The following sections enable you to use the Mobile SQL application. Topics include:

3.1.2.1 Using Mobile SQL

Mobile SQL is an application that runs as a command line interface. It allows the user to execute SQL statements against the local database. It allows users to access functionality provided by the interfaces of the underlying Oracle Database Lite database engine.

The following sections describe how to access the database and start Mobile SQL. Topics include:

3.1.2.2 Database Access

Mobile SQL 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.

3.1.2.3 Starting Mobile SQL

Mobile SQL is started by opening the &fmv107;\Mobile\Sdk\WinCE, select the folder representing the version Windows CE, then 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.

3.2 Database Tools and Utilities for PALM

This section describes how to use the ValidateDB utility. Topics include:

3.2.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 run the ValidateDB utility to check the database for consistency. The Oracle Database Lite development group then uses the validateDB utility to diagnose the extent of corruption and fixes the problem.

3.2.2 Installing ValidateDB

To install the validateDB utility, you just need to install a single file named validatedb.prc. It is included on your system during installation and is located in the directory named Lite\Runtime. Using the HotSync application, you must install this file on your Palm device or emulator (using the right-click menu).

3.2.3 Running ValidateDB

To run the validateDB utility, perform the following steps.

  1. Click the ValidateDB icon. The main ValidateDB form appears. As Table 3-2 describes, the ValidateDB form contains the following items.

    Table 3-2 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 required 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 validating the chosen database(s), the ValidateDB utility displays the following alerts:

  • No Errors Found - This alert indicates that no corruption has been detected.

  • CorruptedDB - This alert indicates that the utility has detected some corruption and the databases need to be sent to the Oracle Database Lite development group for further investigation.

  • System Fatal - This alert indicates that the utility has detected system fatal alerts and the databases need to be sent to the Oracle Database Lite development group for further investigation. You should reset the device if you receive a system fatal alert.

  • Assertion - This alert indicates that the utility has detected some errors and the databases need to be sent to Oracle Database Lite development group for further investigation.


Note:

Do not select the "Log to Desktop" box as it is used primarily by Oracle Database Lite developers to log further debugging information to a desktop computer. If you select this box, the validateDB utility stops functioning and does not respond.

3.2.4 Sending Corrupted Databases

If the validateDB utility detects corruption in the database, it is mandatory that you send all such databases to the Oracle Database Lite development group. 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.

3.2.5 Must Send Databases

This section lists databases that must be sent along with the database that has been detected as corrupt.

  1. All databases which appear in the list on the validateDB form.

  2. Databases which 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, ... These are Oracle Database Lite extensions for large databases.

  3. okSysDB.PDB

  4. okTransLog.PDB

To fix corrupted databases, the Oracle Database Lite development group retrieves further debugging information using the validateDB utility during the problem diagnosis phase.

3.3 CREATEDB

Description

Utility for creating a database.

Syntax

CREATEDB DataSourceName DatabaseName [[[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 3.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.

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.

DATABASE_SIZE

The database size in bytes.

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.

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 3-3:

Table 3-3 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

createdb polite db1

createdb polite c:\testdir\db2.odb 300

createdb polite polite french

3.4 DECRYPDB

Description

This tool allows you to decrypt an encrypted Oracle Database Lite. For more information, see Section 3.5, "ENCRYPDB".

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 3-4:

Table 3-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 3.5, "ENCRYPDB".

3.5 ENCRYPDB

Description

This tool allows you to encrypt Oracle Database Lite with a password and to change a database password. The password prevents unauthorized access to the database and encrypts the database, so that the data stored in the database files cannot be interpreted. For more information, see Section 3.4, "DECRYPDB".

ENCRYPDB uses CAST5 encryption, which is a 128-bit, DES compliant encryption scheme.

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:

Comments

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

Table 3-5 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:

3.5.1 Synchronizing with an Encrypted Database

These steps are required to synchronize with an encrypted Oracle Database Lite database.

  1. Retrieve the user password from the Mobile Server Repository.

  2. Convert the password into uppercase. For example, change "manager" into "MANAGER".

  3. Launch Mobile Sync (msync.exe) and execute a sync. Provide username, password and Mobile Server URL. Select Apply and then select Sync. This creates an non-encrypted Oracle Database Lite database.

  4. Encrypt the Oracle Database Lite database using the ENCRYPDB utility. Enter the converted uppercase password, for example MANAGER.

  5. Continue to synchronize.

3.6 MIGRATE

Description

Utility for migrating a database from a previous version of Oracle Lite to Oracle Database Lite 10g. The utility migrates your Oracle Database Lite 3.6 database and makes a backup copy with a .36 extension. If you have an earlier release of Oracle Database Lite, see the Oracle Database Lite Installation and Configuration Guide for Windows NT/2000/XP for more information.

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 DB Name 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

3.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 its online help for this information.

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

Table 3-6 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

3.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 (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.

3.7.2 Adding a DSN which points to Read-Only Media (CD-ROM)

  1. Create the DSN as explained in Section 3.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.


3.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 3-7 with the ODBINFO utility:

Table 3-7 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.


3.9 OLLOAD

For information on the Oracle Database Lite Load APIs, refer the Oracle Database Lite Developer’s Guide.

Description

This command line tool allows you to load data from an external file into a table in Oracle Database Lite, or to unload (dump) data from a table in the Oracle Database Lite database to an external file. Unlike SQL*Loader, OLLOAD does not use a control file 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 (,). The records can also include fields with values that are quoted strings. The default is a single quote ('). See Data Parsing for more information on data parsing.

Syntax

To load a datafile:

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

To unload (dump) to an outfile

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

Keywords and Parameters

[options]

See Options for a list of options.

-load

To use the load utility.

-dump

To use the unload (dump) utility.

dbpath

The path to the Oracle Database Lite file (.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 uppercase of the user-specified name.


Note:

The default user is "SYSTEM". If you want 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 name(s). OLLOAD first attempts to find a column name in the user-specified case. If this fails, it searches for the uppercase of the user-specified name.

[datafile] [outfile]

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

Options

-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 the Oracle Database Lite database. When unloading (dumping) data, it is the destination file for the unloaded data.


Important:

To unload data from an Oracle Database Lite and load (or pipe) it to another Oracle Database Lite database, do not specify a file name for this option. See the second example in Examples for sample syntax.

-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 database, for example, one located on a CD-ROM.

-commit count

Use this option if you want OLLLOAD to commit after processing a specified number of rows. The default is 10000. OLLLOAD 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.

Comments

Data Parsing

Table 3-8 shows examples for OLLOAD data parsing:

Table 3-8 Data Parsing Examples

Input Data Explanation
'Redwood Shores, CA' Redwood Shores, CA Enclosing the input string in quotes preserves spaces and punctuation within the 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 are 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

3.10 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

3.11 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:

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