Home » SQL & PL/SQL » SQL & PL/SQL » Converting BLOB data to long or varchar (oracle 11g)
Converting BLOB data to long or varchar [message #576557] Wed, 06 February 2013 04:19 Go to next message
harry_Oracle
Messages: 2
Registered: February 2013
Location: INDIA
Junior Member
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;
/



[Edit MC: delete poll and code tags]

[Updated on: Wed, 06 February 2013 04:46] by Moderator

Report message to a moderator

Re: Converting BLOB data to long or varchar [message #576564 is a reply to message #576557] Wed, 06 February 2013 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A BLOB is something binary and not a string, so it can't be retrieve in string unless you want a string that gives the hexadecimal values of each byte of the BLOB.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Converting BLOB data to long or varchar [message #576566 is a reply to message #576564] Wed, 06 February 2013 04:42 Go to previous message
harry_Oracle
Messages: 2
Registered: February 2013
Location: INDIA
Junior Member
Yes Michel, But is there any way we can get the the charcters or string value?

how to get the hexadecimal values of each byte of the
Previous Topic: Conditional query using SYSDATE ignored and all rows returned, regardless
Next Topic: DBMS_OUTPUT Error
Goto Forum:
  


Current Time: Fri Apr 18 08:08:20 CDT 2014

Total time taken to generate the page: 0.05307 seconds