Oracle 如何在BLOB类型列中剥离字符至特定字符

我有一个BLOB类型的列，用于存储图像。当前存储在此表中的典型值是:

数据: image/jpeg;base64，/9j/4 aaqskzjrgabaqaaqabaad/2wBDAAYEBQYFBAYGBQYHBwYIChAKCgkJChQODwwQFxQYGBcUFhYaHSUfGhsjHBYWICwgIyYnKSopGR8tMC0oMCUoKSj/2wBDAQcHBwoIChMKChMoGhYaKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCj/wAARCAKMBe8DASIAAhEBAxEB/8QAHAABAAIDAQEBAAAAAAAAAAAAAAUGAgMEBwgB/8QASBABAAICAQICBwQECgcIAwAAAAECAwQFBhESIQcTMUFRYXEUIoGhCBUykRYjJEJSYnKCkqIlMzQ1Q7LBRFODo7Gz0fBUc8P/xAAUAQEAAAAAAAAAAAAAAAAAAAAA/8QAFBEBAAAAAAAAAAAAAAAAAAAAAP.

如果你看它，BLOB中的前几个字符，即直到 '数据: 图像/jpeg;base64，' 不是形成图像的实际数据。包含图像的base64数据仅在逗号之后开始。此外，我感兴趣的始终是逗号之后的数据。

现在，当我为BI Publisher报告编写数据模型SQL时，我需要找到一种方法来剥离不形成实际图像的字符。我不能做同样的事情，因为列类型是BLOB。如果我只是按现状使用RTF模板中的值，它不会为我渲染图像。报告的渲染引擎要求我只提供制作图像的Base64数据。有没有一种方法可以在SQL中实现相同？

也许是这样的:

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.
复制