13 Direct Path Loading

Direct Path Context

This handle must be allocated for each object, either a table or a partition of a partitioned table, being loaded. Because a OCIDirPathCtx handle is the parent handle of the OCIDirPathFuncCtx, OCIDirPathColArray, and OCIDirPathStream handles, freeing a OCIDirPathCtx handle frees its child handles also (although for good coding practices, free child handles individually before you free the parent handle).

A direct path context is allocated with OCIHandleAlloc(). Note that the parent handle of a direct path context is always the environment handle. A direct path context is freed with OCIHandleFree(). Include the header files in the first two lines in all direct path programs:

...
#include <cdemodp0.h>
#include <cdemodp.h>

OCIEnv *envp;
OCIDirPathCtx *dpctx;
sword error;
error = OCIHandleAlloc((void  *)envp, (void  **)&dpctx,
                    OCI_HTYPE_DIRPATH_CTX, (size_t)0,(void  **)0);
...
error = OCIHandleFree(dpctx, OCI_HTYPE_DIRPATH_CTX);

OCI Direct Path Function Context

This handle, of type OCIDirPathFuncCtx, is used to describe the following named type and REF columns:

• Column objects. The function context here describes the object type, which will be used as the default constructor to construct the object, and the object attributes of the constructor.

• REF columns. The function context here describes a single object table (optional) to reference row objects from, and the REF arguments that identify the row object.

• SQL string columns. The function context here describes a SQL string and its arguments to compute the value to be loaded into the column.

The handle type OCI_HTYPE_DIRPATH_FN_CTX is passed to OCIHandleAlloc() to indicate that a function context is to be allocated, as in the following example.

OCIDirPathCtx *dpctx;       /* direct path context */
OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
sword error;

error = OCIHandleAlloc((void  *)dpctx, (void  **)&dpfnctx,
                       OCI_HTYPE_DIRPATH_FN_CTX,
                       (size_t)0, (void  **)0);

Note that the parent handle of a direct path function context is always the direct path context handle. A direct path function context handle is freed with:

error = OCIHandleFree(dpfnctx, OCI_HTYPE_DIRPATH_FN_CTX);

Direct Path Column Array and Direct Path Function Column Array

These handles are used to present an array of rows to the direct path interface. A row is represented by three arrays: column values, column lengths, and column flags. Methods used on a column array include: allocate the array handle and set or get values corresponding to an array entry.

Both handles share the same data structure, OCIDirPathColArray. But these column array handles differ in parent handles and handle types.

A direct path column array handle is allocated with OCIHandleAlloc(). The following code fragment shows explicit allocation of the direct path column array handle:

OCIDirPathCtx *dpctx;      /* direct path context */
OCIDirPathColArray *dpca;  /* direct path column array */
sword error;
error = OCIHandleAlloc((void  *)dpctx, (void  **)&dpca,
               OCI_HTYPE_DIRPATH_COLUMN_ARRAY, 
               (size_t)0, (void  **)0);

A direct path column array handle is freed with OCIHandleFree().

error = OCIHandleFree(dpca, OCI_HTYPE_DIRPATH_COLUMN_ARRAY);

A direct path function column array handle is allocated in almost the same way:

OCIDirPathFuncCtx *dpfnctx;   /* direct path function context */
OCIDirPathColArray *dpfnca;   /* direct path function column array */
sword error;
error = OCIHandleAlloc((void  *)dpfnctx, (void  **)&dpfnca,
                        (ub4)OCI_HTYPE_DIRPATH_FN_COL_ARRAY,
                        (size_t)0, (void  **)0);

A direct path function column array is freed with OCIHandleFree():

error = OCIHandleFree(dpfnca, OCI_HTYPE_DIRPATH_FN_COL_ARRAY);

Freeing a OCIDirPathColArray handle also frees the column array associated with the handle.

Direct Path Stream

This handle is used by the conversion operation, OCIDirPathColArrayToStream(), and by the load operation, OCIDirPathLoadStream().

Direct path stream handles is allocated by the client with OCIHandleAlloc(). The structure of a OCIDirPathStream handle can be thought of as a pair in the form (buffer, buffer length).

A direct path stream is a linear representation of Oracle table data. The conversion operations always append to the end of the stream. Load operations always start from the beginning of the stream. After a stream is completely loaded, the stream must be reset by calling OCIDirPathStreamReset().

The following example shows a direct path stream handle allocated with OCIHandleAlloc(). The parent handle is always an OCIDirPathCtx handle:

OCIDirPathCtx *dpctx;    /* direct path context */
OCIDirPathStream *dpstr; /* direct path stream */
sword error;
error = OCIHandleAlloc((void  *)dpctx, (void  **)&dpstr,
               OCI_HTYPE_DIRPATH_STREAM, (size_t)0,(void  **)0);

A direct path stream handle is freed using OCIHandleFree().

error = OCIHandleFree(dpstr, OCI_HTYPE_DIRPATH_STREAM);

Freeing an OCIDirPathStream handle also frees the stream buffer associated with the handle.

Data Structures Used in Direct Path Loading Example

The following data structure is used in the example.

/* load control structure */
struct loadctl
{
  ub4                 nrow_ctl;            /* number of rows in column array */
  ub2                 ncol_ctl;         /* number of columns in column array */
  OCIEnv             *envhp_ctl;                       /* environment handle */
  OCIServer          *srvhp_ctl;                            /* server handle */
  OCIError           *errhp_ctl;                             /* error handle */
  OCIError           *errhp2_ctl;                /* yet another error handle */
  OCISvcCtx          *svchp_ctl;                          /* service context */
  OCISession         *authp_ctl;                   /* authentication context */
  OCIParam           *colLstDesc_ctl;        /* column list parameter handle */
  OCIDirPathCtx      *dpctx_ctl;                      /* direct path context */
  OCIDirPathColArray *dpca_ctl;           /* direct path column array handle */
  OCIDirPathColArray *dpobjca_ctl;          /* dp column array handle for obj*/
  OCIDirPathColArray *dpnestedobjca_ctl;  /* dp col array hndl for nested obj*/
  OCIDirPathStream   *dpstr_ctl;                /* direct path stream handle */
  ub1                *buf_ctl;    /* pre-alloc'd buffer for out-of-line data */
  ub4                 bufsz_ctl;                 /* size of buf_ctl in bytes */
  ub4                 bufoff_ctl;                     /* offset into buf_ctl */
  ub4                *otor_ctl;                  /* Offset to Recnum mapping */
  ub1                *inbuf_ctl;                 /* buffer for input records */
  struct pctx         pctx_ctl;                     /* partial field context */
  boolean             loadobjcol_ctl;             /* load to obj col(s)? T/F */
};

