Detailed Description

OCILIB (from version 3.2.0) support the OCI direct Path API.

Actual implementation of direct path API does not support the following elements :

Objects datatypes (User Defined Types and Object References)
Object tables
Nested tables
SQL String functions

All scalar datatypes (numerics, characters and date/time), including LOBs and LONG types are supported

Oracle direct API features (from Oracle Documentation)

The direct path load interface allows an application to access the direct path load engine of the Oracle database server to perform the functions of the Oracle SQL*Loader utility. This functionality provides the ability to load data from external files into Oracle database objects, either a table or a partition of a partitioned table. The OCI direct path load interface has the ability to load multiple rows by loading a direct path stream which contains data for multiple rows.

Oracle direct API limitation (from Oracle Documentation)

The direct path load interface has the following limitations which are the same as SQL*Loader:

triggers are not supported
check constraints are not supported
referential integrity constraints are not supported
clustered tables are not supported
loading of remote objects is not supported
user-defined types are not supported
LOBs must be specified after all scalar columns
LONGs must be specified last

Warning:

Its recommended to use direct path interface with an Oracle client that is the same version than the database. With version < 10g, it is mandatory regarding that it causes segmentation faults and it's known from Oracle that advices to use the same version for client and server (see metalink KB)

How to use direct path

1 : Create a direct path handle with OCI_DirPathCreate()
2 : Set (optional) some direct path load attributes
3 : Describe the columns to load with OCI_DirPathSetColumn()
4 : Populate data with OCI_DirPathSetEntry()
5 : Convert the data with OCI_DirPathConvert()
6 : Load the data into the database with OCI_DirPathLoad()
7 : Repeat step 4,5,6 + reset the stream with OCI_DirPathReset() until all rows has been loaded
8 : Commit the load with OCI_DirPathFinish()
9 : Free the direct path handle with OCI_DirPathFree()

Example

#include "ocilib.h"

#define SIZE_ARRAY 100
#define NB_LOAD    10
#define SIZE_COL1  20
#define SIZE_COL2  30
#define SIZE_COL3  8
#define NUM_COLS   3

int main(void)
{ 
    OCI_Connection *cn;
    OCI_DirPath    *dp;
    OCI_TypeInfo   *tbl;

    dtext val1[SIZE_COL1+1];
    dtext val2[SIZE_COL2+1];
    dtext val3[SIZE_COL3+1];

    int i = 0, j = 0, nb_rows = SIZE_ARRAY;
    boolean res = TRUE;
    
    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
       return EXIT_FAILURE;

    cn  = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    tbl = OCI_TypeInfoGet(cn, "test_directpath", OCI_TIF_TABLE);
    dp  = OCI_DirPathCreate(tbl, NULL, NUM_COLS, nb_rows);

    /* optional attributes to set */

    OCI_DirPathSetBufferSize(dp, 64000);
    OCI_DirPathSetNoLog(dp, TRUE);
    OCI_DirPathSetParallel(dp, TRUE);

    /* describe the target table */

    OCI_DirPathSetColumn(dp, 1, "VAL_INT",  SIZE_COL1, NULL);
    OCI_DirPathSetColumn(dp, 2, "VAL_STR",  SIZE_COL2, NULL);
    OCI_DirPathSetColumn(dp, 3, "VAL_DATE", SIZE_COL3, "YYYYMMDD");

    /* prepare the load */

    OCI_DirPathPrepare(dp);

    nb_rows = OCI_DirPathGetMaxRows(dp);          
    
    for (i = 0; i < NB_LOAD ; i++)
    {
        OCI_DirPathReset(dp);

        for (j = 1; j <= nb_rows; j++)
        {
            /* fill test values */

            sprintf(val1, "%04d", i + (i*100));
            sprintf(val2, "value %05d", j + (i*100));
            sprintf(val3, "%04d%02d%02d", (j%23)+1 + 2000, (j%11)+1, (j%23)+1);

            OCI_DirPathSetEntry(dp, j, 1, val1, (unsigned int) strlen(val1), TRUE);
            OCI_DirPathSetEntry(dp, j, 2, val2, (unsigned int) strlen(val2), TRUE);
            OCI_DirPathSetEntry(dp, j, 3, val3, (unsigned int) strlen(val3), TRUE);
        }
        
       /* load data to the server */

        while (res)
        {
            int state = OCI_DirPathConvert(dp);

            if ((state == OCI_DPR_FULL) || (state == OCI_DPR_COMPLETE))
                res = OCI_DirPathLoad(dp);

            if (state == OCI_DPR_COMPLETE)
                break;
        }
    }

    /* commits changes */

    OCI_DirPathFinish(dp);

    printf("%04d row(s) loaded\n", OCI_DirPathGetRowCount(dp));

    /* free direct path object */

    OCI_DirPathFree(dp);

    OCI_Cleanup();

   return EXIT_SUCCESS;
}

