I tried to insert into CLOB using the latest Oracle ODBC driver 9.02.00.65 and it fails. But the same works with earlier versions of ODBC driver earlier to 9.02.00.65 ie., 9.02.00.63.

Here is the code snippet I tried. Any help now is highly appreciated as I am in the crunch time.

I tried the same code snippet with VARCHAR2 column with the same driver and it works.

/*
** CONVDSN.C - This is the ODBC sample code for
** creating File DSN pointers to machine DSNs.
**
**This code is furnished on an as-is basis as part of the ODBC SDK and is
**intended for example purposes only.
**
*/

#include
#include
#include

#include
#include
#include

#include

#define MAXDATALEN 25 //maximum data length per column
#define MAX_COL 15 //maximum column in result set
#define MAX_ROW 100 //maximum number of rows
#define MAXBUFLEN 256
#define SQLERR_FORMAT "SQL Error State:%s, Native Error Code: %lX, ODBC Error: %s"
#define MAXDISPLAYSIZE MAX_COL*(MAXDATALEN+1)
#define SQLWRNMSGTITLE "SQL_SUCCESS_WITH_INFO results"
#define SQLERRCNTDTITLE "SQL_ERROR results continued"
#define SQLWRNMSGTITLE "SQL_SUCCESS_WITH_INFO results"
#define SQLWRNCNTDTITLE "SQL_SUCCESS_WITH_INFO results continued"
#define NULLDATASTRING "SQL_NULL_DATA"
#define SQLERRMSGTITLE "SQL_ERROR results"
// prototypes

void ExpandFileName(LPSTR szFileDSNName, LPCSTR szDSNName);
void MakeLegalName(LPSTR szLegalDSNName, LPCSTR szDSNName);

// main routine: Iterate through the user and system DSNs, creating a pointer
// to each.

void FAR PASCAL DisplayError(SQLRETURN nResult, HWND hWnd, SWORD fHandleType, SQLHANDLE handle);

void insertSelectClob();
void checkRcCode(RETCODE rc);

int main (int argc, char* argv[])
{
insertSelectClob();
return 0;
}

void FAR PASCAL DisplayError(SQLRETURN nResult, HWND hWnd, SWORD fHandleType, SQLHANDLE handle)
{
UCHAR szErrState[SQL_SQLSTATE_SIZE+1]; // SQL Error State string
UCHAR szErrText[SQL_MAX_MESSAGE_LENGTH+1]; // SQL Error Text string
char szBuffer[SQL_SQLSTATE_SIZE+SQL_MAX_MESSAGE_LENGTH+MAXBUFLEN+1];
// formatted Error text Buffer
SWORD wErrMsgLen; // Error message length
UDWORD dwErrCode; // Native Error code
int iSize; // Display Error Text size
SQLRETURN nErrResult; // Return Code from SQLGetDiagRec
SWORD sMsgNum = 1;
SWORD fFirstRun = TRUE;
char szDispBuffer[MAXDISPLAYSIZE+1]; // Display Buffer

szBuffer[0] = '\0';

do
{
// continue to bring messageboxes till all errors are displayed.
// more than one message box may be reqd. as err text has fixed
// string size.

// initialize display buffer with the string in error text buffer
strcpy(szDispBuffer, szBuffer);

// call SQLGetDiagRec function with proper ODBC handles, repeatedly until
// function returns SQL_NO_DATA. Concatenate all error strings
// in the display buffer and display all results.
while ((nErrResult = SQLGetDiagRec(fHandleType, handle, sMsgNum++,
szErrState, &dwErrCode, szErrText,
SQL_MAX_MESSAGE_LENGTH-1, &wErrMsgLen)) != SQL_NO_DATA)
{


if(nErrResult == SQL_ERROR || nErrResult == SQL_INVALID_HANDLE)
break;

wsprintf(szBuffer, SQLERR_FORMAT, (LPSTR)szErrState, dwErrCode, (LPSTR)szErrText);
iSize = strlen(szDispBuffer);
if (iSize && (iSize+strlen(szBuffer)+1) >= MAXDISPLAYSIZE)
break;
if (iSize)
strcat(szDispBuffer, "\n");
strcat(szDispBuffer, szBuffer);
}

// display proper ERROR or WARNING message with proper title

if (nResult == SQL_SUCCESS_WITH_INFO)
MessageBox(hWnd, szDispBuffer, (fFirstRun? SQLWRNMSGTITLE : SQLWRNCNTDTITLE),
MB_OK | MB_ICONINFORMATION);
else
MessageBox(hWnd, szDispBuffer, (fFirstRun? SQLERRMSGTITLE : SQLERRCNTDTITLE),
MB_OK | MB_ICONEXCLAMATION);

if (fFirstRun)
fFirstRun = FALSE;
}
while (!(nErrResult == SQL_NO_DATA || nErrResult == SQL_ERROR || nErrResult == SQL_INVALID_HANDLE));
}


