SQL Developer

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;