?
Solved

OCI for Direct Path Loading on Oracle8i and 9i

Posted on 2003-03-28
6
Medium Priority
?
1,451 Views
Last Modified: 2012-06-21
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
                                    ////////////////////////////////////////////////////////////////////////
                              }
0
Comment
Question by:vachooho
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 1200 total points
ID: 8230599
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
 
LVL 5

Author Comment

by:vachooho
ID: 11024529
schwertner's comment does not answer the question.

Workaround's like "do not use direct path" not acccepted.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11025059
Refund the points!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question