3 Replies Latest reply: Sep 26, 2013 9:19 AM by BCasa RSS

    The Mysterious Disappearing Blob Links

    BCasa

      Hello, newbie here, be gentle, .

      I am using Oracle Apex 4.1, Theme Topaz-4.  I created a form that includes BLOB upload fields if user wants to include attachments.  Table includes blob fields as well as fields for filename, mime type, last update and character set for each blob field.  In the reports, I use the dbms_lob.getlength function in the sql for each blob, and include the download format in the field definition for each blob (ex.

       

      DOWNLOAD:SBR_PROJECT_DETAILS:BUNDLED_YES_DOC_UPLOAD:PROJECT_DETAIL_ID::BUNDLED_YES_MIMETYPE:BUNDLED_YES_FILENAME:attachment:Download
      

      Actual version includes all four associated fields for each blob).  It works great actually, once the file is uploaded a link appears in both the form and the reports.

      The problem comes if the form is edited.  The form does not actually update the original record, but inserts a new one with the changes, so that version control is maintained.  The filename, mime type, last update and character set for each blob field is retained in the new record with no problem, however, the links to the uploaded document no longer work.  It is unclear whether the blob data is being overwritten or some other disconnect is happening.  I have not been able to find anything about whether forms with blobs can be edited, or if there is any trick to this that I am somehow missing.  Any suggestions are greatly appreciated!

        • 1. Re: The Mysterious Disappearing Blob Links
          jrimblas

          Is your form inserting the new record using the Built In Automatic Row Processing process?

          If so, that's the issue.  Your form is saving all the data that was retrieved by the form and the blob is NOT one of those values.  That's why you have the pretty links so that the blob doesn't get pushed to the page every time just in case you need it.

           

          So, I think what you need to do is let the new record be created with the Automatic Row Processing process. Just like you do now.

          Then have a process that runs right after and updates the new BLOB with the old one. Something like this:

           

          update SBR_PROJECT_DETAILS
            set bundled_yes_blob_upload = (select bundled_yes_blob_upload from SBR_PROJECT_DETAILS where project_detail_id = :PREVIOUS_RECORD_ID)
          where project_detail_id = :CURRENT_RECORD_ID;
          

          Tip: Use the Return Key Into Item field in the process to get the new Primary key.

           

          Of course you need to plug in the correct ID values.

          Now, important, this process should only run if your BLOB is empty otherwise you would overwrite a possible new BLOB that got uploaded.

          Make sense?

           

          Hopefully I understood the logic correctly.

           

          Thanks,

          -Jorge

          http://rimblas.com/blog/

          • 2. Re: The Mysterious Disappearing Blob Links
            BCasa

            Jorge, that makes perfect sense, I will give it a try!  Thank you very much.

            • 3. Re: The Mysterious Disappearing Blob Links
              BCasa

              Jorge, your post helped me understand the problem but I never actually was able to fix it, due to new requirements taking precidence before I could get a working solution.  In the meantime, I found more problems due to the uploaded documents being dropped when the form failed validations for any reason, and having to be uploaded again.  I read that the upload process was set up this way for security reasons.  I felt this would be a pain for the users, so I separated the uploads from the main form.  The users only need to upload their attachments once, even if they create a new version of the form, problem solved.  Thank you again.