Reading a CLOB data type using oracle enterprise stage

[somu_june]

Hi ,

I'm trying to read a CLOB data type in datastage 7.5.2 using oracle Enterprise stage and my oracle is 10g

in oracle


DESC Test_CLOB

Name Null? Type
----------------------------------------- -------- --------
TEST CLOB


select dbms_lob.substr( TEST, 4000, 1 ) as TEST from Test_CLOB;

I got output as 123 in oracle


I used same SQL in datastage and mentioned column as Varchar2 with out length the job is running fine but I could not able to see any data as I mentioned above like 123 in a dataset .

I tried with utl_raw.cast_to_varchar2 in oracle but it is giving me some pound and space stuff .
select utl_raw.cast_to_varchar2 ( dbms_lob.substr( TEST, 4000, 1 )) as TEST from Test_CLOB;

TEST
---------
#

My aim is to read CLOB column data and write to a dataset. Any help is appricated.


Thanks,
Raju.

[chulett]

Your "select dbms_lob.substr" SQL should work fine as long as you'll never need more than 4k from the CLOB. Did it select zero rows or you didn't like the data it selected?

[somu_june]

Hi chulett,

In oracle it selected the row which I mentioned 123, in datastage oracle Enterprise stage I used the same sql select dbms_lob.substr( TEST, 4000, 1 ) as TEST from Test_CLOB and with sql type as varchar and with no length specified and it is returning no rows and no rows written to a dataset but job is running successfully . When I tried to view data from oracle enterprise stage


##I TFOP 000163 12:31:13(000) <_Head,0> Input 0 consumed 0 records.
##I TFOP 000094 12:31:13(001) <_Head,0> Output 0 produced 0 records.
##I TFOP 000094 12:31:13(000) <Oracle_Enterprise_30,0> Output 0 produced 0 records.
##I TOPK 000163 12:31:13(002) <_PEEK_IDENT_,0> Input 0 consumed 0 records.
##I TOPK 000094 12:31:13(003) <_PEEK_IDENT_,0> Output 0 produced 0 records.
**VIEWDATA ROW LIMITER HIT**



Thanks,
Raju.

[chulett]

Hmmm... to be honest, I've done this in the OCI stage in a Server job. For PX, perhaps you could declare the field as a LongVarchar, see if that works?

[somu_june]

Hi chulett,

Thanks for reply. Sorry it is my fault I forgot to commit after I inserted the record in database. After I do a commit parallel job is doing fine with both Varchar and LongVarchar.



Thanks,
Raju