Oracle BLOB 到 base64 CLOB

Oracle BLOB to base64 CLOB

提问by JavaSheriff

Can I convert an oracle BLOB to Base64 CLOB in One go?

我可以一次性将 oracle BLOB 转换为 Base64 CLOB 吗？

like:

喜欢：

CREATE TABLE test
(
image BLOB,
imageBase64 CLOB
);

INSERT INTO test(image)
VALUES (LOAD_FILE('/full/path/to/new/image.jpg'));

UPDATE test SET imageBase64 = UTL_ENCODE.base64_encode(image);

commit;

I know I can add functions/Stored proc to do the work. Performance aspect is very important,so I am asking if there is a way to overcome the 32K limitation by directly pushing the data into a CLOB.

我知道我可以添加函数/存储过程来完成这项工作。性能方面非常重要，所以我问是否有办法通过直接将数据推送到 CLOB 来克服 32K 限制。

采纳答案by nop77svk

Provided that stored procs would despite be a viable alternative for you, here's one possible solution to your problem ...

如果存储过程对您来说是一个可行的替代方案，那么这里有一个可能的解决方案来解决您的问题......

First, let's make that nice base64encode()function of Tim Hall's into a procedure ...

首先，让我们base64encode()将 Tim Hall 的好函数变成一个程序......

create or replace procedure base64encode
    ( i_blob                        in blob
    , io_clob                       in out nocopy clob )
is
    l_step                          pls_integer := 22500; -- make sure you set a multiple of 3 not higher than 24573
    l_converted                     varchar2(32767);

    l_buffer_size_approx            pls_integer := 1048576;
    l_buffer                        clob;
begin
    dbms_lob.createtemporary(l_buffer, true, dbms_lob.call);

    for i in 0 .. trunc((dbms_lob.getlength(i_blob) - 1 )/l_step) loop
        l_converted := utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(i_blob, l_step, i * l_step + 1)));
        dbms_lob.writeappend(l_buffer, length(l_converted), l_converted);

        if dbms_lob.getlength(l_buffer) >= l_buffer_size_approx then
            dbms_lob.append(io_clob, l_buffer);
            dbms_lob.trim(l_buffer, 0);
        end if;
    end loop;

    dbms_lob.append(io_clob, l_buffer);

    dbms_lob.freetemporary(l_buffer);
end;

The "trick" here is to directly use the persistent LOB locators in calls to procedures/functions. Why "persistent"? Because if you create a function that returns a LOB, then there's a temporary LOB created in background and this means some TEMP disk/memory usage and LOB content copying involved. For large LOBs this may imply a performance hit. In order to satisfy your requirement of making this the most performing possible, you should avoid this TEMP space usage. Hence, for this approach, a stored procedure instead of a function must be used.

这里的“技巧”是在调用过程/函数时直接使用持久性 LOB 定位器。为什么是“持久”？因为如果您创建一个返回 LOB 的函数，则会在后台创建一个临时 LOB，这意味着涉及一些 TEMP 磁盘/内存使用和 LOB 内容复制。对于大型 LOB，这可能意味着性能受到影响。为了满足您的要求，使其尽可能发挥最佳性能，您应该避免使用这种 TEMP 空间。因此，对于这种方法，必须使用存储过程而不是函数。

Then, of course, the procedure must be fed with persistent LOB locators. You have to do that, again, with a stored procedure, where you e.g. insert an empty LOB (effectively creating a new LOB locator) to a table first, and then supplying that newly created LOB locator to the base64 encoding routine ...

然后，当然，必须使用持久的 LOB 定位器来提供该过程。您必须再次使用存储过程执行此操作，例如，首先将一个空的 LOB（有效地创建一个新的 LOB 定位器）插入到一个表中，然后将新创建的 LOB 定位器提供给 base64 编码例程......

create or replace procedure load_and_encode_image
    ( i_file_name       in varchar2 )
is
    l_input_bfile       bfile := bfilename('DIR_ANYTHING', i_file_name);
    l_image_base64_lob  test.imageBase64%type;
    l_image_raw         test.image%type;
begin
    insert into test(image, imageBase64)
    values (empty_blob(), empty_clob())
    returning image, imageBase64
    into l_image_raw, l_image_base64_lob;

    begin
        dbms_lob.fileopen(l_input_bfile);
        dbms_lob.loadfromfile(
            dest_lob => l_image_raw,
            src_lob => l_input_bfile,
            amount => dbms_lob.getlength(l_input_bfile)
        );
        dbms_lob.fileclose(l_input_bfile);
    exception
        when others then
            if dbms_lob.fileisopen(l_input_bfile) = 1 then
                dbms_lob.fileclose(l_input_bfile);
            end if;
            raise;
    end;

    base64encode(
        i_blob => l_image_raw,
        io_clob => l_image_base64_lob
    );
end;

