Oracle APEX 文件上传

Question

我希望改变APEX上传文件的方式。我不想把文件作为BLOB存储到数据库表中，而是希望它们直接保存在运行APEX的机器上的操作系统目录中。这样是否可行？如果可行，我需要从哪里开始？

- Kevin

Answer 1

文件浏览项将始终上传到BLOB列。如果没有在指定的表中，它将进入wwv_flow_files（apex_application_files）这是备选项。然而这不应该成为致命问题，因为在处理完BLOB后您可以轻松清理该表。

• Determine the location where your files need to end up
• Make sure you have the necessary permissions! (read, write,...)
• Create a directory object in the database which refers to this location: CREATE DIRECTORY statement documentation
• Make sure you have the necessary grants on this object (read, write,...)

• Create a procedure that will write a BLOB to a file. An example of this technique is here (dba-oracle.com). In short, what that will do is:

  • open up a file on the filesystem (a directory is required, and a directory is referred to by the name of the directory object you
    created earlier!)

    -- define output directory
    l_output := utl_file.fopen('DIR_TEMP', 'filename','wb', 32760);
    

    In this example code, the created directory is the DIR_TEMP object

  • take the blob
  • read a piece of it
  • write that piece to the filesystem
  • repeat last 2 steps until the end of the blob has been reached (unless the BLOB is small enough to be written in 1 go)
  • set the file browse item to upload to wwv_flow_files
  • close the file (finish it)
• You could then alter that procedure to take a BLOB as in IN parameter.
• In apex, create an after-submit plsql process. You can call the file-writing procedure there, providing it with the stored blob.
• And clean up the upload table.

示例 Apex 流程：

DECLARE
   v_upl_blob BLOB;
BEGIN
   SELECT blob_content 
     INTO v_upl_blob
     FROM wwv_flow_files
    WHERE name = :Px_FILE_BROWSE_ITEM;

   my_file_write_procedure(v_upl_blob);

   DELETE FROM wwv_flow_files
    WHERE name = :Px_FILE_BROWSE_ITEM;
END;

如果您需要更多的文档资料，当然可以使用谷歌搜索，或查看所有在此处使用的对象的Oracle文档，甚至可以参考Oracle论坛（例如OTN apex论坛 或 OTN PL/SQL论坛）。

- Tom

---

感谢您提供如此详细的解释，尽管我已经按照您所描述的方式完成了。问题是我想省略那些步骤（写入BLOB列->提取到本地目录）。似乎在APEX中不可能实现。 - Kevin

据我所知，不是的。当文件上传到服务器后，你需要一种方式来获取它的句柄，而表中的一个二进制大对象（blob）就是用来实现这个目的的。我想不出有什么办法可以跳过这一步骤。文件必须上传到服务器端，并在那里进行处理。 - Tom

Answer 2

declare
  v_length number;
  v_id number;
begin
   select doc_size
   into v_length
   from   wwv_flow_files
   where  name = :P105_PIC;

if v_length > 20000 then
   delete from wwv_flow_files where name = :P105_PIC;
   commit;

    apex_error.add_error (
    p_message          => 'Cannot upload pictures bigger than 20kB!',
    p_display_location => apex_error.c_inline_in_notification );

end if;
exception
 when others then
    apex_error.add_error (
    p_message          => 'Cannot upload pictures!',
    p_display_location => apex_error.c_inline_in_notification );
end;

- Ahmed Bilal

---

这段代码并没有完成问题所要求的功能；你的代码是在删除一个文件。 - Mark Stewart