Oracle BLOB 到 base64 CLOB

2021-12-30 00:00:00 oracle11g oracle

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

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

喜欢:

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;

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

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.

推荐答案

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

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

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

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

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;

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

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 定位器)插入到一个表中，然后将新创建的 LOB 定位器提供给 base64 编码例程......

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

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;

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

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将块编码到另一个内存块并将它们都附加到目标来进一步加速持久性 LOB.这样，您应该避免通过 base64encode() 过程重新读取完整的 test.image LOB 内容，从而使工作负载更加轻松.

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.image LOB contents by the base64encode() procedure.

相关文章