BLOB over DB Link

Using a DB Link to query BLOB's from a remote database could be a problem. Recently, I had to show images comming from a remote server in an APEX application. Selecting from a remote table would result in an error. Creating a copy of the remote table by issuing the following statement worked without any problems:

INSERT INTO dbt_images
   SELECT *
     FROM dbt_images@remote_db
    WHERE ID = p_id;

After searching for a solution I found an interesting way to get it working:

Jiri's Microblog

Basically, all you need to do is to:

1. create a local copy of the table you get the data from as

CREATE TABLE dbt_images AS
   SELECT *
     FROM dbt_images@remote_db
     WHERE 1 = 2;

2. create two types for storing the information

CREATE OR REPLACE TYPE object_row_type AS OBJECT (
   ID        NUMBER,
   NAME      VARCHAR2 (256),
   image     BLOB,
   creator   NUMBER,
   created   DATE
);
/

CREATE OR REPLACE TYPE object_table_type AS TABLE OF object_row_type;
/

3. create a pipelined function to get the required row

CREATE OR REPLACE FUNCTION get_remote_blob (p_id IN NUMBER)
   RETURN object_table_type PIPELINED
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO dbt_images
      SELECT *
        FROM dbt_images@remote_db
       WHERE ID = p_id;

   COMMIT;

   FOR cur IN (SELECT ID, NAME, image, creator, created
                 FROM dbt_images)
   LOOP
      PIPE ROW (object_row_type (cur.ID,
                                 cur.NAME,
                                 cur.image,
                                 cur.creator,
                                 cur.created
                                ));
   END LOOP;

   DELETE FROM dbt_images
         WHERE ID = p_id;

   COMMIT;
   RETURN;
END get_remote_blob;
/

Now, selecting from the function using the following SQL:

SELECT ID, image, NAME, DBMS_LOB.getlength (image)
  FROM TABLE (getblob (p_id));

will get that BLOB for you.