Tuesday, September 18, 2007

In ApEx, Upload/Dowload BLOB/CLOB stored in another database

Scenario: I have a database named BLOBS_DB which has a table (REPORTS) containing a primary key (PK) column, a BLOB column (BLOB_COL) , and a CLOB column (CLOB_COL). I have HTML DB (predecessor to Oracle Application Express) running in another database named APP_DB. (The names of the databases and objects have been changed to protect the innocent;) I want to be able to upload and download either LOB columns into the BLOBS_DB database from within an HTML DB application running on the APP_DB database.

Problem: For downloading you cannot issue a SELECT of a LOB column over a database link. You get the "ORA-22992: cannot use LOB locators selected from remote tables" error message.

The Trick: You can get LOBs over a database link using an INSERT statement.

Upload Trick: The HTML DB form inserts into a global temporary table (UPLOAD_REPORTS) in the APP_DB with an INSTEAD-OF trigger on it which moves a copy of the LOBs to the REPORT_DB database via a INSERT INTO/SELECT FROM statement:

INSERT INTO reports@BLOBS_DB
(pk, blob_col, clob_col)
VALUES
(:new.pk, :new.blob_col, :new.clob_col);


Download Work Around: When the user selects a LOB to download, I move a copy of the row in the BLOBS_DB database to the APP_DB database with an INSERT INTO/SELECT FROM statement:

INSERT INTO download_reports
SELECT * FROM reports@BLOBS_DB;

Since the UPLOAD_REPORTS and DOWNLOAD_REPORTS tables are a global temporary tables in the APP_DB database when the HTML DB access to the APP_DB database is finished, the global temporary tables are emptied.