5 Replies Latest reply: Jan 31, 2013 9:03 AM by Ramani_apex RSS

    Upload/download PDF Files in local drive of Oracle Server via Oracle APEX

    uzairmalik
      First of all "Hi to all respected Gurus/Members of this forum."

      My requirement is to upload/download PDF Files via Oracle APEX.

      Currently we are dealing with this requirement as that users are directly uploading PDF Files into Oracle Database via Oracle APEX and downloading with the same way.

      But by doing this ".dmp" size is increasing rapidly.

      So, I want to store all that PDFs in local drive of Oracle Server instead of Oracle Database and all that users have the access should be able to upload/download the PDF files on that local drive of Oracle Server via Oracle APEX Application.


      Kindly guide me how to do it?
        • 1. Re: Upload/download PDF Files in local drive of Oracle Server via Oracle APEX
          VC
          Try this APEX plugin http://www.apex-plugin.com/oracle-apex-plugins/process-type-plugin/savetodisk_167.html

          I have never used it before..but just remembered seeing that.
          • 2. Re: Upload/download PDF Files in local drive of Oracle Server via Oracle APEX
            Vite DBA
            Hi,

            this can be done, a lob doesn't have to be stored in the database, but can be an external file referenced by an external lob reference, but, whats the difference! The lobs are still consuming space on your database server, whether they are externally referenced files or lobs in the database as part of the data file (.dbf).

            Regards
            Andre
            • 3. Re: Upload/download PDF Files in local drive of Oracle Server via Oracle APEX
              uzairmalik
              @Vite DBA

              Yes you are right that in both cases space will be consumed on Database Server.

              Can you please guide me how to use external lobs. I'll try to upload files on another server on the same network with the use of Oracle APEX
              • 4. Re: Upload/download PDF Files in local drive of Oracle Server via Oracle APEX
                j.gauger
                Hi,

                the general approach to download files from the filesystem using Apex would be to
                1. Create a directory in your file system the database has access to. Important: The OS user the database is running under must have read and write permissions to this directory.
                2. Create a logical directory object with SQL plus (or whatever) pointing to the physical location and grant read,write to the schema users who want to use it.
                3. Assuming you have some files in the directory, you can use bfilename to reference a file. The general approach would be

                DECLARE
                l_filename BFILE;
                BEGIN
                l_filename := bfilename('<Your directory object>','<filename>');
                ...
                -- Set HTTP header with mime type etc.
                ...
                wpg_docload.download_file(l_filename);
                END;


                To store uploaded files to your filesystem via Apex you can use the APEX_APPLICATION_FILES dictionary view to do this. Besides some general information about file size, type etc. you have a column BLOB_CONTENT containing the file as blob. All you have to do is to write the blob to disk using UTL_FILE, most likely using the procedure put_raw. You will find more information and examples in the database documentation. Once you have written the file to disk you can delete the relating record from APEX_APPLICATION_FILES.

                Regards,
                Jens
                • 5. Re: Upload/download PDF Files in local drive of Oracle Server via Oracle APEX
                  Ramani_apex
                  hi forum friends,

                  I download from apex apex plugin website .and followed the guidelines..i could upload the pdf files to OS file system(as Oracle Directory)..but i could not do the DML operation even download option via apex plugin tools.

                  Please visit my query in previous days......
                  Not Download file from oracle directory
                  
                  at the end i propose DB level file upload and download in apex. becoz we want 2 million pdf pages dealing from  apex application also security.
                  
                  Thanx 
                  Ram