How to read an oracle CLOB object in Oracle Stage?

yabhinav · Post by **yabhinav** » Thu Aug 06, 2009 3:23 am

The return type of a function that we use is CLOB and I need to use that function in an oracle stage. Is is possible to read a clob object in an Oracle stage. If not is there any workaround for this?

Thanks,
Abhinav

ArndW · Post by **ArndW** » Thu Aug 06, 2009 3:42 am

You can declare the data type as binary in order to read it.

yabhinav · Post by **yabhinav** » Thu Aug 06, 2009 3:46 am

The job aborted with the following error

Code: Select all

main_program: Internal Error: (colDesc):orautils.C: 1722: getColDesc failed.
Traceback: msgAssertion__13APT_FatalPathFPCcRC11APT_UStringPCci() at 0xd583d20c
querySchema__12APT_OraUtilsFPCwP12APT_ErrorLogi() at 0xd6d72248
describeOperator__19APT_OraReadOperatorFv() at 0xd6dd0044
wrapDescribeOperator__15APT_OperatorRepFv() at 0xd500cc60
check1a__15APT_OperatorRepFv() at 0xd500d844
sequenceAndCheck1Operators__11APT_StepRepFR12APT_ErrorLog() at 0xd511e388
check__11APT_StepRepFv() at 0xd511d988
check__8APT_StepFv() at 0xd513d770
createAndCheckStep__7APT_OSLFP20APT_OSL_SIL_StepSpecR12APT_ErrorLog() at 0xd668629c
APT_ORCHESTRATE_main__FiPPc() at 0x10008268

ArndW · Post by **ArndW** » Thu Aug 06, 2009 4:14 am

Ahh, sorry - when writing to Oracle it is easier, in your case you would need to use user-defined SQL and of the form

Code: Select all

select utl_raw.cast_to_varchar2(dbms_lob.substr(BLOBColumn,4000,1)) AS BLOBColumn FROM MyTable

yabhinav · Post by **yabhinav** » Thu Aug 06, 2009 5:01 am

Hi Arnd,

When i try to do the conversion it says invalid hex number. Is it because my Clob data is more than 4000 characters?

And isn't the code mentioned for conversion of blob to varchar. I was looking for Clob to Varchar.

The reason i need this is because i have select statement that has more than 4000 characters, so i'm converting it into Clob

Code: Select all

Select to_clob(column names) as record from tablename

This is returned from a function
Im not able to read the above value.

Thanks,
Abhinav

ArndW · Post by **ArndW** » Thu Aug 06, 2009 7:33 am

The "dbms_lob.substr" function is for clob and clobs alike. Declare the column as varbinary and then don't do an explicit character conversion. You could then work witha column export stage.

You are going to

yabhinav · Post by **yabhinav** » Thu Aug 06, 2009 8:47 am

Thanks a lot I finally got it to work although i didn't use cast_to_varchar2 conversion and I had the output column defined as varchar