Page 1 of 1
Error in extracting records having CLOB datatype.
Posted: Wed Mar 21, 2012 7:01 am
by deepu_n55
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
Posted: Wed Mar 21, 2012 7:19 am
by priyadarshikunal
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.
Posted: Wed Mar 21, 2012 10:44 am
by PaulVL
Cut and pasting the error message here would help as well.
Posted: Thu Mar 22, 2012 6:57 am
by deepu_n55
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
Posted: Thu Mar 22, 2012 7:04 am
by kwwilliams
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.
Posted: Thu Mar 22, 2012 7:24 am
by deepu_n55
I am using 8.1 version and Oracle Enterprise stage . this doesn't have CLOB datatype
Posted: Thu Mar 22, 2012 7:27 am
by kwwilliams
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.
Posted: Thu Mar 22, 2012 7:36 am
by deepu_n55
No i don't have Oracle Connector stage available. any solutions ??
Posted: Thu Mar 22, 2012 8:04 am
by chulett
As noted, try casting the column to a supported datatype. Or upgrade DataStage.
Posted: Thu Mar 22, 2012 8:12 am
by deepu_n55
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
Posted: Thu Mar 22, 2012 9:22 am
by chulett
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.
Posted: Thu Mar 22, 2012 9:52 am
by deepu_n55
Thanks Craig,
Im not soo good at Queries can you tell me how to do that? I appreciate you.
Posted: Thu Mar 22, 2012 9:09 pm
by chulett
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.
Posted: Mon Mar 26, 2012 12:53 pm
by svga
Pls try using VARCHAR(999999999) datatype..