The header file cdemodp.h, which is from the demo directory, defines several structs:

#ifndef cdemodp_ORACLE
# define cdemodp_ORACLE

# include <oratypes.h>

# ifndef externdef
#  define externdef
# endif

/* External column attributes */
struct col
{
  text *name_col;                                             /* column name */
  ub2   id_col;                                            /* column load id */
  ub2   exttyp_col;                                         /* external type */
  text *datemask_col;                             /* datemask, if applicable */
  ub1   prec_col;                                /* precision, if applicable */
  sb1   scale_col;                                   /* scale, if applicable */
  ub2   csid_col;                                        /* character set id */
  ub1   date_col;            /* is column a chrdate or date? 1=TRUE. 0=FALSE */
  struct obj * obj_col;          /* description of object, if applicable */
#define COL_OID 0x1                                         /* col is an OID */
  ub4   flag_col;
};

/* Input field descriptor
 * For this example (and simplicity),
 * fields are strictly positional.
 */
struct fld
{
  ub4  begpos_fld;                             /* 1-based beginning position */
  ub4  endpos_fld;                             /* 1-based ending    position */
  ub4  maxlen_fld;                       /* max length for out of line field */
  ub4    flag_fld;
#define FLD_INLINE            0x1
#define FLD_OUTOFLINE         0x2
#define FLD_STRIP_LEAD_BLANK  0x4
#define FLD_STRIP_TRAIL_BLANK 0x8
};

struct obj
{
  text               *name_obj;                                /* type  name*/
  ub2                 ncol_obj;              /* number of columns in col_obj*/
  struct col         *col_obj;                          /* column attributes*/
  struct fld         *fld_obj;                           /* field descriptor*/
  ub4                 rowoff_obj;  /* current row offset in the column array*/
  ub4                 nrows_obj;              /* number of rows in col array*/
  OCIDirPathFuncCtx  *ctx_obj;       /* Function context for this obj column*/
  OCIDirPathColArray *ca_obj;           /* column array  for this obj column*/
  ub4                 flag_obj;                              /* type of obj */
#define OBJ_OBJ  0x1                                             /* obj col */
#define OBJ_OPQ  0x2                                  /* opaque/sql str col */
#define OBJ_REF  0x4                                             /* ref col */
};

struct tbl
{
  text        *owner_tbl;                                     /* table owner */
  text        *name_tbl;                                       /* table name */
  text        *subname_tbl;                        /* subname, if applicable */
  ub2          ncol_tbl;                     /* number of columns in col_tbl */
  text        *dfltdatemask_tbl;            /* table level default date mask */
  struct col  *col_tbl;                                 /* column attributes */
  struct fld  *fld_tbl;                                  /* field descriptor */
  ub1          parallel_tbl;                         /* parallel: 1 for true */
  ub1          nolog_tbl;                          /* no logging: 1 for true */
  ub4          xfrsz_tbl;                   /* transfer buffer size in bytes */
  text         *objconstr_tbl;   /* obj constr/type if loading a derived obj */
};

struct sess                        /* options for a direct path load session */
{
  text        *username_sess;                                        /* user */
  text        *password_sess;                                    /* password */
  text        *inst_sess;                            /* remote instance name */
  text        *outfn_sess;                                /* output filename */
  ub4          maxreclen_sess;          /* max size of input record in bytes */
};


#endif                                              /* cdemodp_ORACLE */

Outline of an Example of a Direct Path Load for Scalar Columns

The following sample code illustrates the use of several of the OCI direct path interfaces. It is not a complete code example.

The init_load function performs a direct path load using the direct path API on the table described by tblp. The loadctl structure given by ctlp has an appropriately initialized environment and service context. A connection has been made to the server.

STATICF void
init_load(ctlp, tblp)
struct loadctl *ctlp;
struct tbl     *tblp;
{
  struct  col   *colp;
  struct  fld   *fldp;
  sword          ociret;                       /* return code from OCI calls */
  OCIDirPathCtx *dpctx;                               /* direct path context */
  OCIParam      *colDesc;                     /* column parameter descriptor */
  ub1            parmtyp;
  ub1           *timestamp = (ub1 *)0;
  ub4            size;
  ub4            i;
  ub4            pos;

  /* allocate and initialize a direct path context */
  /* See cdemodp.c for the definition of OCI_CHECK */
  OCI_CHECK(ctlp->envhp_ctl, OCI_HTYPE_ENV, ociret, ctlp,
            OCIHandleAlloc((void  *)ctlp->envhp_ctl,
                           (void  **)&ctlp->dpctx_ctl,
                           (ub4)OCI_HTYPE_DIRPATH_CTX,
                           (size_t)0, (void  **)0));

  dpctx = ctlp->dpctx_ctl;                                      /* shorthand */

  OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIAttrSet((void  *)dpctx, (ub4)OCI_HTYPE_DIRPATH_CTX,
                       (void  *)tblp->name_tbl,
                       (ub4)strlen((const char *)tblp->name_tbl),
                       (ub4)OCI_ATTR_NAME, ctlp->errhp_ctl));

Additional attributes, such as OCI_ATTR_SUB_NAME and OCI_ATTR_SCHEMA_NAME, are also set here. After the attributes have been set, prepare the load.

OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIDirPathPrepare(dpctx, ctlp->svchp_ctl, ctlp->errhp_ctl));

