Oracle® Database Lite Developer's Guide
10g (10.2.0) Part No. B15920-01 |
|
Previous |
Next |
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". |
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:
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:
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.
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.
|
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.
Controls the display of timing statistics. ON displays timing statistics on each SQL command. OFF suppresses timing of each command.
The following sections describe start mSQL and how to access the database using mSQL for the Windows CE platform:
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.
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.
The Oracle Lite database format is the same on Windows 32 and Windows CE platforms. Manage your snapshots, as follows:
Create and test your snapshots on Windows 32 using the Windows 32 mSQL command-line utility.
Copy the database to the Windows CE platform.
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.
Description
Utility for creating a database.
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
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
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.
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".
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
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
|
|
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
To add a DSN using the ODBC Administrator:
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
Click Add.
Double-click the Oracle Database Lite nn ODBC Driver, where nn is the release number, from the list of Installed ODBC Drivers.
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.
Create the DSN as explained in Section B.7.1, "Adding a DSN Using the ODBC Administrator".
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 theODBC.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):
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. |
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 Using this option has no effect on applications that seldom update the database. Setting the transaction isolation level to |
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 This option is safe for pure relational applications. However, SQL applications that use |
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:
Changing this option does not compress or decompress any existing objects in the database. |
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
The following sections describe how to use the validatedb
utility:
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.
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.
To run the validatedb
utility, perform the following steps.
Click the validatedb
icon. The main validatedb
form appears. As Table B-7 describes, the validatedb
form contains the following items.
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, thevalidatedb utility stops functioning and does not respond.
|
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
.
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.
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:
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".
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]
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 isSYSTEM . 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.
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
This document describes the Oracle Database Lite Load APIs. Each section of this document presents a different topic. These topics include:
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.
The Oracle Database Lite Load APIs include:
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"
The normal mechanism for unloading and loading a table is as follows:
Declare local variable, DBHandle
.
Connect to the database using olConnect
.
Optionally, set parameters for load or unload.
Dump or load the data using olDump
or olLoad
. You may optionally delete all rows from a table by calling olTruncate
.
Disconnect from the database using olDisconnect
.
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.
Disconnects from the database.
Syntax
olError olDisconnect (DBHandle dbh);
The arguments for olDisconnect
are listed in Table B-10:
Return Value
(short) integer error code
This API can be used to delete all rows from an existing table.
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
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
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 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
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
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.
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.
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:
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".) |
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, |
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. |