CODEWITHSUNDEEP
oracle-databaseplsqlblob
onur
onur

Reputation: 6395

How to parse BLOB datatype?

I have a BLOB data like this:

{.\.r.t.f.1.\.d.e.f.f.0.T.a.b.l.o.S.i.m.p.l.e...

I need to split into 2 column with Tablo seperator like this:

  1. blob column:
{.\.r.t.f.1.\.d.e.f.f.0.
  1. blob column:
S.i.m.p.l.e...

Seperator HEX code

2A002A007400610062006C006F002A002A00 --> **Tablo**

Blob data length larger than 10000, largest data 3162910.

I tried dbms_lob.substr, dbms_lob.instr with hex code, blob_to_clob conversions etc, but i can't do that.

How can I do that?

Upvotes: 0

Views: 1125

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

You need to convert the BLOB to a CLOB first, then you can treat the CLOB as a string.

    create or replace function blob_to_char (p1_blob BLOB)
return clob is
  out_clob clob;
  n number;
begin
  if (p1_blob is null) then
    return null;
  end if;
  if (length(p1_blob)=0) then
    return empty_clob();
  end if;
  dbms_lob.createtemporary(out_clob,true);
  n:=1;
  while (n+32767<=length(p1_blob)) loop
    dbms_lob.writeappend(out_clob,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(p1_blob,32767,n)));
    n:=n+32767;
  end loop;
  dbms_lob.writeappend(out_clob,length(p1_blob)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(p1_blob,length(p1_blob)-n+1,n)));
  return out_clob;
end;
/

Once you have this function, you can use it to retrieve the BLOB as CLOB, obviously we are assuming that the BLOB is actually a string stored as binary object.

SELECT BLOB_TO_CHAR(p1_blob => your blob column ) from your table ;

Then, as the output is CLOB, you can apply dbms_lob.substr or any other function.

Additional to convert back to BLOB

    create or replace function clob_to_blob (p1_clob CLOB) return BLOB is
  Result BLOB;
  o1 integer;
  o2 integer;
  c integer;
  w integer;
begin
  o1 := 1;
  o2 := 1;
  c := 0;
  w := 0;
  DBMS_LOB.CreateTemporary(Result, true);
  DBMS_LOB.ConvertToBlob(Result, p1_clob, length(p1_clob), o1, o2, 0, c, w);
  return(Result);
end clob2blob;
/

Once you have applied whatever split you do in your column in clob format, use this function to convert back to blob.

Hope it helps

Upvotes: 1

Related Questions