OCI_CHECK(ctlp->envhp_ctl, OCI_HTYPE_ENV, ociret, ctlp,
            OCIHandleAlloc((void  *)ctlp->dpctx_ctl, (void  **)&ctlp->dpca_ctl,
                           (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
                           (size_t)0, (void  **)0));

  OCI_CHECK(ctlp->envhp_ctl, OCI_HTYPE_ENV, ociret, ctlp,
            OCIHandleAlloc((void  *)ctlp->dpctx_ctl,(void  **)&ctlp->dpstr_ctl,
                           (ub4)OCI_HTYPE_DIRPATH_STREAM,
                           (size_t)0, (void  **)0));

OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIAttrGet(ctlp->dpca_ctl, (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
                       &ctlp->nrow_ctl, 0, OCI_ATTR_NUM_ROWS,
                       ctlp->errhp_ctl));

   OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIAttrGet(ctlp->dpca_ctl, (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
                       &ctlp->ncol_ctl, 0, OCI_ATTR_NUM_COLS,
                       ctlp->errhp_ctl));

ub4            rowoff;                          /* column array row offset */
ub4            clen;                                      /* column length */
ub1            cflg;                                  /* column state flag */
ub1           *cval;                             /* column character value */

OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIDirPathColArrayEntrySet(ctlp->dpca_ctl, ctlp->errhp_ctl,
                                       rowoff, colp->id_col,
                                       cval, clen, cflg));

(void) OCIDirPathColArrayReset(ctlp->dpca_ctl, ctlp->errhp_ctl);

(void) OCIDirPathStreamReset(ctlp->dpstr_ctl, ctlp->errhp_ctl);

ub4            rowcnt;                   /* number of rows in column array */
ub4            startoff;         /* starting row offset into column array  */

/* convert array to stream, filter out bad records */
ocierr = OCIDirPathColArrayToStream(ctlp->dpca_ctl, ctlp->dpctx_ctl,
                                        ctlp->dpstr_ctl, ctlp->errhp_ctl,
                                        rowcnt, startoff);

/* load the stream */
ociret = OCIDirPathLoadStream(ctlp->dpctx_ctl, ctlp->dpstr_ctl,
            ctlp->errhp_ctl);

/* free up server data structures for the load */
OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
          OCIDirPathFinish(ctlp->dpctx_ctl, ctlp->errhp_ctl));

ociret = OCIHandleFree((void  *)ctlp->dpca_ctl,
                           OCI_HTYPE_DIRPATH_COLUMN_ARRAY);
ociret = OCIHandleFree((void  *)ctlp->dpstr_ctl,
                           OCI_HTYPE_DIRPATH_STREAM);
ociret = OCIHandleFree((void  *)ctlp->dpctx_ctl, 
                          OCI_HTYPE_DIRPATH_CTX);

OCI_ATTR_DIRPATH_DCACHE_SIZE

This attribute, when not equal to 0, sets the date cache size (in elements) for a table. For example, if the date cache size is set to 200, then at most 200 unique date or timestamp values can be stored in the cache. The date cache size cannot be changed once OCIDirPathPrepare() has been called. The default value is 0, meaning a date cache will not be created for a table. A date cache will be created for a table only if one or more date or timestamp values are loaded that require datatype conversions and the attribute value is nonzero.

OCI_ATTR_DIRPATH_DCACHE_NUM

This attribute is used to query the current number of entries in a date cache.

OCI_ATTR_DIRPATH_DCACHE_MISSES

This attribute is used to query the current number of date cache misses. If this number is high, consider tuning the application with a larger date cache size. If increasing the date cache size doesn't cause this number to decrease significantly, the date cache should probably not be used. Date cache misses are expensive, due to hashing and look up times.

OCI_ATTR_DIRPATH_DCACHE_HITS

This attribute is used to query the number of date cache hits. This number should be relatively large in order to see any benefit of using the date cache support.

OCI_ATTR_DIRPATH_DCACHE_DISABLE

Setting this attribute to 1 indicates that the date cache should be disabled if the size is exceeded. Note that this attribute cannot be changed or set after OCIDirPathPrepare() has been called.

The default (= 0) is to not disable a cache on overflow. When not disabled, the cache is searched to avoid conversions, but overflow input date value entries will not be added to the date cache, and will be converted using expensive date conversion functions. Again, excessive date cache misses can cause the application to run slower than not using the date cache at all.

This attribute can also be queried to see if a date cache has been disabled due to overflow.

Describing a Nested Table Column and Its Nested Table

Loading the parent table with a nested table column is a separate action from loading the child nested table.

• To load the parent table with a nested-table column:

  1. Describe the parent table and its columns as usual, except:

  2. When describing the nested-table column, this is the column that stores the SETIDs. Its external datatype is SQLT_CHR if the SETIDs in the data file are in characters, SQLT_BIN if binary.

• To load the nested table (child):

  1. Describe the nested table and its columns as usual.

  2. The SETID column is required.

     • Set its OCI_ATTR_NAME using a dummy name (for example "setid") because the API does not expect you to know its system name.

     • Set the column attribute with OCI_ATTR_DIRPATH_SID to indicate that this is a SETID column:

       ub1 flg = 1;
       sword error;
       
       error = OCIAttrSet((void  *)colDesc,
                          OCI_DTYPE_PARAM,
                          (void  *)&flg, (ub4)0,
                          OCI_ATTR_DIRPATH_SID, ctlp->errhp_ctl);
       

Describing a Column Object

To describe a column object and its object attributes, use a direct path function context. Describing a column object requires setting its object constructor. Describing object attributes is similar to describing a list of scalar columns.

To describe a column object:

1. Allocate a parameter handle on the column object with OCI_DTYPE_PARAM. This parameter handle is used to set the column's external attributes.

2. Set the column name and its other external column attributes (for example, maximum data size, precision, scale).

3. Set the external type as SQLT_NTY (named type) with OCI_ATTR_DATA_TYPE.

4. Allocate a direct path function context handle. This context will be used to describe the column's object type and attributes:

OCIDirPathFuncCtx *dpfnctx  /* direct path function context */;
sword error;
error = OCIHandleAlloc((void  *)dpctx, (void  **)&dpfnctx,
               OCI_HTYPE_DIRPATH_FN_CTX, 
               (size_t)0, (void  **)0);

5. Set the column's object type name (for example, "Employee") with OCI_ATTR_NAME in the function context:

OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
text *obj_type;   /* column object's object type */
sword error;

