OCI for Direct Path Loading on Oracle8i and 9i

The code below works with Oracle 8i and does not work with Oracle 9i
it returns error

ORA-24329: invalid character set identifier

in OCIDirPathPrepare() call

I did not set character set in any of previous calls and it works for Oracle 8i
but it doesnt work for 9i even if I set character sets

Currently I am using OCI 8 - NOT 9 - but it doesnt work even with 9i

Any help?
Thanx

{
                                    ub2            nCharSetID      = 0;      // character set!!! 178 (9i) or 31 (8i)

                                    ////////////////////////////////////////////////////////////////////////
                                    // initialize direct path
                                    ////////////////////////////////////////////////////////////////////////
                                          // allocate and initialize a direct path context
                                    if(      OCI_CheckError(m_pControl->m_hOCIEnvironment, ::OCIHandleAlloc((dvoid *)m_pControl->m_hOCIEnvironment, (dvoid **)&m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (size_t)0, (dvoid **)0), &m_strError, OCI_HTYPE_ENV) &&
                                          // set table name
                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid *)(LPCTSTR)strTableName, (ub4)strTableName.GetLength(), (ub4)OCI_ATTR_NAME, m_pControl->m_hOCIError), &m_strError) &&
                                          // set schema ( owner ) name
                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid *)(LPCTSTR)(m_pControl->m_strOwner), (ub4)m_pControl->m_strOwner.GetLength(), (ub4)OCI_ATTR_SCHEMA_NAME, m_pControl->m_hOCIError), &m_strError) &&
                                          // Note: setting tbl default datemask will not trigger client library
                                          // to check strings for dates - only setting column datemask will.
                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid *)(LPCTSTR)(OraDate::m_strDefaultFormat), (ub4)OraDate::m_strDefaultFormat.GetLength(), (ub4)OCI_ATTR_DATEFORMAT, m_pControl->m_hOCIError), &m_strError) &&
                                          // set table level parallel option
                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid *)&(m_pControl->m_nDirPathParrallel), (ub4)0, (ub4)OCI_ATTR_DIRPATH_PARALLEL, m_pControl->m_hOCIError), &m_strError) &&
                                          // set table level nolog option
                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid *)&(m_pControl->m_nDirPathNoLog), (ub4)0, (ub4)OCI_ATTR_DIRPATH_NOLOG, m_pControl->m_hOCIError), &m_strError) //&&
                                          // set character set for direect path loading
                                          //OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid *)&nCharSetID, (ub4)0, (ub4)OCI_ATTR_CHARSET_ID, m_pControl->m_hOCIError), &m_strError)
                                      )
                                    {
                                          ////////////////////////////////////////////////////////////////////////
                                          // this will create Columns array
                                          ////////////////////////////////////////////////////////////////////////
                                          OCIParam *            hParam             = NULL;            //parmh;            // parameter handle
                                          OCIParam *            hColList       = NULL;            //collsthd;            // handle to list of columns
                                          OCIParam *            hColumn             = NULL;            //colhd;            // column handle
                                          OCIParam *            hColumnDesc       = NULL;            // column parameter descriptor in dirpath context
                                          OCIDescribe *      hDescribe       = NULL;            //dschp;            // describe handle
                                          ub2                        nColumnCount = 0;

                                          if(      OCI_CheckError(m_pControl->m_hOCIError, ::OCIHandleAlloc( (dvoid *)(m_pControl->m_hOCIEnvironment), (dvoid **) &hDescribe, OCI_HTYPE_DESCRIBE, (size_t) 0, (dvoid **) 0), &m_strError) &&
                                                // get the describe handle for the table
                                                OCI_CheckError(m_pControl->m_hOCIError, ::OCIDescribeAny(m_pControl->m_hOCIServiceContext, m_pControl->m_hOCIError, (dvoid*)(LPCTSTR)strTableName, strTableName.GetLength(), OCI_OTYPE_NAME, 0, OCI_PTYPE_TABLE, hDescribe), &m_strError) &&
                                                // get the parameter handle
                                                OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrGet(hDescribe, OCI_HTYPE_DESCRIBE, &hParam, 0, OCI_ATTR_PARAM, m_pControl->m_hOCIError), &m_strError) &&
                                                // The type information of the object, in this case, OCI_PTYPE_TABLE,
                                                // is obtained from the parameter descriptor returned by the OCIAttrGet().
                                                // get the number of columns in the table
                                                OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrGet(hParam, OCI_DTYPE_PARAM, &nColumnCount, 0, OCI_ATTR_NUM_COLS, m_pControl->m_hOCIError), &m_strError) &&
                                                // get the handle to the column list of the table
                                                OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrGet(hDescribe, OCI_HTYPE_DESCRIBE, &hParam, 0, OCI_ATTR_PARAM, m_pControl->m_hOCIError), &m_strError)
                                            )
                                          {
                                                sword status = ::OCIAttrGet(hParam, OCI_DTYPE_PARAM, &hColList, 0, OCI_ATTR_LIST_COLUMNS, m_pControl->m_hOCIError);
                                                if(status == OCI_NO_DATA)
                                                      m_strError.Format(_T("NO schema data for table : %s"), strTableName);
                                                else
                                                {
                                                      if(OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrGet(hDescribe, OCI_HTYPE_DESCRIBE, &hParam, 0, OCI_ATTR_PARAM, m_pControl->m_hOCIError), &m_strError))
                                                      {
                                                                  // set number of columns to be loaded!
                                                            if(      OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid *)&nColumnCount, (ub4)0, (ub4) OCI_ATTR_NUM_COLS, m_pControl->m_hOCIError), &m_strError) &&
                                                                  // get the column parameter list!
                                                                  OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrGet((dvoid *)m_hOCIDPContext, OCI_HTYPE_DIRPATH_CTX, (dvoid *)&m_hOCIDPColListDesc, (ub4 *)0, OCI_ATTR_LIST_COLUMNS, m_pControl->m_hOCIError), &m_strError)
                                                              )
                                                            {
                                                                  CString strMsg;
                                                                  strMsg.Format(_T("[%s] : table contains %d columns"), strTableName, nColumnCount);
                                                                  ::Log_AddMessage(strMsg);

                                                                  // go through the column list and retrieve the data-type of each column,
                                                                  // and then recursively describe column types.
                                                                  for(ub2 i = 1; i <= nColumnCount; i++)
                                                                  {
                                                                              // get parameter for column i in Table description
                                                                        if(      OCI_CheckError(m_pControl->m_hOCIError, ::OCIParamGet(hColList, OCI_DTYPE_PARAM, m_pControl->m_hOCIError, (void **)&hColumn, i), &m_strError) &&
                                                                              // and in the Direct path context
                                                                              OCI_CheckError(m_pControl->m_hOCIError, ::OCIParamGet((CONST dvoid *)m_hOCIDPColListDesc, (ub4)OCI_DTYPE_PARAM, m_pControl->m_hOCIError, (dvoid **)&hColumnDesc, i), &m_strError)
                                                                          )
                                                                        {
                                                                              // get data type for column
                                                                              ub2            nType            = 0;
                                                                              ub2            nSize            = 0;
                                                                              ub4            nLength            = 0;
                                                                              short      nScale            = 0;
                                                                              short      nPrec            = 0;
                                                                              text *      pName            = NULL;

                                                                              if(      !::OCIAttrGet(hColumn, OCI_DTYPE_PARAM, &nType, 0, OCI_ATTR_DATA_TYPE, m_pControl->m_hOCIError) &&
                                                                                    !::OCIAttrGet(hColumn, OCI_DTYPE_PARAM, &nSize, 0, OCI_ATTR_DATA_SIZE, m_pControl->m_hOCIError) &&
                                                                                    !::OCIAttrGet(hColumn, OCI_DTYPE_PARAM, &pName, &nLength, OCI_ATTR_NAME, m_pControl->m_hOCIError) &&
                                                                                    !::OCIAttrGet(hColumn, OCI_DTYPE_PARAM, &nPrec, 0, OCI_ATTR_PRECISION, m_pControl->m_hOCIError) &&
                                                                                    !::OCIAttrGet(hColumn, OCI_DTYPE_PARAM, &nScale, 0, OCI_ATTR_SCALE, m_pControl->m_hOCIError) //&&
                                                                                    //!::OCIAttrGet(hColumn, OCI_DTYPE_PARAM, &nCharSetID, 0, OCI_ATTR_CHARSET_ID, m_pControl->m_hOCIError)
                                                                                )
                                                                              {
                                                                                    //CString str;
                                                                                    //str.Format("OCI_ATTR_CHARSET_ID = %d", nCharSetID);
                                                                                    //AfxMessageBox(str);

                                                                                    CString strColumnName = (LPCTSTR)pName;
                                                                                    strColumnName = strColumnName.Left(nLength);

                                                                                    //CString str;
                                                                                    //str.Format(_T("%d. Column : %s, type : %d, size : %d"), (long)i, strColumnName, nType, nSize);
                                                                                    //::Log_AddMessage(str);
                                                                                    //AfxMessageBox(strColumnName);

                                                                                    ub2            nType2      = SQLT_CHR;
                                                                                    ub4            nSize2      = nSize;
                                                                                    //short      nPrec2      = nPrec == 0 ? 38 : nPrec;
                                                                                    //short      nScale2      = nScale == 0 ? 2 : nScale;

                                                                                    switch(nType)
                                                                                    {
                                                                                          case 2:            // NUMBER
                                                                                                if((nPrec > 0) && (nPrec <= 10))
                                                                                                {
                                                                                                      nType2      = SQLT_INT;
                                                                                                      nSize2      = sizeof(long);
                                                                                                }
                                                                                                else
                                                                                                {
                                                                                                      nType2 = SQLT_FLT;
                                                                                                      nSize2 = sizeof(double);
                                                                                                }
                                                                                                break;
                                                                                          case 12 :      // SQLT_DAT, DATE
                                                                                                nType2 = SQLT_CHR;
                                                                                                nSize2 = 20;            // lengthof(YYYY/MM/DD HH:MI:SS) + 1
                                                                                                break;
                                                                                          default:
                                                                                                nType2      = SQLT_CHR;
                                                                                                break;
                                                                                    }

                                                                                    // set column name
                                                                                    if(      OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)hColumnDesc, (ub4)OCI_DTYPE_PARAM, (dvoid *)(LPCTSTR)strColumnName, (ub4)strColumnName.GetLength(), (ub4)OCI_ATTR_NAME, m_pControl->m_hOCIError), &m_strError) && 
                                                                                          // set column type
                                                                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)hColumnDesc, (ub4)OCI_DTYPE_PARAM, (dvoid *)&nType2, (ub4)0, (ub4)OCI_ATTR_DATA_TYPE, m_pControl->m_hOCIError), &m_strError) &&
                                                                                          // set data size ???
                                                                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)hColumnDesc, (ub4)OCI_DTYPE_PARAM, (dvoid *)&nSize2, (ub4)0, (ub4)OCI_ATTR_DATA_SIZE, m_pControl->m_hOCIError), &m_strError) &&
                                                                                          // If column is chrdate or date, set column (input field) date mask
                                                                                          // to trigger client library to check string for a valid date.
                                                                                          OCI_CheckError(m_pControl->m_hOCIError, (nType == 12 /*DATE*/ ? ::OCIAttrSet((dvoid *)hColumnDesc, (ub4)OCI_DTYPE_PARAM, (dvoid *)(LPCTSTR)(OraDate::m_strDefaultFormat), (OraDate::m_strDefaultFormat.IsEmpty()) ? NULL : (ub4)OraDate::m_strDefaultFormat.GetLength(), (ub4)OCI_ATTR_DATEFORMAT, m_pControl->m_hOCIError) : OCI_SUCCESS), &m_strError) &&
                                                                                          // set precision
                                                                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)hColumnDesc, (ub4)OCI_DTYPE_PARAM, (dvoid *)&nPrec, (ub4)0, (ub4)OCI_ATTR_PRECISION, m_pControl->m_hOCIError), &m_strError) &&
                                                                                          // set scale
                                                                                          OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)hColumnDesc, (ub4)OCI_DTYPE_PARAM, (dvoid *)&nScale, (ub4)0, (ub4)OCI_ATTR_SCALE, m_pControl->m_hOCIError), &m_strError) //&&
                                                                                          // set charset
                                                                                          //OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)hColumnDesc, (ub4)OCI_DTYPE_PARAM, (dvoid *)&nCharSetID, (ub4)0, (ub4)OCI_ATTR_CHARSET_ID, m_pControl->m_hOCIError), &m_strError)
                                                                                      )
                                                                                    {
                                                                                          //CString str;
                                                                                          //str.Format("OCI_ATTR_CHARSET_ID = %d", nCharSetID);
                                                                                          //AfxMessageBox(str);

                                                                                          COCIColumn * pColumn      = new COCIColumn;

                                                                                          pColumn->m_strName            = strColumnName;
                                                                                          pColumn->m_ColType            = nType == SQLT_DAT ? SQLT_DAT : nType2;
                                                                                          pColumn->m_Precision      = nPrec;
                                                                                          pColumn->m_ColSize            = nSize2;
                                                                                          pColumn->m_Scale            = nScale;
                                                                                          pColumn->m_ColumnID            = i - 1;

                                                                                          m_arrColumns.Add(pColumn);

                                                                                          // do not allocate data buffer now !

                                                                                          m_mapColumns.SetAt(strColumnName, m_arrColumns.GetSize() - 1);
                                                                                    }
                                                                                    else
                                                                                          ::Log_AddMessage(m_strError);
                                                                              }

                                                                              // free col list descriptors
                                                                              ::OCIDescriptorFree((dvoid *)hColumnDesc, OCI_DTYPE_PARAM);
                                                                              ::OCIDescriptorFree((dvoid *)hColumn, OCI_DTYPE_PARAM);
                                                                        }
                                                                  }

                                                                  //AfxMessageBox("continue");

                                                                  // continue initialization
                                                                  // set buffer size for dir path loading
                                                                  if(      OCI_CheckError(m_pControl->m_hOCIError, ::OCIAttrSet((dvoid *)m_hOCIDPContext, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid *)&(m_pControl->m_nDirPathBufferSize), (ub4)0, (ub4)OCI_ATTR_BUF_SIZE, m_pControl->m_hOCIError), &m_strError) &&
                                                                        // prepare the load
                                                                        OCI_CheckError(m_pControl->m_hOCIError, ::OCIDirPathPrepare(m_hOCIDPContext, m_pControl->m_hOCIServiceContext, m_pControl->m_hOCIError), &m_strError) &&
                                                                        // Allocate column array and stream handles.
                                                                        // Note that for the column array and stream handles
                                                                        // the parent handle is the direct path context.
                                                                        // Also note that Oracle errors are returned via the
                                                                        // environment handle associated with the direct path context.
                                                                        OCI_CheckError(m_pControl->m_hOCIEnvironment, ::OCIHandleAlloc((dvoid *)m_hOCIDPContext, (dvoid **)&m_hOCIDPColArray, (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY, (size_t)0, (dvoid **)0), &m_strError, OCI_HTYPE_ENV) &&
                                                                        OCI_CheckError(m_pControl->m_hOCIEnvironment, ::OCIHandleAlloc((dvoid *)m_hOCIDPContext, (dvoid **)&m_hOCIDPStream, (ub4)OCI_HTYPE_DIRPATH_STREAM, (size_t)0, (dvoid **)0), &m_strError, OCI_HTYPE_ENV)
                                                                    )
                                                                  {
                                                                        // get number of rows in the column array just allocated
                                                                        ::OCIAttrGet((CONST dvoid *)m_hOCIDPColArray, OCI_HTYPE_DIRPATH_COLUMN_ARRAY, (dvoid *)(&m_nRowCount), (ub4 *)0, OCI_ATTR_NUM_ROWS, m_pControl->m_hOCIError);

                                                                        // get number of columns in the column array just allocated
                                                                        ::OCIAttrGet((CONST dvoid *)m_hOCIDPColArray, OCI_HTYPE_DIRPATH_COLUMN_ARRAY, (dvoid *)(&m_nColCount), (ub4 *)0, OCI_ATTR_NUM_COLS, m_pControl->m_hOCIError);

                                                                        //CString str;
                                                                        //str.Format(_T("Direct path load array allocated : [%d] rows, [%d] cols..."), (long)(m_pControl->m_nRowCount), (long)(m_pControl->m_nColCount));
                                                                        //::AfxMessageBox(str);

                                                                        // set all values to NULL initialy
                                                                        for(int i = 0 ; i < m_arrColumns.GetSize() ; i++)
                                                                        {
                                                                              COCIColumn * pColumn = m_arrColumns.ElementAt(i);
                                                                              if(pColumn != NULL)
                                                                                    ::OCIDirPathColArrayEntrySet(m_hOCIDPColArray, m_pControl->m_hOCIError, 0, (ub2)pColumn->m_ColumnID, (ub1*)NULL, 0, OCI_DIRPATH_COL_NULL);
                                                                        }
                                                                  }
                                                                  else
                                                                        ::Log_AddMessage(m_strError);
                                                                  
                                                            }
                                                      }
                                                }
                                          }

                                          // free describe handle
                                          if(hDescribe != NULL)
                                                ::OCIHandleFree((dvoid *)hDescribe, OCI_HTYPE_DESCRIBE);

                                    }
                                    ////////////////////////////////////////////////////////////////////////
                                    // end create column array
                                    ////////////////////////////////////////////////////////////////////////
                              }
LVL 5
vachoohoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
symptom: ORA-24329: Invalid character set identifier
cause: Direct Path Load can only be used when SQL*Loader and Database have the same version.
fix:
1. Use SQL*Loader that has the same version as the Database or
2. Update the mapping properties in Warehouse Builder to set Direct Mode  property to False and re-generate the mapping.


[BUG:1755233]  NLS: CAN'T DIRECT LOAD MULTIBYTE DATA INTO ADT CLOB COLUMN.
Ver(s): 9.0.1  Fixed Ver(s): 9.0.1 (new patchset)
Symptom(s):
- SQL*Loader-951: Error calling once/load initialization
- ORA-24329: invalid character set identifier                
Available Workaround(s):  
- Use conventional load

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vachoohoAuthor Commented:
schwertner's comment does not answer the question.

Workaround's like "do not use direct path" not acccepted.
0
schwertnerCommented:
Refund the points!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.