Upload-Download BLOB via DB LINK in ORACLE APEX

One of strategy for maintaining files is in Database it means can be read and write on tables. So it can be to maintain in another instance but when read, write BLOB files via DB Link occurs some issues and deal with other ways that refers to:

unable to select image from remote database

Select a blob across a database link, without getting ORA-22992

Oracle Database:Select BLOB via DB Link

Web services is first solution, must be to implement a Service on remote database(file storage) e.g. can be install APEX and create a Restful Web service or install some API on database or with JDeveloper create a Web service and next the PROD database call web service on remote database(file storage).

Another solution is to upload and download via DB link in Oracle APEX. 

Step 1: Create two Tables in order to maintain files in remote database

Connect to FILEUSER on remote database:

CREATE TABLE FILES_MIMETYPE_TB(ID   NUMBER(10) NOT NULL,
                               NAME            VARCHAR2(200 CHAR)   NOT NULL,
                               MIME_TYPE       VARCHAR2(200 CHAR)   NOT NULL,
                               FILE_EXTENSION  VARCHAR2(30 CHAR)    NOT NULL,
                               MORE_DETAILES   VARCHAR2(200 CHAR))

This table uses for MIMETYPE and for obtain those data go to end of blog.

Why do we use this table?
Generally, this table is maintaining storage files so may be fill the other way it means will not fill from APEX applications.[Will not need this table if you fill table only from APEX applications because you could get from apex_application_temp_files table]

Next create table in order to have blob storage, yet it’s sample table and depends of your request change this structure for instance add fields or index;

CREATE TABLE FILES_TB(ID  NUMBER   NOT NULL,
                      MIME_TYPE  VARCHAR2(200 CHAR)  NOT NULL,
                      FILE_NAME  VARCHAR2(100 CHAR)  NOT NULL,
                      FILE_SIZE  NUMBER, 
                      FILE_CONTENT  BLOB  NOT NULL,
                      TABLE_NAME    VARCHAR2(30 CHAR)  NOT NULL,
                      TABLE_ID      NUMBER  NOT NULL)
...

Step 2: Create a DB link from PROD database to files storage Database

Connect to sys on PROD database:

CREATE PUBLIC DATABASE LINK "UDFILES_DBLINK"
 CONNECT TO FILEUSER
 IDENTIFIED BY FILEUSER
 USING 'FILESDB';

Step 3: Create a temp table

Connect to own user on PROD database  and grant to other scheme:

CREATE GLOBAL TEMPORARY TABLE BLOB_TEMP( FILE_CONTENT  BLOB)
ON COMMIT PRESERVE ROWS
RESULT_CACHE (MODE DEFAULT)
NOCACHE;

Step 4: Create a function to get blob via DB Link

Connect to own user on PROD database  and grant to other schemes:

CREATE OR REPLACE FUNCTION GETBLOB_VIA_DBLINK
(P_DBLINK IN VARCHAR2
,P_TABLE  IN VARCHAR2
,P_COL    IN VARCHAR2
,P_ROWID  IN UROWID
,P_WHERE  IN VARCHAR2 DEFAULT NULL
)

RETURN BLOB
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_return_blob BLOB;
  l_sql VARCHAR2(1000);
  l_where VARCHAR2(1000);
  
BEGIN

 IF P_WHERE IS NOT NULL THEN
    l_where :=' And '||P_WHERE;
 ELSE
    l_where :=' And 1=1';   
 END IF;
  
  
  l_sql := 'Insert /*+ NOLOGGING */ into BLOB_TEMP select '||P_COL||' from '||P_TABLE||'@'||P_DBLINK||' where rowid=:P_ROWID'||l_where;
  EXECUTE IMMEDIATE l_sql USING P_ROWID;
  
  SELECT FILE_CONTENT INTO l_return_blob FROM BLOB_TEMP;
  DELETE /*+ NOLOGGING */ FROM BLOB_TEMP;
  COMMIT;
  
  RETURN l_return_blob;
  
END GETBLOB_VIA_DBLINK;

Step 5: Create a sample table

Connect to own user on PROD database:

Create table mytable (id number, description varchar2(100))

Step 6: Create a View on BLOB table via DB Link

For every table that would like to have blob field you should create a special view itself.

Connect to own user on PROD database:

CREATE OR REPLACE FORCE VIEW MYTABLEBLOB
AS
SELECT      ID
           ,MIME_TYPE
           ,FILE_NAME
           ,dbms_lob.getlength(FILE_CONTENT) FILE_SIZE
           ,TABLE_NAME
           ,TABLE_ID
           ,FILE_CONTENT
