oracle apex file upload

Question

I want to change the way APEX uploads files. I don't want files get into database tables as a BLOB. Instead, I want'em to get right to OS directory on the machine where apex is running. Is it possible? If so, what do I need to start with?

Answer 1

The file browse item will always upload to a BLOB column. If not in a specified table, it'll go to wwv_flow_files (apex_application_files) which is the alternate option. That shouldn't be a dealbreaker though as you can easily clean up the table after you finish processing the 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.

Example apex process:

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;

For even more documentation, there is of course always google, the oracle documentation on all objects used here, or even the oracle forums (OTN apex forums or OTN PL/SQL forums for example)

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;