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.
Reading a CLOB data type using oracle enterprise stage
Moderators: chulett, rschirm, roy
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.
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.
somaraju