error = OCIAttrSet((void  *)dpfnctx,
                   OCI_HTYPE_DIRPATH_FN_CTX,
                   (void  *)obj_type, (ub4)strlen((const char *)obj_type),
                   OCI_ATTR_NAME, ctlp->errhp_ctl);

6. Set the expression type, OCI_ATTR_DIRPATH_EXPR_TYPE, to be OCI_DIRPATH_EXPR_OBJ_CONSTR. This indicates that the expression set with OCI_ATTR_NAME will be used as the default object constructor:

OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
ub1 expr_type = OCI_DIRPATH_EXPR_OBJ_CONSTR;
sword error;

error = OCIAttrSet((void  *)dpfnctx,
                   OCI_HTYPE_DIRPATH_FN_CTX,
                   (void  *)&expr_type, (ub4)0,
                   OCI_ATTR_DIRPATH_EXPR_TYPE,
                   ctlp->errhp_ctl);

7. Set the number of columns or object attributes that will be loaded for this column object using OCI_ATTR_NUM_COLS.

8. Get the column/attribute parameter list for the function context OCIDirPathFuncCtx.

9. For each object attribute:

1. Get the column descriptor for the object attribute with OCI_DTYPE_PARAM.

2. Set the attribute's column name with OCI_ATTR_NAME.

3. Set the external column type (the type of the data that will be passed to the direct path API) with OCI_ATTR_DATA_TYPE.

4. Set any other external column attributes (maximum data size, precision, scale, and so on.)

5. If this attribute column is a column object, then do steps 3-10 for its object attributes.

6. Free the handle to the column descriptor.

10. Set the function context OCIDirPathFuncCtx that was created in step 4 into the parent column object's parameter handle with OCI_ATTR_DIRPATH_FN_CTX.

Allocating the Array Column for the Column Object

When loading a column object, the data for its object attributes will be loaded into a separate column array created just for that object. A child column array is allocated for each column object, whether it is nested or not. Each row of object attributes in the child column array maps back to the corresponding non-NULL row of its parent column object in the parent column array.

Use the column object's direct path function context handle and column array type OCI_HTYPE_DIRPATH_FN_COL_ARRAY.

To allocate a child column array for a column object:

OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
OCIDirPathColArray *dpfnca;   /* direct path function column array */
sword error;

error = OCIHandleAlloc((void  *)dpfnctx, (void  **)&dpfnca,
                       OCI_HTYPE_DIRPATH_FN_COL_ARRAY,
                       (size_t)0, (void  **)0);

Loading Column Object Data into the Column Array

If a column is scalar, its value is set in the column array by passing the address of its value to OCIDirPathColArrayEntrySet(). And if a column is an object, the address of its child column array handle is passed instead. The child column array will contain the data of the object attributes.

To load data into a column object:

(Start.) For each column object:

1. If the column is non-NULL:

   1. For each of its object attribute columns:

      If an object attribute is a nested column object, then go to (Start.) and do this entire procedure recursively.

      Set the data in the child column array using OCIDirPathColArrayEntrySet().

   2. Set the column object's data in the column array by passing the address of its child column array handle to OCIDirPathColArrayEntrySet().

2. Else if the column is NULL:

   • Set the column object's data in the column array by passing a NULL address for the data, a length of 0, and an OCI_DIRPATH_COL_NULL flag to OCIDirPathColArrayEntrySet().

OCI_DIRPATH_COL_ERROR

This value is passed to OCIDirPathColArrayEntry() to indicate that the current column array row should be ignored. A typical use of this value is to back out all previous conversions for a row when an error occurs providing more data for a partial column (OCI_NEED_DATA was returned from the previous OCIDirPathColArrayToStream() call). Any previously converted data placed in the output stream buffer for the current row is removed. Conversion then continues with the next row in the column array. The purged row is counted in the converted row count.

When OCI_DIRPATH_COL_ERROR is specified, the current row is ignored, as well as are any corresponding rows in any child column arrays referenced, starting from the top level column array row. Any NULL child column array references are ignored when moving all referenced child column arrays to their next row.

Describing a SQL String Column

1. Allocate a parameter handle on the SQL string column with OCI_DTYPE_PARAM. This parameter handle is used to set the column's external attributes.

2. Set the column name and its other external column attributes (for example, maximum data size, precision, scale).

3. Set the SQL string column's external type as SQLT_NTY with OCI_ATTR_DATA_TYPE.

4. Allocate a direct path function context handle. This context will be used to describe the arguments of the SQL string.

   OCIDirPathFuncCtx *dpfnctx  /* direct path function context */;
   sword error;
   error = OCIHandleAlloc((void  *)dpctx, (void  **)&dpfnctx,
                  OCI_HTYPE_DIRPATH_FN_CTX, 
                  (size_t)0, (void  **)0);
   
   

5. Set the column's SQL string in OCI_ATTR_NAME in the function context.

   OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
   text *sql_str;   /* column's SQL string expression */
   sword error;
   
   error = OCIAttrSet((void  *)dpfnctx,
                      OCI_HTYPE_DIRPATH_FN_CTX,
                      (void  *)sql_str, (ub4)strlen((const char *)sql_str),
                      OCI_ATTR_NAME, ctlp->errhp_ctl);
   
   

6. Set the expression type, OCI_ATTR_DIRPATH_EXPR_TYPE, to be OCI_DIRPATH_EXPR_SQL. This indicates that the expression set with OCI_ATTR_NAME will be used as the SQL string to derive the value from.

   OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
   ub1 expr_type = OCI_DIRPATH_EXPR_SQL;
   sword error;
   
   error = OCIAttrSet((void  *)dpfnctx,
                      OCI_HTYPE_DIRPATH_FN_CTX,
                      (void  *)&expr_type, (ub4)0,
                      OCI_ATTR_DIRPATH_EXPR_TYPE, ctlp->errhp_ctl);
   
   

7. Set the number of arguments that will be passed to the SQL string with OCI_ATTR_NUM_COLS.

8. Get the column/attribute parameter list for the function context.

