DSXchange

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.

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?

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.

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?

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