Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

BLOB to Varchar2

user477692Feb 6 2013 — edited Feb 6 2013
Hi All,

Can someone please help me in converting BLOB data into varchar2 or long .

we have function which convert long data and return it has varchar . But has part of Apps upgrade the Column has been converted into blob column.

How we need the same function to read the data from BLOB and return its as long or varchar2.

Somewhere i am making mistake..

CREATE OR REPLACE function GDS.test_alert_msg(v_rowid rowid) return varchar2 is

vblob blob;
i2 number;
amt number :=32767;
len number;
pos raw(32767);
position INTEGER := 10000;
my_vr raw(32767);
begin

select m.GDTXFT into vblob from jdedta.f00165 m where rowid = v_rowid;

len := DBMS_LOB.GETLENGTH(vblob);
position := DBMS_LOB.INSTR(UTL_RAW.CAST_TO_VARCHAR2(vblob),'^',1,1);

dbms_output.put_line('Length of the Column : ' || to_char(len));
dbms_output.put_line(position);
i2 := 1;

if len < 32767 then

DBMS_LOB.READ(vblob,len,i2,my_vr);
POS:= DBMS_LOB.substr(UTL_RAW.CAST_TO_VARCHAR2(POS),len,1);
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(POS));
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(my_vr));
else

DBMS_LOB.READ(vblob,amt,i2,my_vr);
POS:= DBMS_LOB.substr(vblob,amt,1);
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(POS));

dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(my_vr));
end if;
i2 := i2 + amt;
while (i2 > len) loop
-- dbms_output.put_line('i2 : ' || to_char(i2));
DBMS_LOB.READ(UTL_RAW.CAST_TO_VARCHAR2(my_vr),amt,i2,my_vr);
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(my_vr));
i2 := i2 + amt ;
end loop;
return(UTL_RAW.CAST_TO_VARCHAR2(pos));

end test_alert_msg;

Comments

riedelme
This forum is about SQL*Developer issues. You should get a better answer faster if you post in the General Oracle forum
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 6 2013
Added on Feb 6 2013
1 comment
500 views