How to parse BLOB datatype?

Question

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:

blob column:

{.\.r.t.f.1.\.d.e.f.f.0.

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?

May be you should convert to clob first? check this out : convert to clob — PKey
– PKey, Commented Jun 26, 2020 at 9:08
I can convert to clob, but after split I can't convert to blob again. — phe
– phe, Commented Jun 26, 2020 at 11:08

Roberto Hernandez · Accepted Answer · 2020-06-26 11:13:36Z

1

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

edited Jun 26, 2020 at 11:13

answered Jun 26, 2020 at 10:05

Roberto Hernandez

8,5833 gold badges22 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

phe Over a year ago

I can convert to clob, but after split I can't convert to blob again. I need same blob values, just split it.

Roberto Hernandez Over a year ago

so you convert to clob, then you split your clob . Perhaps you need to another function to convert back again to blob.

Roberto Hernandez Over a year ago

Hi @phe . I added a functioon to convert back to blob

Collectives™ on Stack Overflow

How to parse BLOB datatype?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related