CLOB datatype issue in datastage

nani0907 · Post by **nani0907** » Thu Sep 22, 2011 4:00 am

Hi All,

We are using datastage 8.5 ,and using oracle connect stage we are reading data ,and have one CLOB datatype colum which is taken as longvarchar(4000).but still i face error as below

The OCI function OCIStmtExecute returned status -1. Error code: 1,461, Error message: ORA-01461: can bind a LONG value only for insert into a LONG column. (CC_OraStatement::executeInsert, file CC_OraStatement.cpp, line 1,684)

Please help me out

ray.wurlod · Post by **ray.wurlod** » Thu Sep 22, 2011 3:23 pm

Look elsewhere for the problem - the error message is complaining about a LONG data type, not about a VARCHAR data type.

nani0907 · Post by **nani0907** » Fri Sep 23, 2011 4:39 am

Ray,Thanks for your reply.

But when we remove CLOB datatype column,the job works fine without errors .

we had tried with other option too.In oracle connector stage properties ,we had given the LOB preference and mentioned the CLOB column name .this time we get the below error log as

The OCI function OCIStmtExecute returned status -1. Error code: 1,445, Error message: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table. (CC_OraStatement::executeSelect, file CC_OraStatement.cpp, line 2,893)

but we have not selected row id in the select query

Kindly suggest us

chulett · Post by **chulett** » Fri Sep 23, 2011 7:21 am

Suggest you read the Connectivity Guide for Oracle Databases pdf where it discusses how to process the CLOB datatype via the Connector stage.

nani0907 · Post by **nani0907** » Sun Oct 02, 2011 11:58 pm

Hi,

It's mentioned in help to use longnvarchar,but still the job get aborted.

ray.wurlod · Post by **ray.wurlod** » Mon Oct 03, 2011 12:33 am

Look again at the error message. LONG is not the same as LONG VARCHAR2 or LONG NVARCHAR2.

prakashdasika · Post by **prakashdasika** » Wed Oct 05, 2011 7:32 pm

I have described the CLOBs as 'Unknown' datatype. In this case the default datatype assigned is Varchar or Longvarchar depending on the CLOB size. I also observed a unicode extension and it worked fine in my Job.