2 Replies Latest reply: Jul 22, 2013 12:29 PM by Joe Upshaw RSS

    Uploading Documents to Another Database Through APEX

    KeishaD

      Hi,

       

      I am trying to find a solution for uploading documents through APEX, but storing them in a database on a different server other than the server where apex is installed.

       

      I am unable to use DBLINK, because we are needing to pass a BLOB column. We have also looked into the DAD, but it only allows for one database connection for the APEX instance. I am pondering with creating a database connection to the other database when needing to upload a document, but not certain if that is the most efficient way.

       

      I am wanting to know if there is a way to upload documents (BLOBS) through APEX that is installed on DATABASE A, but store the documents in DATABASE B?

       

      Thanks,

       

      Keisha

        • 1. Re: Uploading Documents to Another Database Through APEX
          Joe Upshaw

          Ok...up front warning...this is a bit klugey but, it does work.

           

          In the APEX database:

           

          1. Create the table (with the BLOB column) to which you will upload the documents
            1. We're going to get them to the remote DB but, for reasons that will become apparent shortly, they need to go here first.

           

          In the Remote database:

           

          1. Create a DB Link to the APEX database.
          2. Create a Materialized View that selects from table with the the BLOB column in the APEX database

           

          In the APEX database:

          1. Create a local synonym that points to the DBMS_REFRESH package in the Remote database.
            1. For this example, we'll name the synonym REMOTE_DBMS_REFRESH

           

          Create a PL/SQL procedure that does the following:

           

          1. Upload the document to the APEX database
          2. Fire the REFRESH method for the MV that you created using REMOTE_DBMS_REFRESH
          3. Remote the data from the APEX database

           

          For whatever reason, the restriction to copy LOB data over the link is by passed when using a Materialized View.

           

          Cheers,

           

          -Joe

          • 2. Re: Uploading Documents to Another Database Through APEX
            Joe Upshaw

            Here's one other, very kludgey way to do it. If you add DUAL to your FROM clause, it will ignore the restriction. Again, I have no idea why.

             

            -- This results in the error "ORA-22992: cannot use LOB locators selected from remote tables"


            SELECT FLNGCRMKEY, FSTRID, FSTRNOTE, FDTMCREATED, FDTMWHEN

            FROM TBLGA_NOTEBIGOUT;

             

            -- This does not

             

            SELECT FLNGCRMKEY, FSTRID, FSTRNOTE, FDTMCREATED, FDTMWHEN

            FROM TBLGA_NOTEBIGOUT, DUAL;