Functions
OCI_EXPORT OCI_DirPath *OCI_API	OCI_DirPathCreate (OCI_TypeInfo typinf, const mtext partition, unsigned int nb_cols, unsigned int nb_rows)
	Create a direct path object.
OCI_EXPORT boolean OCI_API	OCI_DirPathFree (OCI_DirPath *dp)
	Free an OCI_DirPath handle.
OCI_EXPORT boolean OCI_API	OCI_DirPathSetColumn (OCI_DirPath dp, unsigned int index, const mtext name, unsigned int maxsize, const mtext *format)
	Describe a column to load into the given table.
OCI_EXPORT boolean OCI_API	OCI_DirPathPrepare (OCI_DirPath *dp)
	Prepares the OCI direct path load interface before any rows can be converted or loaded.
OCI_EXPORT boolean OCI_API	OCI_DirPathSetEntry (OCI_DirPath dp, unsigned int row, unsigned int index, void value, unsigned size, boolean complete)
	Set the value of the given row/column array entry.
OCI_EXPORT unsigned int OCI_API	OCI_DirPathConvert (OCI_DirPath *dp)
	Convert user provided data to a direct path stream format.
OCI_EXPORT unsigned int OCI_API	OCI_DirPathLoad (OCI_DirPath *dp)
	Loads the data converted to direct path stream format.
OCI_EXPORT boolean OCI_API	OCI_DirPathReset (OCI_DirPath *dp)
	Reset internal arrays and streams to prepare another load.
OCI_EXPORT boolean OCI_API	OCI_DirPathFinish (OCI_DirPath *dp)
	Terminate a direct path operation and commit changes into the database.
OCI_EXPORT boolean OCI_API	OCI_DirPathAbort (OCI_DirPath *dp)
	Terminate a direct path operation without committing changes.
OCI_EXPORT boolean OCI_API	OCI_DirPathSave (OCI_DirPath *dp)
	Execute a data savepoint (server side).
OCI_EXPORT boolean OCI_API	OCI_DirPathFlushRow (OCI_DirPath *dp)
	Flushes a partially loaded row from server.
OCI_EXPORT boolean OCI_API	OCI_DirPathSetCurrentRows (OCI_DirPath *dp, unsigned int nb_rows)
	Set the current number of rows to convert and load.
OCI_EXPORT unsigned int OCI_API	OCI_DirPathGetCurrentRows (OCI_DirPath *dp)
	Return the current number of rows used in the OCILIB internal arrays of rows.
OCI_EXPORT unsigned int OCI_API	OCI_DirPathGetMaxRows (OCI_DirPath *dp)
	Return the maximum number of rows allocated in the OCI and OCILIB internal arrays of rows.
OCI_EXPORT boolean OCI_API	OCI_DirPathSetDateFormat (OCI_DirPath dp, const mtext format)
	Set the default date format string for input conversion.
OCI_EXPORT boolean OCI_API	OCI_DirPathSetParallel (OCI_DirPath *dp, boolean value)
	Set the parallel loading mode.
OCI_EXPORT boolean OCI_API	OCI_DirPathSetNoLog (OCI_DirPath *dp, boolean value)
	Set the logging mode for the loading operation.
OCI_EXPORT boolean OCI_API	OCI_DirPathSetCacheSize (OCI_DirPath *dp, unsigned int size)
	Set number of elements in the date cache.
OCI_EXPORT boolean OCI_API	OCI_DirPathSetBufferSize (OCI_DirPath *dp, unsigned int size)
	Set the size of the internal stream transfer buffer.