FROM(
    SELECT  ID
           ,MIME_TYPE
           ,FILE_NAME
           ,TABLE_NAME
           ,TABLE_ID
           ,GETBLOB_VIA_DBLINK('UDFILES_DBLINK','FILEUSER.FILES_TB','file_content',rowid,q'[TABLE_NAME = 'MYTABLE']') FILE_CONTENT
    FROM    FILEUSER.FILES_TB@UDFILES.SAIPA.IR
    Where  TABLE_NAME = 'MYTABLE'
    )

Step 7: We want to implement Upload file via DB Link so create a procedure in order to insert/update/delete files

Connect to own user on PROD database  and grant to other schemes:

CREATE TABLE TEMPMBLOB(ID NUMBER, 
                       FILE_ID NUMBER, 
                       FILENAME VARCHAR2(400), 
                       BLOB_CONTENT BLOB, 
                       SESSION_ID NUMBER)

Why do we have to insert into temp table on PROD database before insert into remote table?

If we wants to insert to remote blob table directly from apex_application_temp_files that will occur below error:

ORA-02069: global_names parameter must be set to TRUE for this operation

In this situation you must change the oracle parameter but sometimes the DBA don’t want to change this parameter for any reason and we must insert into temp table before insert into remote table

CREATE OR REPLACE PROCEDURE DML_BLOB_VIA_DBLINK(p_request VARCHAR2,
                                                p_item_name VARCHAR2, 
                                                p_table_name VARCHAR2,
                                                p_table_id NUMBER)

IS
  l_id  NUMBER;
  l_file_id NUMBER;
  l_filecontent BLOB;
  l_filename VARCHAR2(400);
  l_mimetype VARCHAR2(500);
  l_session  NUMBER;
  
BEGIN
    
    --Insert temp table  
    If p_request IN ('CREATE','SAVE') THEN
        
        
        Select ID, FILENAME, BLOB_CONTENT,nv ('SESSION'), MIME_TYPE
        Into   l_file_id, l_filename, l_filecontent, l_session, l_mimetype
        From   apex_application_temp_files
        Where  NAME = p_item_name;
       
        /*
        Why do we have to insert into temp table on PROD databse before insert into remote table?
        If we wants to insert to remote blob table directly from apex_application_temp_files that will occur below error:
        ORA-02069: global_names parameter must be set to TRUE for this operation

        In this situation you must change the oracle parameter but
        sometimes the DBA don’t want to change this parameter for any reason 
        and we must insert into temp table before insert into remote table
        */ 
        
        Insert Into TEMPMBLOB(FILE_ID, FILENAME, BLOB_CONTENT, SESSION_ID)
            Values(l_file_id, l_filename, l_filecontent, l_session) Returning ID Into l_id;
         
    End If;          
  
    If p_request = 'DELETE' Then
        
        Delete FILEUSER.FILES_TB@UDFILES_DBLINK
        Where  TABLE_NAME = p_table_name
        And    TABLE_ID = p_table_id;
        
    
    ElsIf p_request = 'CREATE' Then
    
      Insert Into FILEUSER.FILES_TB@UDFILES_DBLINK(FILE_NAME, FILE_CONTENT, TABLE_NAME, TABLE_ID)
          Select FILENAME, BLOB_CONTENT, P_TABLE_NAME, P_TABLE_ID
          From   TEMPMBLOB
          Where  ID = l_id  
          And    FILE_ID = l_file_id
          And    SESSION_ID = l_session; 
            
        
    ElsIf p_request = 'SAVE' Then

        Update FILEUSER.FILES_TB@UDFILES_DBLINK Set 
                                                      FILE_CONTENT = (Select BLOB_CONTENT 
                                                                      From   TEMPMBLOB
                                                                      Where  ID = l_id  
                                                                      And    FILE_ID = l_file_id
                                                                      And    SESSION_ID = l_session),
                                                      FILE_NAME = l_filename
        Where  TABLE_NAME = p_table_name
        And    TABLE_ID = p_table_id;
    
    End If;
    
    --Delete temp table
    If p_request IN ('CREATE','SAVE') THEN
        Delete TEMPMBLOB
        Where  ID = l_id  
        And    FILE_ID = l_file_id
        And    SESSION_ID = l_session;
    End If;
    
    Commit;
    
END DML_BLOB_VIA_DBLINK;

Final step: create two pages for upload/download files from APEX application

Now, create a form page (Upload) on MYTABLE and set such as images: in this sample the number of page is 2

Next, create another page for download files which in this case we should have an interactive report page on MYTABLE and in the following match page items like images:

You run and enjoy it.

Download all scripts and application:

UDBLOBDBLINK

All suggestions are welcome!