9. For each SQL string argument:

   1. Get the column descriptor for the object attribute with OCI_DTYPE_PARAM.

   2. The order in which the SQL string arguments are defined does not matter. The order does not have to match the order used in the SQL string.

   3. Set the attribute's column name with OCI_ATTR_NAME.

   4. There is a naming convention for SQL string arguments.

   5. The argument names must match the bind variable names used in the SQL string in content but not in case. For example, if the SQL string is "substr(:INPUT_STRING, 3, 5)", then it is acceptable if you give the argument name as "input_string".

   6. If an argument is used multiple times in an SQL string, declaring it once and counting it as one argument only is correct.

   7. Set the external column type (the type of the data that will be passed to the direct path API) with OCI_ATTR_DATA_TYPE.

   8. Set any other external column attributes (maximum data size, precision, scale, and so on).

   9. Free the handle to the column descriptor.

10. Set the function context OCIDirPathFuncCtx that was created in step 4 into the parent column object's parameter handle with OCI_ATTR_DIRPATH_FN_CTX.

Allocating the Column Array for SQL String Columns

When loading a SQL string column, the data for its arguments will be loaded into a separate column array created just for that SQL string column. A child column array is allocated for each SQL string column. Each row of arguments in the child column array maps back to the corresponding non-NULL row of its parent SQL string column in the parent column array.

To allocate a child column array for a SQL string column:

OCIDirPathFuncCtx *dpfnctx;        /* direct path function context */
OCIDirPathColArray *dpfnca;   /* direct path function column array */
sword error;

error = OCIHandleAlloc((void  *)dpfnctx, (void  **)&dpfnca, 
                       OCI_HTYPE_DIRPATH_FN_COL_ARRAY,
                       (size_t)0, (void  **)0);

Loading the SQL String Data into the Column Array

If a column is scalar, its value would be set in the column array by passing the address of its value to OCIDirPathColArrayEntrySet(). If a column is of a SQL string type, the address of its child column array handle would be passed instead. The child column array would contain the SQL string's argument data.

To load data into a SQL string column:

For each SQL string column:

1. If the column is non-NULL:

   1. For each of its function argument columns:

      Set the data in the child column array using OCIDirPathColArrayEntrySet().

   2. Set the SQL string column's data into the column array by passing the address of its child column array handle to OCIDirPathColArrayEntrySet().

2. Else if the column is NULL:

   Set the SQL string column data into the column array by passing a NULL address for the data, a length of 0, and an OCI_DIRPATH_COL_NULL flag to OCIDirPathColArrayEntrySet().

This process is similar to that for column objects.

Describing the REF Column

Describing the arguments to a REF column is similar to describing the list of columns to be loaded for a table.

1. Get a parameter handle on the REF column with OCI_DTYPE_PARAM. This parameter handle is used to set the column's external attributes.

2. Set the column name and its other external column attributes (for example, maximum data size, precision, scale).

3. Set the REF column's external type as SQLT_REF with OCI_ATTR_DATA_TYPE.

4. Allocate a direct path function context handle. This context is used to describe the REF column's arguments.

   OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
   sword error;
   
   error = OCIHandleAlloc((void  *)dpctx, (void  **)&dpfnctx,
                          OCI_HTYPE_DIRPATH_FN_CTX,
                          (size_t)0, (void  **)0);
   
   

5. OPTIONAL: Set the REF column's table name in OCI_ATTR_NAME in the function context. See the next step for more details.

   OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
   text *ref_tbl;     /* column's reference table */
   sword error;
   
   error = OCIAttrSet((void  *)dpfnctx,
                      OCI_HTYPE_DIRPATH_FN_CTX,
                      (void  *)ref_tbl, (ub4)strlen((const char *)ref_tbl),
                      OCI_ATTR_NAME, ctlp->errhp_ctl);
   
   

6. OPTIONAL: Set the expression type, OCI_ATTR_DIRPATH_EXPR_TYPE, to be OCI_DIRPATH_EXPR_REF_TBLNAME. Set this only if step 5 was done. This indicates that the expression set with OCI_ATTR_NAME will be used as the object table to reference row objects from. This parameter is optional. The behavior for this parameter varies for the REF type.

   • Unscoped REF columns (unscoped, system-OID-based):

     If not set, then by the definition of an "unscoped" REF column, this REF column is required to have a reference table name as its argument for every data row.

     If set, this REF column can only refer to row objects from this specified object table for the duration of the load. And the REF column is not allowed to have a reference table name as its argument. (The direct path API is providing this parameter as a short cut to users who will be loading to an unscoped REF column that refers to the same reference object table during the entire load.)

   • Scoped REF columns (scoped, system-OID-based and primary-key-based):

     If not set, the direct path API will use the reference table specified in the schema.

     If set, the reference table name must match the object table specified in the schema for this scoped REF column. An error occurs if the table names do not match.

     Whether this parameter is set or not, it does not matter to the API whether this reference table name is in the data row or not. If the name is in the data row, it has to match the table name specified in the schema. If it is not in the data row, the API will use the reference table specified in the schema.

7. Set the number of REF arguments that will be used to reference a row object. with OCI_ATTR_NUM_COLS. The number of arguments required varies for the REF column type. This number is derived from step 6 earlier.

   • Unscoped REF columns (unscoped, system-OID-based REF columns):

     One if OCI_DIRPATH_EXPR_REF_TBLNAME is used. None for the reference table name, and one for the OID value.

     Two if OCI_DIRPATH_EXPR_REF_TBLNAME is not used. One for the reference table name, and one for the OID value.

   • Scoped REF columns (scoped, system-OID-based and primary-key-based):

     N or N+1 are acceptable, where N is the number of columns making up the object id, regardless if OCI_DIRPATH_EXPR_REF_TBLNAME is used or not. Minimum is N if the reference table name is not in the data row. It's N+1 if the reference table name is in the data row. Note: If the REF is system-OID-based, then N is one. If the REF is primary-key-based, then N is the number of component columns that make up the primary key. If the reference table name is in the data row, then add one to N.

     Note:

     To simplify the error message if you were to pass in a number of REF arguments other than N or N+1, the error message will say that it found so-and-so number of arguments when it expects N. Although N+1 is not stated in the message, N+1 is acceptable (even though the reference table name is not needed) and will not invoke an error message.

8. Get the column/attribute parameter list for the function context.