OCI_EXPORT unsigned int OCI_API	OCI_DirPathGetRowCount (OCI_DirPath *dp)
	Return the number of rows successfully loaded into the database so far.
OCI_EXPORT unsigned int OCI_API	OCI_DirPathGetAffectedRows (OCI_DirPath *dp)
	return the number of rows successfully processed during in the last conversion or loading call
OCI_EXPORT unsigned int OCI_API	OCI_DirPathGetErrorColumn (OCI_DirPath *dp)
	Return the column index which caused an error during data conversion.
OCI_EXPORT unsigned int OCI_API	OCI_DirPathGetErrorRow (OCI_DirPath *dp)
	Return the row index which caused an error during data conversion.

Function Documentation

OCI_EXPORT OCI_DirPath* OCI_API OCI_DirPathCreate	(	OCI_TypeInfo *	typinf,
		const mtext *	partition,
		unsigned int	nb_cols,
		unsigned int	nb_rows
	)

Create a direct path object.

Parameters:

	typinf	- Table type info handle
	partition	- Partition name
	nb_cols	- Number of columns to load
	nb_rows	- Maximum of rows to handle per load operation

Note:: Retrieve the table type info handle with OCI_TypeInfoGet(). The partition name is not mandatory; Parameter 'nb_rows' is ignored for Oracle 8i. Prior to Oracle 9i, it's the OCI client that decides of the number of rows to process per convert/load calls. From Oracle 9i, OCI allows application to specify this value. Note that, the OCI client might not accept the input value. After OCI_DirPathPrepare() has been successfully called, OCI_DirPathGetMaxRows() returns the final number of rows used for the given direct path operation.

Returns:: Return the direct path handle on success otherwise NULL on failure

Definition at line 46 of file dirpath.c.

References OCI_DirPathFree().

OCI_EXPORT boolean OCI_API OCI_DirPathFree ( OCI_DirPath * dp )

Free an OCI_DirPath handle.

Parameters:

dp

- Direct path Handle

Returns:: TRUE on success otherwise FALSE

Definition at line 208 of file dirpath.c.

Referenced by OCI_DirPathCreate().

OCI_EXPORT boolean OCI_API OCI_DirPathSetColumn	(	OCI_DirPath *	dp,
		unsigned int	index,
		const mtext *	name,
		unsigned int	maxsize,
		const mtext *	format
	)

Describe a column to load into the given table.

Parameters:

	dp	- Direct path Handle
	index	- Column index
	name	- Column name
	maxsize	- Maximum input value size for a column entry
	format	- Date or numeric format to use

Note:

An error is thrown if :

If the column specified by the 'name' parameter is not found in the table referenced by the type info handle passed to OCI_DirPathCreate()
the index is out of bounds (= 0 or >= number of columns)

Returns:: TRUE on success otherwise FALSE

Definition at line 242 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathPrepare ( OCI_DirPath * dp )

Prepares the OCI direct path load interface before any rows can be converted or loaded.

Parameters:

dp

- Direct path Handle

Returns:: TRUE on success otherwise FALSE

Definition at line 531 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathSetEntry	(	OCI_DirPath *	dp,
		unsigned int	row,
		unsigned int	index,
		void *	value,
		unsigned	size,
		boolean	complete
	)

Set the value of the given row/column array entry.

Parameters:

	dp	- Direct path Handle
	row	- Row index
	index	- Column index
	value	- Value to set
	size	- Size of the input value
	complete	- Is the entry content fully provided ?

Note:

Rows and columns indexes start at 1.

The 'size' parameter is expressed in number of :

bytes for binary columns
characters for other columns

Direct path support piece loading for LONGs and LOBs columns. When filling these columns, it's possible to provide input buffer piece by piece. In order to do so :

set the 'complete' parameter to FALSE
set the 'size' parameter to the piece size
Repeat calls to OCI_DirPathSetEntry() until the data is totally provided
The last call that set the last piece or an entry must specify the value TRUE for the 'complete' parameter

Returns:: TRUE on success otherwise FALSE

OCI_EXPORT unsigned int OCI_API OCI_DirPathConvert ( OCI_DirPath * dp )

Convert user provided data to a direct path stream format.

Parameters:

dp

- Direct path Handle

Note:

