问题描述
我可以一次性将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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！