Note:Of course, if you base64-encode only small files (the actual size depends on your PGA settings, I guess; a question for a DBA, this is), then the function-based approach may be equally performing than this procedure-based one. Base64-encoding a 200MB file on my laptop took 55 seconds with the function+update approach, 14 seconds with the procedure approach. Not exactly a speed demon, so choose what suits your needs.

注意：当然，如果您只对小文件进行 base64 编码（实际大小取决于您的 PGA 设置，我猜；这是 DBA 的问题），那么基于函数的方法可能与此过程的性能相同-基于一个。在我的笔记本电脑上对 200MB 文件进行 Base64 编码，使用函数+更新方法需要 55 秒，使用过程方法需要 14 秒。不完全是速度恶魔，所以选择适合您的需求。

Note:I believe this procedure-based approach may be further speeded up by reading the file to inmemory chunks in loop, base64-encoding the chunks to another inmemory chunks and appending them both to the target persistent LOBs. That way you should make the workload even easier by avoiding re-reading the full test.imageLOB contents by the base64encode()procedure.

注意：我相信这种基于过程的方法可以通过循环读取文件到内存块中来进一步加速，base64 将块编码到另一个内存块并将它们都附加到目标持久性 LOB。这样，您应该避免test.image通过base64encode()过程重新读取完整的LOB 内容，从而使工作负载更加轻松。

回答by dimm

This function got from hereshould do the job.

从这里得到的这个函数应该可以完成这项工作。

CREATE OR REPLACE FUNCTION base64encode(p_blob IN BLOB)
  RETURN CLOB
-- -----------------------------------------------------------------------------------
-- File Name    : http://oracle-base.com/dba/miscellaneous/base64encode.sql
-- Author       : Tim Hall
-- Description  : Encodes a BLOB into a Base64 CLOB.
-- Last Modified: 09/11/2011
-- -----------------------------------------------------------------------------------
IS
  l_clob CLOB;
  l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
  FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step) LOOP
    l_clob := l_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob, l_step, i * l_step + 1)));
  END LOOP;
  RETURN l_clob;
END;
/

Then the update can look like

然后更新看起来像

UPDATE test SET imageBase64 = base64encode(image);

Note that maybe the function should be optimized with the function DBMS_LOB.APPEND instead of that concatenation operator. Try that if you have performance problems.

请注意，可能应该使用函数 DBMS_LOB.APPEND 而不是该连接运算符来优化该函数。如果您有性能问题，请尝试。

回答by Joshua Huber

I solved this same problem at work by using a Java stored procedure. There is no chunking/contatenation of VARCHAR2s involved in such an approach, since the ability to encode/decode base64 is natively built into Java, simply writing an Oracle function that thinly wraps the Java method works well and is high-performance since as soon as you've executed it a few times, the HotSpot JVM compiles the Java proc into low-level code (high performance just like a C stored function). I'll edit this answer later and add the details about that Java code.

我在工作中使用 Java 存储过程解决了同样的问题。这种方法不涉及 VARCHAR2 的分块/合并，因为编码/解码 base64 的能力是原生内置于 Java 中的，只需编写一个简单地包装 Java 方法的 Oracle 函数就可以很好地工作并且是高性能的，因为只要您已经执行了几次，HotSpot JVM 将 Java proc 编译为低级代码（高性能就像 C 存储函数一样）。稍后我将编辑此答案并添加有关该 Java 代码的详细信息。

But to step back just one step, question why are you storing this data as both a BLOB and base64 encoded (CLOB)?Is it because you have clients that want to consume the data in the latter format? I'd really prefer to only store the BLOB format. One reason why is that the base64 encoded version can be double the size of the original binary BLOB, so storing them both means possibly 3x the storage.

但是，退一步说，为什么要将这些数据同时存储为 BLOB 和 base64 编码 (CLOB)？是因为您有客户想要使用后一种格式的数据吗？我真的更喜欢只存储 BLOB 格式。一个原因是 base64 编码版本的大小可能是原始二进制 BLOB 的两倍，因此将它们都存储意味着可能是存储空间的 3 倍。