9. For each REF argument or attribute:

   1. Get the column descriptor for the REF argument using OCI_DTYPE_PARAM.

   2. Set the attribute's column name using OCI_ATTR_NAME.

      The order of the REF arguments given matter. The reference table name comes first, if given. The object id, whether it is system-generated or primary-key-based, comes next.

      There is a naming convention for the REF arguments. Since the reference table name is not a table column, you can use any dummy names for its column name, such as "ref-tbl". For a system-generated OID column, you can use any dummy names for its column name, such as. "sys-OID". For a primary-key-based object id, list all the primary-key columns to load into. There is no need to create a dummy name for OID. The component column names, if given (see short cut note later), can be given in any order.

      Do not set the attribute column name(s) for the object id if you want to use the short cut.

      Short cut. If loading a system-OID-based REF column, do not set the column name with a name. The API will figure it out. But you will still have to set other column attributes, such as external datatype.

      If loading a primary-key REF column and its primary key consists of multiple columns, the short cut is not to set their column names. But you will still have to set other column attributes, such as external datatype.

      Note:

      If the component column names are NULL, then the API code determines the column names in the position or order in which they were defined for the primary key. So, when you set column attributes other than the name, make sure the attributes are set for the component columns in the correct order.

   3. Set the external column type (the type of the data that will be passed to the direct path API) using OCI_ATTR_DATA_TYPE.

   4. Set any other external column attributes (max data size, precision, scale, and so on).

   5. Free the handle to the column descriptor.

   6. Set the function context OCIDirPathFuncCtx that was created in step 4 in the parent column object's parameter handle using OCI_ATTR_DIRPATH_FN_CTX.

Allocating the Column Array for a REF Column

To allocate a child column array for a REF column:

OCIDirPathFuncCtx *dpfnctx; /* direct path function context */
OCIDirPathColArray *dpfnca; /* direct path function column array */
sword error;

error = OCIHandleAlloc((void  *)dpfnctx, (void  **)&dpfnca,
                       OCI_HTYPE_DIRPATH_FN_COL_ARRAY,
                       (size_t)0, (void  **)0);

Loading the REF Data into the Column Array

If a column is scalar, its value would be set in the column array by passing the address of its value to OCIDirPathColArrayEntrySet(). If a column is a REF, the address of its child column array handle would be passed instead. The child column array will contain the REF arguments' data.

To load data into a REF column:

For each REF column:

1. If the column is non-NULL:

   1. For each of its REF argument columns:

      Set its data in the child column array using OCIDirPathColArrayEntrySet().

   2. Set the REF column's data into the column array by passing the address of its child column array handle to OCIDirPathColArrayEntrySet().

2. Else if the column is NULL:

   Set the REF column's data into the column array by passing a NULL address for the data, length of 0, and a OCI_DIRPATH_COL_NULL flag to OCIDirPathColArrayEntrySet().

   See Also:

   "OCI_DIRPATH_COL_ERROR"

Inheritance Hierarchy Diagram

In this example, Person is at the top of the hierarchy. It has two sub-types, Employee and Student. ParttimeEmployee is a sub-type of Employee. Therefore, the types which can be stored in a Person column are shown in this diagram:

Person (Name, Address)
                |            |
                |            |
   Student(Units, GPA)      Employee (Manager, Deptid)
                             |
                             |
                            ParttimeEmployee (Hours)

When loading a table which contains a column of type Person, the actual set of types could include any of these four: the NOT FINAL type Person, and its three sub-types: Student, Employee, and ParttimeEmployee. The direct path API only supports the loading of one fixed, derived type to this NOT FINAL column for the duration of this load. Thus, the API needs to know which one of these types will be loaded, the attributes to load for this type, and the function used to create this type.

Describing a Fixed, Derived Type to be Loaded

To describe a NOT FINAL or substitutable object and REF columns of a fixed, derived type:

To describe a NOT FINAL column of type X (where X is object or REF), refer to previous sections to describe a FINAL column of this type. Because the derived type (could be a supertype or a subtype) is fixed for the duration of the load, the client interface for describing a NOT FINAL column is the same as for a FINAL column.

A subtype can be thought of as a flattened representation of all the object attributes that are unique to this type plus all the attributes of its ancestors. Therefore, any of these attribute columns that are to be loaded into will have to be described and counted.

Allocating the Column Array

This is the same as for a FINAL column of the same type.

Loading the Data into the Column Array

This is the same as for a FINAL column of the same type.

Describing an Object Table

Describing an object table is very similar to describing a non-object table. Each object attribute is a column in the table. The only difference is that you may need to describe the OID, which could be system-generated, user-generated, or primary-key based.

To describe an object table:

For each object attribute column:

Describe each object attribute column as it needs to be described, depending on its type (for example, NUMBER, REF):

For the object table OID (Oracle Internet Directory):

1. If the object id is system-generated:

   Nothing extra to do. The system will generate OIDs for each row object.

2. If the object id is user-generated:

   1. Use a dummy name to represent the column name for the OID (for example, "cust_oid").

   2. Set the OID column attribute with OCI_ATTR_DIRPATH_OID.

3. If the object id is primary-key-based:

   1. All of the primary-key columns making up the OID must be loaded.

   2. Do not set OCI_ATTR_DIRPATH_OID, because no OID column with a dummy name was created.

Allocating the Column Array for the Object Table

This is the same as allocating a column array for a non-object table.

OCIDirPathColArray *dpca;  /* direct path column array */
sword error;

error = OCIHandleAlloc((void  *)dpctx, (void  **)&dpca,
                       OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
                       (size_t)0, (void  **)0);

Loading Data into the Column Array

This is the same as loading data into a non-object table.

Describing a NOT FINAL Object Table

Describing a NOT FINAL object table of a fixed derived type is very similar to describing a FINAL object table.

To describe a NOT FINAL object table of a fixed derived type:

1. Set the object table's object type in the direct path context with OCI_ATTR_DIRPATH_OBJ_CONSTR. This indicates that the object type, whether it is a supertype or a derived type, will be used as the default object constructor when loading to this table for the duration of the load.

   text *obj_type;            /* the object type to load into this NOT FINAL */
                              /* object table */
   sword error;
   
   error = OCIAttrSet((void  *)dpctx,
                      OCI_HTYPE_DIRPATH_CTX,
                      (void  *) obj_type,
                      (ub4)strlen((const char *) obj_type),
                      OCI_ATTR_DIRPATH_OBJ_CONSTR, ctlp->errhp_ctl);
   
   

