Error in extracting records having CLOB datatype.

deepu_n55 · Post by **deepu_n55** » Wed Mar 21, 2012 7:01 am

Hi All,
I have a problem , i hope you guys could help me on this. I am having problem in extracting records from Oracle table where one of the column has CLOB datatype. I am using Oracle Enterprise stage . Target column also has the datatype as CLOB. I tried using longVarchar in datastage still wouldnt work . Do you guys have any idea.
thanks in advance . i appreciate your help

priyadarshikunal · Post by **priyadarshikunal** » Wed Mar 21, 2012 7:19 am

as you might know that datastage doesn't support clob datatypes and there is no default conversion from clob to any other datatype.

You have to convert it to some other datatype in your SQL query itself say varchar2 or something else.

PaulVL · Post by **PaulVL** » Wed Mar 21, 2012 10:44 am

Cut and pasting the error message here would help as well.

deepu_n55 · Post by **deepu_n55** » Thu Mar 22, 2012 6:57 am

The Error i See is as below. can anyone suggest me any solution?

: Error when checking operator: Caught parsing exception during wrapDescribeOperator(): Could not find type: oratype:112

kwwilliams · Post by **kwwilliams** » Thu Mar 22, 2012 7:04 am

What version of DataStage are you using? The Oracle Connector has the ability to reference an LOB data type -- at least in 8.5 and above. I can't remember if 8.0 or 8.1 had this capability.

deepu_n55 · Post by **deepu_n55** » Thu Mar 22, 2012 7:24 am

I am using 8.1 version and Oracle Enterprise stage . this doesn't have CLOB datatype

kwwilliams · Post by **kwwilliams** » Thu Mar 22, 2012 7:27 am

I knew from your previous you are using the Oracle Enterprise Stage. Do you have the Oracle Connector and could you try that instead? Your requirement is to move data, not to move it with a particular stage.

deepu_n55 · Post by **deepu_n55** » Thu Mar 22, 2012 7:36 am

No i don't have Oracle Connector stage available. any solutions ??

chulett · Post by **chulett** » Thu Mar 22, 2012 8:04 am

As noted, try casting the column to a supported datatype. Or upgrade DataStage.

deepu_n55 · Post by **deepu_n55** » Thu Mar 22, 2012 8:12 am

I tried using LongVarchar ,LongNvarchar, LongVarBInary too .. still doesnt work . do i have to make any changes in the size field? i appreciate ur help, guys
thank you

chulett · Post by **chulett** » Thu Mar 22, 2012 9:22 am

You need to do more than change the datatype in the job, you need to do something like CAST on the field in your source query as well.

deepu_n55 · Post by **deepu_n55** » Thu Mar 22, 2012 9:52 am

Thanks Craig,
Im not soo good at Queries can you tell me how to do that? I appreciate you.

chulett · Post by **chulett** » Thu Mar 22, 2012 9:09 pm

Talk to someone you work with that can help - DBA, Architect, whomever.

Google can also help as well. For example, a search for "oracle cast clob to varchar2" returns many results, including this one from the perennial favorite Ask Tom.

svga · Post by **svga** » Mon Mar 26, 2012 12:53 pm

Pls try using VARCHAR(999999999) datatype..