You Asked

I have a column of type BLOB which is being used to store images. A typical value that is currently getting stored in this table is:

data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAYEBQYFBAYGBQYHBwYIChAKCgkJChQODwwQFxQYGBcUFhYaHSUfGhsjHBYWICwgIyYnKSopGR8tMC0oMCUoKSj/2wBDAQcHBwoIChMKChMoGhYaKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCj/wAARCAKMBe8DASIAAhEBAxEB/8QAHAABAAIDAQEBAAAAAAAAAAAAAAUGAgMEBwgB/8QASBABAAICAQICBwQECgcIAwAAAAECAwQFBhESIQcTMUFRYXEUIoGhCBUykRYjJEJSYnKCkqIlMzQ1Q7LBRFODo7Gz0fBUc8P/xAAUAQEAAAAAAAAAAAAAAAAAAAAA/8QAFBEBAAAAAAAAAAAAAAAAAAAAAP............

If you look at it, the first few characters in the BLOB i.e. till 'data:image/jpeg;base64,' is not the actual data which forms the image. The base64 data that comprises the image starts only after the comma.Also, it is always the data after the comma that I am interested in.

Now when I write my Data Model SQL for a BI Publisher report, I need to find a way to strip off the characters that do not form the actual image. I am not able to do the same since the column type is BLOB. If I just use the value in the RTF template as is, it does not render the image for me. The rendering engine for the report requires me to supply just the Base64 data that makes the image. Is there a way I can achieve the same in SQL?

and Connor said...

Perhaps something like this:

SQL> create table t ( b blob);

Table created.

SQL> insert into t values (utl_raw.cast_to_raw('data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAYEBQYFBAYGBQYHBwYIChAKCgkJC'));

1 row created.

SQL> select * from t;

B
----------------------------------------------------------------------------------------------------------------------------------------------------------------
646174613A696D6167652F6A7065673B6261736536342C2F396A2F34414151536B5A4A5267414241514141415141424141442F3277424441415945425159464241594742515948427759494368414B43

1 row selected.

SQL>
SQL> select utl_raw.cast_to_raw('/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAYEBQYFBAYGBQYHBwYIChAKCgkJC') from dual;

UTL_RAW.CAST_TO_RAW('/9J/4AAQSKZJRGABAQAAAQABAAD/2WBDAAYEBQYFBAYGBQYHBWYICHAKCGKJC')
------------------------------------------------------------------------------------------------------------------------------------------------------------------
2F396A2F34414151536B5A4A5267414241514141415141424141442F3277424441415945425159464241594742515948427759494368414B43676B4A43

1 row selected.

--
-- the above is just for verification - it tells us we're looking for a blob starting with 2F39...
--

--
-- I look for the "base64" tag
--
SQL>
SQL> select dbms_lob.instr(b,utl_raw.cast_to_raw(';base64')) from t;

DBMS_LOB.INSTR(B,UTL_RAW.CAST_TO_RAW(';BASE64'))
------------------------------------------------
                                              16

1 row selected.

SQL>
SQL> select dbms_lob.substr(
  2           b,
  3           dbms_lob.getlength(b),
  4           8+dbms_lob.instr(b,utl_raw.cast_to_raw(';base64'))
  5           ) from t;

DBMS_LOB.SUBSTR(B,DBMS_LOB.GETLENGTH(B),8+DBMS_LOB.INSTR(B,UTL_RAW.CAST_TO_RAW(';BASE64')))
------------------------------------------------------------------------------------------------------------------------------------------------------------------
2F396A2F34414151536B5A4A5267414241514141415141424141442F3277424441415945425159464241594742515948427759494368414B43676B4A43

1 row selected.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up question

Mudit, September 12, 2018 - 8:35 am UTC

Thanks for your response Connor. I think the solution that you have provided works only when the length of the BLOB is not too long. In my case when I try and execute the solution for an actual BLOB (dbms_lob.getlength gives me 15235 as the length), I get the following error:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.

September 18, 2018 - 3:53 am UTC

Sorry, I was being a bit lazy. Here's how to read chunks in build a larger blob with debugging output so you can follow

SQL> create table t ( b blob);

Table created.

SQL> declare
  2    b1 blob;
  3  begin
  4    insert into t values (utl_raw.cast_to_raw('data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAYEBQYFBAYGBQYHBwYIChAKCgkJC'))
  5    returning b into b1;
  6
  7    for i in 1 .. 1000
  8    loop
  9      dbms_lob.writeappend(b1,
 10         64,
 11         utl_raw.cast_to_raw('asdasdasdAAQSkZJRgABAQAAAQABAAD2wBDAAYEBQYFBAYGBQYHBwYIChAKCgkJC')
 12       );
 13    end loop;
 14    commit;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>
SQL> select dbms_lob.getlength(b) from t;

DBMS_LOB.GETLENGTH(B)
---------------------
                64084

1 row selected.

SQL> select dbms_lob.instr(b,utl_raw.cast_to_raw(';base64')) from t;

DBMS_LOB.INSTR(B,UTL_RAW.CAST_TO_RAW(';BASE64'))
------------------------------------------------
                                              16

1 row selected.

SQL>
SQL>
SQL> variable output blob;
SQL> set serverout on
SQL> declare
  2    l_idx    int;
  3    l_len    int;
  4    l_blob   blob;
  5  begin
  6      select b into l_blob from t;
  7      dbms_lob.createtemporary(:output,true);
  8      l_idx     := 8+dbms_lob.instr(l_blob,utl_raw.cast_to_raw(';base64'));
  9      l_len     := dbms_lob.getlength(l_blob);
 10      dbms_output.put_line('l_idx ' || l_idx );
 11      dbms_output.put_line('l_len ' || l_len );
 12
 13      for i in 1 .. trunc((l_len-l_idx)/2000)+1
 14      loop
 15        dbms_output.put_line('start ' || l_idx );
 16        dbms_output.put_line('amount ' ||least(2000,l_len-l_idx));
 17
 18        dbms_lob.writeappend(:output,
 19                             least(2000,l_len-l_idx),
 20                             dbms_lob.substr(l_blob,least(2000,l_len-l_idx),l_idx)
 21                             );
 22        l_idx := l_idx + 2000;
 23      end loop;
 24  end;
 25  /
l_idx 24
l_len 64084
start 24
amount 2000
start 2024
amount 2000
start 4024
amount 2000
start 6024
amount 2000
start 8024
amount 2000
start 10024
amount 2000
start 12024
amount 2000
start 14024
amount 2000
start 16024
amount 2000
start 18024
amount 2000
start 20024
amount 2000
start 22024
amount 2000
start 24024
amount 2000
start 26024
amount 2000
start 28024
amount 2000
start 30024
amount 2000
start 32024
amount 2000
start 34024
amount 2000
start 36024
amount 2000
start 38024
amount 2000
start 40024
amount 2000
start 42024
amount 2000
start 44024
amount 2000
start 46024
amount 2000
start 48024
amount 2000
start 50024
amount 2000
start 52024
amount 2000
start 54024
amount 2000
start 56024
amount 2000
start 58024
amount 2000
start 60024
amount 2000
start 62024
amount 2000
start 64024
amount 60

PL/SQL procedure successfully completed.

SQL>
SQL> select dbms_lob.getlength(:output) from dual;

DBMS_LOB.GETLENGTH(:OUTPUT)
---------------------------
                      64060

1 row selected.

SQL>

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Follow up question

More to Explore

PL/SQL demos

PL/SQL docs

How to strip off characters upto a specific character in a BLOB type column

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Follow up question

More to Explore

PL/SQL demos

PL/SQL docs