2. For each of the object attribute columns to be loaded, describe them according to their datatypes. Describe the object id, if needed. This is the same as describing a FINAL object table.

Allocating the Column Array for the NOT FINAL Object Table

This is the same as for a FINAL object table.

OCI_ATTR_DIRPATH_OBJ_CONSTR

Indicates the object type to load into a NOT FINAL object table.

ttext *obj_type;            /* the object type to load into this NOT FINAL */
                           /* object table */
sword error;

error = OCIAttrSet((void  *)dpctx,
                   OCI_HTYPE_DIRPATH_CTX,
                   (void  *) obj_type,
                   (ub4)strlen((const char *) obj_type),
                   OCI_ATTR_DIRPATH_OBJ_CONSTR, ctlp->errhp_ctl);

OCI_ATTR_DIRPATH_OBJ_CONSTR

Indicates the object type to load into a substitutable object table.

text *obj_type; /* stores an object type name */
sword error;

error = OCIAttrSet((void  *)dpctx,
                   OCI_HTYPE_DIRPATH_CTX,
                   (void  *) obj_type,
                   (ub4)strlen((const char *) obj_type),
                   OCI_ATTR_DIRPATH_OBJ_CONSTR, ctlp->errhp_ctl);

OCI_ATTR_NAME

When a function context is created, set OCI_ATTR_NAME equal to the expression that describes the non-scalar column. Then set an OCI attribute to indicate the type of the expression. The expression type varies as follows:

1. Column objects:

   1. This required expression is the object type name. The object type will be used as the default object constructor.

   2. Set the expression type OCI_ATTR_DIRPATH_EXPR_TYPE to OCI_DIRPATH_EXPR_OBJ_CONSTR to indicate this expression is an object type name.

2. REF columns:

   1. This optional expression is the reference table name. This table is the object table from which the REF column will be referencing row objects.

   2. Set the expression type OCI_ATTR_DIRPATH_EXPR_TYPE to OCI_DIRPATH_EXPR_REF_TBLNAME to indicate this expression is a reference object table.

   3. The behavior for this parameter, set or not set, varies for each REF type.

      • Unscoped REF columns (unscoped, system-OID-based):

      • If not set, then by the definition of an "unscoped" REF column, this REF column is required to have a reference table name as its argument for every data row.

      • If set, this REF column can only refer to row objects from this specified object table for the duration of the load. And the REF column is not allowed to have a reference table name as its argument. (Direct path API is providing this parameter as a short cut to the users who will be loading to an unscoped REF column that refers to the same reference object table during the entire load.)

      • Scoped REF columns (scoped, system-OID-based and primary-key-based):

      • If not set, the direct path API will use the reference table specified in the schema.

      • If set, the reference table name must match the object table specified in the schema for this scoped REF column. An error occurs if the table names do not match.

      • Whether this parameter is set or not, it will not matter to the API whether this reference table name is in the data row or not. If the name is in the data row, it has to match the table name specified in the schema. If it is not in the data row, the API will use the reference table defined in the schema.

3. SQL string columns:

   This mandatory expression contains a SQL string to derive the value that will be stored in the column.

   Set the expression type OCI_ATTR_DIRPATH_EXPR_TYPE to OCI_DIRPATH_EXPR_SQL to indicate that this expression is a SQL string.

OCI_ATTR_DIRPATH_EXPR_TYPE

This attribute is used to indicate the type of the expression specified in OCI_ATTR_NAME for the non-scalar column's function context.

If OCI_ATTR_NAME is set, then OCI_ATTR_DIRPATH_EXPR_TYPE is required.

The possible values for OCI_ATTR_DIRPATH_EXPR_TYPE are:

1. OCI_DIRPATH_EXPR_OBJ_CONSTR

   • Indicates that the expression is an object type name and will be used as the default object constructor for a column object.

   • Required for column objects.

2. OCI_DIRPATH_EXPR_REF_TBLNAME

   • Indicates that the expression is a reference object table name. This table is the object table from which the REF column will be referencing row objects.

   • Optional for REF columns.

3. OCI_DIRPATH_EXPR_SQL

   • Indicates that the expression is a SQL string, which is executed to derive a value to be stored in the column.

   • Required for SQL string columns.

The following pseudocode example illustrates the preceding rules:

OCIDirPathFuncCtx  *dpfnctx; /* function context for this non-scalar column */
ub1 expr_type; /* expression type */
sword error;

if (...) /* (column type is an object) */
expr_type = OCI_DIRPATH_EXPR_OBJ_CONSTR;
...
if (...) /* (column_type is a REF && function context name exists) */
expr_type = OCI_DIRPATH_EXPR_REF_TBLNAME;
...
if (...) /* (column_type is a SQL string) */
expr_type = OCI_DIRPATH_EXPR_SQL;
...
error = OCIAttrSet((void  *)(dpfnctx),
                   OCI_HTYPE_DIRPATH_FN_CTX,
                   (void  *)&expr_type, (ub4)0,
                   OCI_ATTR_DIRPATH_EXPR_TYPE, ctlp->errhp_ctl);

OCI_ATTR_NUM_COLS

This attribute describes the number of attributes or arguments that will be loaded or processed for a non-scalar column. This parameter must be set before the column list can be retrieved.

1. Column objects:

   The number of object attribute columns to be loaded for this column object.

2. SQL string columns:

   1. The number of arguments to be passed to the SQL string.

   2. If an argument is used multiple times in the function, counting it as one is correct.

