Oracle® Database Lite Developer's Guide
10g (10.0.0) Part No. B13788-01 |
|
![]() Previous |
![]() Next |
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 the Oracle Database Lite Tools and Utilities Guide. You can use the API calls presented in this document to make your own customizations.
The Oracle Database Lite Load APIs include:
Section B.2.2, "Disconnecting from the Database: olDisconnect"
Section B.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-1:
Table B-1 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-2:
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-3:
Table B-3 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-4:
Table B-4 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.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-5:
Table B-5 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-6:
Table B-6 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-7 are not case sensitive.
Table B-7 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:
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.
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-8 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-8 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. |