if the call does return another result than OCI_DPR_COMPLETE :

OCI_DirPathGetErrorRow() returns the row where the error occured
OCI_DirPathGetErrorColumn() returns the column where the error occured

OCI_DirPathGetAffectedRows() returns the number of rows processed in the last call.

Returns:

Possible return values :

OCI_DPR_COMPLETE : load has been successful
OCI_DPR_ERROR : an error happened while loading data
OCI_DPR_FULL : the internal stream is full
OCI_DPR_PARTIAL : a column hasn't been fully filled yet
OCI_DPR_EMPTY : no data was found to load

Definition at line 819 of file dirpath.c.

OCI_EXPORT unsigned int OCI_API OCI_DirPathLoad ( OCI_DirPath * dp )

Loads the data converted to direct path stream format.

Parameters:

dp

- Direct path Handle

Note:: OCI_DirPathGetAffectedRows() returns the number of rows successfully loaded in the last call.

Returns:

Possible return values :

OCI_DPR_COMPLETE : conversion has been successful
OCI_DPR_ERROR : an error happened while converting data
OCI_DPR_FULL : the internal stream is full
OCI_DPR_PARTIAL : a column hasn't been fully filled yet
OCI_DPR_EMPTY : no data was found to load

Definition at line 949 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathReset ( OCI_DirPath * dp )

Reset internal arrays and streams to prepare another load.

Parameters:

dp

- Direct path Handle

Note:: Once some data have been converted or loaded, OCI_DirPathReset() resets internal OCI structures in order to prepare another load operation (set entries, convert and load)

Returns:: TRUE on success otherwise FALSE

Definition at line 783 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathFinish ( OCI_DirPath * dp )

Terminate a direct path operation and commit changes into the database.

Parameters:

dp

- Direct path Handle

Warning:: The direct path handle cannot be used anymore after this call for any more loading operations and must be freed with OCI_DirPathFree().

Note:: Some properties functions of the direct path handle, such as OCI_DirPathGetRowCount() can be called on a terminated direct path handle

Returns:: TRUE on success otherwise FALSE

Definition at line 1017 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathAbort ( OCI_DirPath * dp )

Terminate a direct path operation without committing changes.

Parameters:

dp

- Direct path Handle

Note:: Any pending loaded data are cancelled. Any load completion operations, such as index maintenance operations, are not performed.

Warning:: The direct path handle cannot be used anymore after this call for any more loading operations and must be freed with OCI_DirPathFree().

Returns:: TRUE on success otherwise FALSE

Definition at line 1047 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathSave ( OCI_DirPath * dp )

Execute a data savepoint (server side).

Parameters:

dp

- Direct path Handle

Note:: Executing a data savepoint is not allowed for LOBs

Returns:: TRUE on success otherwise FALSE

Definition at line 1077 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathFlushRow ( OCI_DirPath * dp )

Flushes a partially loaded row from server.

Parameters:

dp

- Direct path Handle

Returns:: TRUE on success otherwise FALSE

Definition at line 1104 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathSetCurrentRows	(	OCI_DirPath *	dp,
		unsigned int	nb_rows
	)

Set the current number of rows to convert and load.

Parameters:

	dp	- Direct path Handle
	nb_rows	- Number of row to process

Warning:: An OCILIB error will be thrown if the value exceeds the maximum number of rows in the internals arrays

Returns:: TRUE on success otherwise FALSE

Definition at line 1131 of file dirpath.c.

OCI_EXPORT unsigned int OCI_API OCI_DirPathGetCurrentRows ( OCI_DirPath * dp )

Return the current number of rows used in the OCILIB internal arrays of rows.

Parameters:

dp

- Direct path Handle

Returns:: Internal current array size on SUCCESS otherwise 0

Definition at line 1156 of file dirpath.c.

OCI_EXPORT unsigned int OCI_API OCI_DirPathGetMaxRows ( OCI_DirPath * dp )

Return the maximum number of rows allocated in the OCI and OCILIB internal arrays of rows.

Parameters:

dp

- Direct path Handle

Returns:: Internal maximum array size on SUCCESS otherwise 0

Definition at line 1172 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathSetDateFormat	(	OCI_DirPath *	dp,
		const mtext *	format
	)

