从Oracle BLOB到base64 CLOB [英] Oracle BLOB to base64 CLOB

问题描述

我可以一次性将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;

我知道我可以添加功能/存储的proc来完成工作.性能方面非常重要，所以我问是否有办法通过直接将数据推入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.

推荐答案

提供了存储的proc尽管对您来说是可行的替代方法，但这是您解决问题的一种可能的解决方法...

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.

这篇关于从Oracle BLOB到base64 CLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！