One solution, the one I implemented at work, to create your own Java stored function base64_encode()that encodes binary --> base64 and then use that function to encode base64 on the fly at query time (it's not expensive). From the application/client side, you would query something like SELECT base64_encode(image) FROM test WHERE ...

一种解决方案，即我在工作中实现的解决方案，用于创建您自己的 Java 存储函数base64_encode()，该函数对二进制文件进行编码 --> base64，然后使用该函数在查询时即时对 base64 进行编码（这并不昂贵）。从应用程序/客户端，您将查询类似SELECT base64_encode(image) FROM test WHERE ...

If the application code can't be touched (ie COTS application) or if your developers aren't thrilled about using a function, you could abstract this for them (since you are using 11g+) by using a VIRTUAL (computed) column on the table which contains the computed base64_encode(image). It would function like a view, in that it wouldn't physically store the encoded CLOBs, but would generate them at query time. To any client, they would not be able to tell they are not reading a physical column. The other benefit is that if you ever update the jpg (BLOB), the virtual CLOB is immediately and automatically updated. If you ever have to insert/update/delete a huge batch of BLOBs, you'd save 66% of the redo/archivelog volume from not having to process all the CLOBs.

如果无法触及应用程序代码（即 COTS 应用程序），或者如果您的开发人员对使用函数不感兴趣，您可以通过在包含计算结果的表base64_encode(image)。它的功能类似于视图，因为它不会物理存储编码的 CLOB，但会在查询时生成它们。对于任何客户，他们都无法判断自己没有阅读实体专栏。另一个好处是，如果您更新了 jpg (BLOB)，虚拟 CLOB 会立即自动更新。如果您必须插入/更新/删除大量 BLOB，则不必处理所有 CLOB，您将节省 66% 的重做/归档日志量。

Lastly, for performance, make very sure you are using SecureFile LOBs (both for BLOBs and CLOBs). They really are much faster and better in just about every way.

最后，为了提高性能，请确保您使用的是 SecureFile LOB（用于 BLOB 和 CLOB）。他们确实在几乎所有方面都更快更好。

UPDATE- I found my code, at least the version that uses a Java Stored Procedure to do the opposite (converting a base64 encoded CLOB to its binary BLOB version). It would not be that difficult to write the inverse.

更新- 我找到了我的代码，至少是使用 Java 存储过程执行相反操作的版本（将 base64 编码的 CLOB 转换为其二进制 BLOB 版本）。写出倒数不会那么困难。

--DROP FUNCTION base64_decode ;
--DROP java source base64;

-- This is a PLSQL java wrapper function
create or replace
FUNCTION base64_decode (
   myclob clob)
   RETURN blob
AS LANGUAGE JAVA
   NAME 'Base64.decode (
            oracle.sql.CLOB) 
            return oracle.sql.BLOB';
/

-- The Java code that base64 decodes a clob and returns a blob.
create or replace and compile java source named base64 as
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import sun.misc.BASE64Decoder;
import oracle.jdbc.driver.*;

public class Base64 {

public static oracle.sql.BLOB decode(oracle.sql.CLOB  myBase64EncodedClob)
  {

    BASE64Decoder base64   = new BASE64Decoder();
    OutputStream  outstrm  = null;
    oracle.sql.BLOB myBlob = null;
    ByteArrayInputStream instrm = null;

    try
    {
      if (!myBase64EncodedClob.equals("Null"))
      {
        Connection conn = new OracleDriver().defaultConnection();
        myBlob = oracle.sql.BLOB.createTemporary(conn, false,oracle.sql.BLOB.DURATION_CALL);
        outstrm = myBlob.getBinaryOutputStream();
        ByteArrayOutputStream byteOutStream = new ByteArrayOutputStream();

        InputStream in = myBase64EncodedClob.getAsciiStream();
        int c;
        while ((c = in.read()) != -1)
        {
          byteOutStream.write((char) c);
        }

        instrm = new ByteArrayInputStream(byteOutStream.toByteArray());

        try  // Input stream to output Stream
        {
          base64.decodeBuffer(instrm, outstrm);
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }

        outstrm.close();
        instrm.close();
        byteOutStream.close();
        in.close();
        conn.close();
      }

    }
    catch (Exception e)
    {
        e.printStackTrace();
    }

    return myBlob;

  }  // Public decode

} // Class Base64
;
/

回答by Manuel Vidigal

The easiest way that I found, that works with special characters (in your case you don't have this problem), is using dbms_lob.converttoclob.

我发现的最简单的方法是使用 dbms_lob.converttoclob，它适用于特殊字符（在你的情况下你没有这个问题）。

Create encapsulated Procedure:

创建封装过程：

CREATE OR REPLACE FUNCTION blob2clob(blob_i IN BLOB) RETURN CLOB IS
  l_clob         CLOB;
  l_dest_offset  NUMBER := 1;
  l_src_offset   NUMBER := 1;
  l_amount       INTEGER := dbms_lob.lobmaxsize;
  l_clob_csid    NUMBER := nls_charset_id('WE8ISO8859P15'); --dbms_lob.default_csid;
  l_lang_context INTEGER := dbms_lob.default_lang_ctx;
  l_warning      INTEGER;
BEGIN
  ---------------------------
  -- Create Temporary BLOB --
  ---------------------------
  dbms_lob.createtemporary(lob_loc => l_clob,
                           cache   => TRUE);
  --------------------------
  -- Convert CLOB to BLOB --
  --------------------------
  dbms_lob.converttoclob(dest_lob     => l_clob,
                         src_blob     => blob_i,
                         amount       => l_amount,
                         dest_offset  => l_dest_offset,
                         src_offset   => l_src_offset,
                         blob_csid    => l_clob_csid,
                         lang_context => l_lang_context,
                         warning      => l_warning);
  --
  RETURN l_clob;
END blob2clob;

Then you can use:

然后你可以使用：

blob2clob(utl_encode.base64_encode(image))