Set the default date format string for input conversion.

Parameters:

	dp	- Direct path Handle
	format	- date format

Note:

For string to date conversion, Oracle uses :

Column date format
Default date format (modified by this call)
Default global support environment setting

Returns:: TRUE on success otherwise FALSE

Definition at line 1188 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathSetParallel	(	OCI_DirPath *	dp,
		boolean	value
	)

Set the parallel loading mode.

Parameters:

	dp	- Direct path Handle
	value	- enable/disable parallel mode

Note:: Default value is FALSE.; Setting the value to TRUE allows multiple load sessions to load the same segment concurrently

Parallel loading mode (From Oracle documentation)

A direct load operation requires that the object being loaded is locked to prevent DML on the object. Note that queries are lock-free and are allowed while the object is being loaded.

For a table load, if the option is set to:
- FALSE, then the table DML X-Lock is acquired.
- TRUE, then the table DML S-Lock is acquired.
For a partition load, if the option is set to:
- FALSE, then the table DML SX-Lock and partition DML X-Lock is acquired.
- TRUE, then the table DML SS-Lock and partition DML S-Lock is acquired.

Returns:: TRUE on success otherwise FALSE

Definition at line 1225 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathSetNoLog	(	OCI_DirPath *	dp,
		boolean	value
	)

Set the logging mode for the loading operation.

Parameters:

	dp	- Direct path Handle
	value	- enable/disable logging

Logging mode (from Oracle Documentation)

The NOLOG attribute of each segment determines whether image redo or invalidation redo is generated:

FALSE : Use the attribute of the segment being loaded.
TRUE : No logging. Overrides DDL statement, if necessary.

Returns:: TRUE on success otherwise FALSE

Definition at line 1256 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathSetCacheSize	(	OCI_DirPath *	dp,
		unsigned int	size
	)

Set number of elements in the date cache.

Parameters:

	dp	- Direct path Handle
	size	- Buffer size

Note:: Default value is 0.; Setting the value to 0 disables the cache

Returns:: TRUE on success otherwise FALSE

Definition at line 1287 of file dirpath.c.

OCI_EXPORT boolean OCI_API OCI_DirPathSetBufferSize	(	OCI_DirPath *	dp,
		unsigned int	size
	)

Set the size of the internal stream transfer buffer.

Parameters:

	dp	- Direct path Handle
	size	- Buffer size

Note:: Default value is 64KB.

Returns:: TRUE on success otherwise FALSE

Definition at line 1339 of file dirpath.c.

OCI_EXPORT unsigned int OCI_API OCI_DirPathGetRowCount ( OCI_DirPath * dp )

Return the number of rows successfully loaded into the database so far.

Parameters:

dp

- Direct path Handle

Note:: Insertions are committed with OCI_DirPathFinish()

Definition at line 1370 of file dirpath.c.

OCI_EXPORT unsigned int OCI_API OCI_DirPathGetAffectedRows ( OCI_DirPath * dp )

return the number of rows successfully processed during in the last conversion or loading call

Parameters:

dp

- Direct path Handle

Note:: This function called after :

OCI_DirPathConvert(), returns the number of converted rows
OCI_DirPathload(), returns the number of loaded rows

Definition at line 1386 of file dirpath.c.

OCI_EXPORT unsigned int OCI_API OCI_DirPathGetErrorColumn ( OCI_DirPath * dp )

Return the column index which caused an error during data conversion.

Parameters:

dp

- Direct path Handle

Warning:: Direct path column indexes start at 1.

Note:: The internal value is reset to 0 when calling OCI_DirPathConvert()

Returns:: 0 is no error occurs otherwise the index of the given column which caused an error

Definition at line 1402 of file dirpath.c.

OCI_EXPORT unsigned int OCI_API OCI_DirPathGetErrorRow ( OCI_DirPath * dp )

Return the row index which caused an error during data conversion.

Parameters:

dp

- Direct path Handle

Warning:: Direct path row indexes start at 1.

Note:: The internal value is reset to 0 when calling OCI_DirPathConvert()

Returns:: 0 is no error occurs otherwise the index of the given row which caused an error

Definition at line 1418 of file dirpath.c.

Direct Path loading

Detailed Description

Functions

Function Documentation