OCILIB (C Driver for Oracle) 3.9.1
Functions
Direct Path loading

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 :

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
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 205 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 239 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 527 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_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 821 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 948 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 785 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 1015 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 1079 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 1106 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 1133 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 1158 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 1174 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 1190 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 1226 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 1257 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 1288 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 1340 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 1371 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 1387 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 1403 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 1419 of file dirpath.c.