我如何使用Oracle utl_file编写图像Clob [英] How do I use Oracle utl_file to write an image clob

问题描述

我有一个可自动生成电子邮件的Oracle Apex应用程序.在Apex中，用户将JPG图像插入到RTF字段中.该图像将保存到CLOB字段中.调用存储过程时，它将读取JPG图像并将其存储到名为l_image_clob的局部变量中.该程序将嵌入式图像(请注意:这是嵌入式图像，而不是电子邮件附件)连同电子邮件正文的其余部分一起发送给用户列表.一切都很好.
现在，我正在尝试将l_image_clob中存储的JPG图像的内容保存到Windows服务器上的JPG文件中.以下代码生成一个文件，该文件的名称正确且大小正确，但系统无法读取.尝试使用Microsoft Paint打开它时收到错误消息这不是有效的位图文件".如何使用utl_file执行此操作?

I have an Oracle Apex application which generates automated eMails. In Apex, the user inserts a JPG image into a rich text field. That image is saved into a CLOB field. When the stored procedure is called, it reads the JPG image and stores it into a local variable called l_image_clob. The program sends the embedded image (note: this an embedded image and it is not an eMail attachment) along with the rest of the eMail body to a list of users. That's all working fine.
Now I'm attempting to save the contents of the JPG image stored in l_image_clob to a JPG file on the windows server. The following code produces a file, named properly and the size is correct, but it isn't readable by the system. I get the error "this is not a valid bitmap file" when I try to open it with Microsoft Paint. How to I use utl_file to do this?

Here's the code which creates the file that is "not a valid bitmap file"
      -- Create a file based on the content of l_image_clob
      l_image_filename := 'image_' || p_event_pkey || '_' || i ||
      '.' || l_image_ext;
      l_file_handle := utl_file.fopen(l_dirname , l_image_filename, 'wb');
      -- wb is write byte. This returns file handle
      <<inner_loop>>
      for i in 1 .. ceil( length( l_image_clob ) / chnksz )
      loop
        utl_file.put_raw( l_file_handle, 
          utl_raw.cast_to_raw( substr( l_image_clob, (i-1) * chnksz + 1, chnksz )));
        utl_file.fflush(l_file_handle);
      end loop inner_loop; 
      utl_file.fclose(l_file_handle); 

感谢您的关注.

推荐答案

我找到了答案. Apex启动的图像是base64编码的.因此，我不得不对其进行解码.有人帮助我完成了一个程序.我修改后的代码现在看起来像这样:

I found an answer. The Apex-initiated image was base64 encoded. Therefore I had to Decode it. Someone helped me with a procedure to do this. My modified code now looks like this:

-- Create a file based on the content of l_image_clob
l_image_filename := 'image_' || p_event_pkey || '_' || i ||
'.' || l_image_ext;
clob_base64_to_file(l_image_clob, l_dirname, l_image_filename);

被调用的过程如下:

create or replace procedure clob_base64_to_file( 
  p_clob        in  clob, 
  p_dir         in  varchar2, 
  p_filename    in  varchar2
  )
is
  t_buffer          varchar2(32767);
  t_pos             number := 1;
  t_len             number;
  t_fh              utl_file.file_type;
  t_size            number := nls_charset_decl_len( 32764, 
                    nls_charset_id( 'char_cs' ) );
begin
  t_fh := utl_file.fopen( p_dir, p_filename, 'wb', 32767 );
  t_len := length( p_clob );
  loop
    exit when t_pos > t_len;
    t_buffer := replace( replace( substr( p_clob, t_pos, t_size ), 
                chr(10) ), chr(13) );
    t_pos := t_pos + t_size;
    while t_pos <= t_len and mod( length( t_buffer ), 4 ) > 0
    loop
      t_buffer := t_buffer || replace( replace( substr( p_clob, t_pos, 1 ), 
                chr(10) ), chr(13) );
      t_pos := t_pos + 1;
    end loop;
    utl_file.put_raw( t_fh, 
      utl_encode.base64_decode( utl_raw.cast_to_raw( t_buffer ) ) );
  end loop;
  utl_file.fclose( t_fh );
end;

当我调用clob_base64_to_file过程时，它将解码图像并根据我在调用中提供的目录和文件名创建文件.

When I call the clob_base64_to_file procedure it decodes the image and creates a file based on the directory and filename I supply in the call.

这篇关于我如何使用Oracle utl_file编写图像Clob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！