void insertSelectClob()
{

SQLCHAR clobdata[1001];
SQLCHAR resultdata[1001];
SQLINTEGER ind = SQL_DATA_AT_EXEC;
SQLCHAR *bufp;
SQLINTEGER cbOrderID = sizeof(SQLSMALLINT);
SQLSMALLINT sTmp=13;

SQLCHAR *sqlStmt1 = _T("INSERT INTO clobtbl(id, clob1) VALUES(?, ?)");
SQLCHAR *sqlStmt2 = _T("SELECT id, clob1 FROM clobtbl");
// SQLCHAR *sqlStmt1 = _T("INSERT INTO testInsert(id, clob1) VALUES(?, ?)");
// SQLCHAR *sqlStmt2 = _T("SELECT id, clob1 FROM testInsert");
int clobdatalen, chunksize, dtsize, retchklen;

HENV envHnd;
HDBC conHnd;
HSTMT stmtHnd;
RETCODE rc;
int nRowcnt=0;
SQLPOINTER pToken = NULL;

rc = SQL_SUCCESS;

// ENV is allocated
rc = SQLAllocEnv(&envHnd);
// Connection Handle is allocated

rc = SQLAllocConnect(envHnd, &conHnd);
rc = SQLConnect(conHnd, _T("testd734"), SQL_NTS, _T("ipathdba"), SQL_NTS, _T("ipathdba"), SQL_NTS);
printf(_T("Insert CLOB1 using SQLPutData...\n[%s]\n"), sqlStmt1);

// Set CLOB Data
{
int i;
SQLCHAR ch;
for (i=0, ch=_T('A'); i< sizeof(clobdata)/sizeof(SQLCHAR); ++i, ++ch)
{
if (ch > _T('Z'))
ch = _T('A');
clobdata = ch;
}

clobdata[sizeof(clobdata)/sizeof(SQLCHAR)-1] = _T('\0');
}
clobdatalen = lstrlen(clobdata); // length of characters
chunksize = clobdatalen / 7; // 7 times to put

rc = SQLAllocHandle(SQL_HANDLE_STMT, conHnd, &stmtHnd);
// Step 1: Prepare
rc = SQLPrepare(stmtHnd, sqlStmt1, SQL_NTS);
// checkSQLErr(envHnd, conHnd, stmtHnd, rc);

// Step 2: Bind Parameter with SQL_DATA_AT_EXEC
rc = SQLBindParameter(stmtHnd,
1,
SQL_PARAM_INPUT,
SQL_C_SSHORT,
SQL_INTEGER,
0,
0,
&sTmp,
0,
&cbOrderID);
rc = SQLBindParameter(stmtHnd,
2,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_LONGVARCHAR,
clobdatalen*sizeof(CHAR),
0,
(SQLPOINTER)clobdata,
clobdatalen*sizeof(CHAR),
&ind);
// checkSQLErr(envHnd, conHnd, stmtHnd, rc);


// Step 3: Execute
rc = SQLExecute(stmtHnd);
while (rc == SQL_NEED_DATA) {
rc = SQLParamData(stmtHnd, &pToken);
if (rc == SQL_NEED_DATA) {
for (dtsize=0, bufp = clobdata;
dtsize < clobdatalen;
dtsize += chunksize, bufp += chunksize)
{
int len;
if (dtsize+chunksize < clobdatalen)
{
len = chunksize;
rc = SQLPutData(stmtHnd, bufp, len*sizeof(SQLCHAR));
}
else
{
len = clobdatalen-dtsize;
rc = SQLPutData(stmtHnd, bufp, SQL_NTS);
}
}
rc = SQLParamData(stmtHnd, &pToken);
}
}

// Fails as row count retrieved is zero.
rc = SQLRowCount(stmtHnd, &nRowcnt);
if(rc != SQL_SUCCESS)
{
DisplayError(rc, NULL, SQL_HANDLE_ENV, conHnd);
}
rc = SQLFreeStmt(stmtHnd, SQL_CLOSE);
printf(_T("Finished Update\n\n"));
rc = SQLAllocStmt(conHnd, &stmtHnd);
if (rc != SQL_SUCCESS)
{
printf(_T("Failed to allocate STMT\n"));
exit(-1);
}

// Clear Result Data
memset(resultdata, 0, sizeof(resultdata));
chunksize = clobdatalen / 15; // 15 times to gut

rc = SQLExecDirect(stmtHnd, sqlStmt2, SQL_NTS); // select
if(rc != SQL_SUCCESS)
{
DisplayError(rc, NULL, SQL_HANDLE_ENV, conHnd);
}

// Step 2: Fetch
rc = SQLFetch(stmtHnd);

for(dtsize=0, bufp = resultdata;

dtsize > sizeof(resultdata)/sizeof(CHAR) && rc != SQL_NO_DATA;
dtsize += chunksize-1, bufp += chunksize-1)
{
int len; // len should contain the space for NULL termination
if (dtsize+chunksize len = chunksize;
else
len = sizeof(resultdata)/sizeof(CHAR)-dtsize;
// Step 3: GetData
rc = SQLGetData(stmtHnd,
2,
SQL_C_CHAR,
(SQLPOINTER)bufp,
len*sizeof(CHAR),
&retchklen);
}
if (!_tcscmp(resultdata, clobdata))
{
printf(_T("Succeeded!!\n\n"));
}
else
{
printf(_T("Failed!!\n\n"));
}

if (conHnd)
SQLFreeConnect(conHnd);
if (envHnd)
SQLFreeEnv(envHnd);
}>