CODEWITHSUNDEEP
oracle-apex
Kevin
Kevin

Reputation: 71

oracle apex file upload

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?

Upvotes: 7

Views: 18253

Answers (2)

Ahmed Bilal
Ahmed Bilal

Reputation: 1

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;

Upvotes: -1

Tom
Tom

Reputation: 7028

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)

Upvotes: 6

Related Questions