Example 21-8 shows sample code that 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.

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));

Allocate the Column Array and Stream Handles

Note that the direct path context handle is the parent handle for the column array and stream handles, as shown in Example 21-9. Also note that errors are returned with the environment handle associated with the direct path context.

Get the Number of Rows and Columns

Get the number of rows and columns in the column array just allocated, as shown in Example 21-10.

Set the Input Data Fields

Set the input data fields to their corresponding data columns, as shown in Example 21-11.

Reset the Column Array State

Reset the column array state in case a previous conversion must be continued or a row is expecting more data, as shown in Example 21-12.

Reset the Stream State

Reset the stream state to start a new stream, as shown in Example 21-13. Otherwise, data in the stream is appended to existing data.

Convert the Data in the Column Array to Stream Format

After inputting the data, convert the data in the column array to stream format and filter out any bad records, as shown in Example 21-14.

Load the Stream

Note that the position in the stream is maintained internally to the stream handle, along with offset information for the column array that produced the stream. When the conversion to stream format is done, the data is appended to the stream, as shown in Example 21-15. It is the responsibility of the caller to reset the stream when appropriate. On errors, the position is moved to the next row, or to the end of the stream if the error occurs on the last row. The next OCIDirPathLoadStream() call starts on the next row, if any. If an OCIDirPathLoadStream() call is made and the end of a stream has been reached, OCI_NO_DATA is returned.

Finish the Direct Path Load

Finish the direct path load as shown in Example 21-16.

Free the Direct Path Handles

Free all the direct path handles allocated, as shown in Example 21-17. Note that the direct path column array and stream handles are freed before the parent direct path context handle is freed.

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 data type 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);

This section includes the following topic: Describing a Nested Table Column and Its Nested Table.

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 is 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 is 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 are to be loaded for this column object using OCI_ATTR_NUM_COLS.

8. Get the column or 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 is to 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 through 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.

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 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 function column array value OCI_HTYPE_DIRPATH_FN_COL_ARRAY.

Example 21-18 shows how to allocate a child column array for a column object.

If a column is an object, the address of its child column array handle is passed instead. The child column array contains 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().

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 is 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 is 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 are to be passed to the SQL string with OCI_ATTR_NUM_COLS.

8. Get the column or 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. Use the 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 a 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 is to 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.

A child column array is allocated for each SQL string column. Each row of arguments in the child column array maps to the corresponding non-NULL row of its parent SQL string column in the parent column array.

Example 21-19 shows how to allocate a child column array for a SQL string column.

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.

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 is to 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 shortcut 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 uses 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 must match the table name specified in the schema. If it is not in the data row, the API uses the reference table specified in the schema.

7. Set the number of REF arguments that are to 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 is 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 pass in some REF arguments other than N or N+1, the error message says 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 does not invoke an error message.

8. Get the column or 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. Because 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 shortcut note later), can be given in any order.

      Do not set the attribute column names for the object ID to use the shortcut.

      Shortcut. If loading a system-OID-based REF column, do not set the column name with a name. The API figures it out. But you must still set other column attributes, such as external data type.

      If loading a primary-key REF column and its primary key consists of multiple columns, the shortcut is not to set their column names. But you must still set other column attributes, such as external data type.

      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, ensure that the attributes are set for the component columns in the correct order.

   3. Set the external column type (the type of the data that is to 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.

Example 21-20 shows how to allocate a child column array for a REF column.

If a column is a REF, the address of its child column array handle is passed instead. The child column array contains 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, a length of 0, and an OCI_DIRPATH_COL_NULL flag to OCIDirPathColArrayEntrySet().

   See Also:

   • OCI_DIRPATH_COL_ERROR

   • OCIDirPathColArrayEntrySet()

Column Objects

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

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

REF Columns

This optional expression is the reference table name. This table is the object table from which the REF column is to reference row objects.

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

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 must 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. The REF column is not allowed to have a reference table name as its argument. (Direct path API provides this parameter as a shortcut for the users who will be loading to an unscoped REF column that refers to the same reference object table during the entire load.)

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

  • If not set, the direct path API uses 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 must match the table name specified in the schema. If it is not in the data row, the API uses the reference table defined in the schema.

SQL String Columns

This mandatory expression contains a SQL string to derive the value that is to 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.

Column Objects

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

SQL String Columns

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

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

REF Columns

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

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 are 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 are 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 1. 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 1 to N.

  Note:

  To simplify the error message if you pass in some REF arguments other than N or N+1, the error message says 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 does not invoke an error message.