3. REF columns:

   1. The number of REF arguments to identify the row object the REF column should point to.

   2. The number of arguments required varies for the REF column type:

   • Unscoped REF columns (unscoped, system-OID-based REF columns):

     If OCI_DIRPATH_EXPR_REF_TBLNAME is used. None for the reference table name, and one for the OID value. (Only the OID values will be in the data rows.)

     If OCI_DIRPATH_EXPR_REF_TBLNAME is not used. One for the reference table name, and one for the OID value. (Both the reference table names and the OID values will be in the data rows.)

   • Scoped REF columns (scoped, system-OID-based and primary-key-based):

     N or N+1 are acceptable, where N is the number of columns making up the object id, regardless if OCI_DIRPATH_EXPR_REF_TBLNAME is used or not. The minimum is N if the reference table name is not in the data row. Use N+1 if the reference table name is in the data row.

     If the REF is system-OID-based, then N is one. If the REF is primary-key-based, then N is the number of component columns that make up the primary key. If the reference table name is in the data row, then add one to N.

OCI_ATTR_NUM_ROWS

This attribute, when used for a OCI_HTYPE_DIRPATH_FN_CTX (function context), is retrievable only, and cannot be set by the user. You can only use this attribute in OCIAttrGet() and not OCIAttrSet(). When called with OCIAttrGet(), the number of rows loaded so far is returned.

However, the attribute OCI_ATTR_NUM_ROWS, when used for a OCI_HTYPE_DIRPATH_CTX (table-level context), can be set and can be retrieved by the user.

Calling OCIAttrSet() with OCI_ATTR_NUM_ROWS and OCI_HTYPE_DIRPATH_CTX sets the number of rows to be allocated for the table-level column array. If not set, the direct path API code will derive a "reasonable" number based on the maximum record size and the transfer buffer size. To see how many rows were allocated, call OCIAttrGet() with OCI_ATTR_NUM_ROWS on OCI_HTYPE_DIRPATH_COLUMN_ARRAY for a table-level column array, and with OCI_HTYPE_DIRPATH_FN_COL_ARRAY for a function column array.

Calling OCIAttrGet() with OCI_ATTR_NUM_ROWS and OCI_HTYPE_DIRPATH_CTX returns the number of rows loaded so far.

This attribute cannot be set by the user for a function context. You are not allowed to specify the number of rows desired in a function column array through OCI_ATTR_NUM_ROWS with OCIAttrSet() because then all function column arrays will have the same number of rows as the table-level column array. Thus this attribute can only be set for a table-level context and not for a function context.

OCI_ATTR_NAME

The naming conventions when loading nested tables, object tables, SQL string columns, and REF columns are described in the following paragraphs.

In general, a dummy column name is used if are loading data into a column that is a system column with a system name that you are not aware of (for example, an object table's system-generated object id (OID) column or a nested table's SETID (SID) column) or if a column is an argument that doesn't have a database table column (for example, SQL string and REF arguments).

If the column is a database table column, but a dummy name was used, then a column attribute has to be set so that the function can identify the column even though it's not under the name known to the database.

The naming rules are:

1. Child nested table's SETID (SID) column:

   The SETID column is required. Set its OCI_ATTR_NAME using a dummy name, because the API doesn't expect the user to know its system name. Then set the column attribute with OCI_ATTR_DIRPATH_SID to indicate that this is a SID column.

2. Object table's object id (OID) column:

   An object id is required if:

   1. If the object id is system-generated:

      Use a dummy name as its column name (for example, "cust_oid").

      Set its column attribute with OCI_ATTR_DIRPATH_OID. So if you have multiple columns with dummy names, you know which one represents the system-generated OID.

   2. If the object id is primary-key-based:

      You cannot use a dummy name as its column name. Therefore, you do not need to set its column attribute with OCI_ATTR_DIRPATH_OID.

3. SQL string argument:

   Set the attribute's column name with OCI_ATTR_NAME.

   The order of the SQL string arguments given does not matter. The order does not have to match the order used in the SQL string.

   There is a naming convention for SQL string arguments.

   1. The argument names must match the bind variable names used in the SQL string in content but not in case. For example, if the SQL string is substr(:INPUT_STRING, 3, 5), then you can give the argument name as "input_string".

   2. If an argument is used multiple times in an SQL string, then you can declare it once and count it as only one argument.

4. REF argument:

   1. Set the attribute's column name using OCI_ATTR_NAME.

      The order of the REF arguments does matter.

      • The reference table name comes first, if given.

      • The object id, whether it is system-generated or primary-key-based, comes next.

   2. There is a naming convention for the REF arguments.

      • For the reference table name argument, use any dummy names for its column name, for example, "ref-tbl".

      • For the system-generated OID argument, use any dummy names for its column name, such as "sys-OID". Note: Since this column is used as an argument and not as a column to load into, do not set this column with OCI_ATTR_DIRPATH_OID.

      • For a primary-key-based object id, list all the primary-key columns to load into. There is no need to create a dummy name for OID. The component column names, if given (see step for short cut later), can be given in any order.

   3. Do not set the attribute column name(s) for the object id if you want to use the short cut.

   • Short cut. If loading a system-OID-based REF column, do not set the column name with a name. The API will figure it out. But you still have to set other column attributes, such as external datatype.

   • If loading a primary-key REF column and its primary key consists of multiple columns, the short cut is not to set their column names. But user will still have to set other column attributes, such as external datatype.

     Note:

     If the component column names are NULL, then the API code determines the column names in the position or order in which they were defined for the primary key. So, when you set column attributes other than the name, make sure the attributes are set for the component columns in the correct order.

OCI_ATTR_DIRPATH_SID

Indicates that a column is a nested table's SETID column. Required if loading to a nested table.

ub1 flg = 1;
sword error;

error = OCIAttrSet((void  *)colDesc, 
                   OCI_DTYPE_PARAM,
                   (void  *)&flg, (ub4)0,
                   OCI_ATTR_DIRPATH_SID, ctlp->errhp_ctl);

OCI_ATTR_DIRPATH_OID

Indicates that a column is an object table's object id column.

ub1 flg = 1;
sword error;

error = OCIAttrSet((void  *)colDesc, 
                   OCI_DTYPE_PARAM,
                   (void  *)&flg, (ub4)0,
                   OCI_ATTR_DIRPATH_OID, ctlp->errhp_ctl);

OCI_ATTR_NUM_ROWS Attribute

This attribute, when used for a OCI_HTYPE_DIRPATH_FN_COL_ARRAY (function column array), is retrievable only, and cannot be set by the user. When called with the function OCIAttrGet(), the number of rows allocated for the function column array is returned.