Remotely Querying CLOB or BLOB Data Across a Database Link Using Oracle PL/SQL
The following solution was developed and tested on an Oracle 11gR2 instance. APEX was used just for visualization of the data values as stored or delivered through different intermediate objects. This solution may still prove helpful for application development on APEX equipped DB instances with remotely shared connections such as Oracle DBLinks.
INTRO: There were a few holes in the explanation of the OP, but I have assumed that the usage of terminology such as "Remote Table" and "Database Link" indicates that the OP is trying to bypass a known limitation of the Oracle RDBMS.
This limitation is the inability to query LOB typed data across remote dblink connections. A common error encountered when attempting this is: ORA-22992: cannot use LOB locators selected from remote tables.
Research and References
A blog post on: Querying CLOB or BLOB Data Across DBLinks This was my primary reference used to develop my walk through... which really is just an recap of my efforts to verify that the code at this link actually works (it does!).
A reference from PSOUG.org: Reference on the DBMS_LOB Oracle package. Used to verify my understanding of CLOB and LOB data type handling.
Special mention to: watchout4snakes. This is an online randomized dictionary tool. I used it here to make large chunks of text that didn't make one's eyes glaze over such as: lorum ipsum locati quorum... :)
If you check out the first reference in this list, you may notice my code here is very similar. The value I've added in re-presenting this case from back in 2010 is creating a larger but more interesting set of test data. I've also added a few tweaks to the original solution to help validate the approach.
One More Note: Before you dive into the example, be sure to map out (at least in your head) the schema/owner of the objects created in each the "remote" and "local" instance of your demo... it will ease the level of grief encountered with this otherwise really straight forward solution.
Setting Up The Demo on the REMOTE SERVER
Following is the source code to set up the REMOTE DATABASE INSTANCE.
Database Object Types For Remote Instance
CREATE TYPE object_row_type AS OBJECT (
MY_ORDER NUMBER,
MY_ID NUMBER,
MY_CLOB_AS_VARCHAR VARCHAR2(4000));
CREATE TYPE object_table_type AS TABLE OF object_row_type;
Table DDL and Test Data Scripts
CREATE TABLE REMOTE_CLOB_TABLE (
ID NUMBER NOT NULL,
MY_CLOB CLOB
);
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(1001, 'When can the expenditure enter behind a shock recovery? The strategy fishes underneath the sugar. An after wrap masters a slim moron. The twenty dish hunts an aunt opposite the credible zone. The phoenix copes. The diagonal flours a bag against the positive fan.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(2001, 'A traveled concept hides a removed skin. A liquid steers whatever understandable heart. A curve strips away an assembly. A wartime freezes the outcome.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(3001, 'A lonely genre bolts beside the obliging prisoner. The freedom stamps! Its game fluid dictates. How will her power imagine the quantum?');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(4001, 'The young office caps the travelled temper. A forum husbands the family. The detail peers. Her jammed agenda experiments against the regarding obstruction.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(5001, 'The incredible drivel suspects. A vehicle reads. A cardboard jacket shares the insult above the baking constitutional. Outside this effort composes the invited jest.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(6001, 'The vegetarian strength marches underneath an opposing intellectual. The ringed lifestyle bends the archaic thirst. A saga escapes with the ego. The acorn escapes against the old lark.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(7001, 'Whatever bear furthers a mania. The norm contours a ruin. How can the reasoned composite cough? With a unimportant timetable reasons the sorry frog. Can the key jacket toss an author?');
COMMIT;
Function Object Script
CREATE or REPLACE FUNCTION CONVERT_CLOB_TO_VARCHAR RETURN object_table_type PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_clob_length number;
v_loops number;
v_varchar_size number := 100;
-- purposely set to a small value to see the looping step work
-- normally set to max varchar2 size (4000 BYTE).
BEGIN
FOR cur IN (SELECT id, my_clob from remote_clob_table)
LOOP
v_clob_length := dbms_lob.getlength (cur.my_clob);
v_loops := trunc(v_clob_length/v_varchar_size) +
sign (mod(v_clob_length, v_varchar_size )) - 1;
FOR i IN 0..v_loops
LOOP
-- This chunks the CLOB/LOB file from beginning to end in chunks
-- of pre-designated size.
PIPE ROW(object_row_type(i+1, cur.id, dbms_lob.substr(cur.my_clob,
v_varchar_size, v_varchar_size * i + 1 )));
END LOOP;
END LOOP;
COMMIT;
RETURN;
END;
The VIEW Object for Presenting Remotely Accessible Converted CLOB Data:
CREATE or REPLACE VIEW myremotedata AS
SELECT * FROM TABLE(CONVERT_CLOB_TO_VARCHAR) a;
Setting Up The Demo on the LOCAL SERVER
Following is the source code to set up the LOCAL DATABASE INSTANCE.
Make the DB Link to the Remote Server:
CREATE PUBLIC DATABASE LINK MY_REMOTE_CONNECTION
CONNECT TO REMOTE_USER
IDENTIFIED BY <PWD>
USING <TNS or DIRECT CONNECTION STRING>
Declaring Custom Object Type:
CREATE OR REPLACE TYPE MY_TABLE_TYPE AS TABLE OF VARCHAR2(4000);
PL/SQL Function for Re-Assembling VARCHAR Pieces to CLOB Format
CREATE OR REPLACE FUNCTION F_VARCHAR_TO_CLOB (input_table_of_varchar my_table_type)
RETURN CLOB IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_clob clob;
BEGIN
FOR i in 1..input_table_of_varchar.COUNT
LOOP
v_clob := v_clob || input_table_of_varchar(i);
END LOOP;
RETURN v_clob;
END;
View Object for Collecting Results From Across the Database Link
CREATE OR REPLACE VIEW MY_REMOTE_DATA AS
SELECT a.id,
f_varchar_to_clob(
CAST( MULTISET( SELECT b.MY_CLOB_AS_VARCHAR
FROM remote_user.myremotedata@my_remote_connection b
WHERE a.id = b.my_id
ORDER BY b.my_id ASC, b.my_order ASC )
as my_table_type)
) MY_CLOB
FROM REMOTE_CLOB_TABLE@my_remote_connection a;
Output Results
Data queried remotely from the local instance is presented in two reports. The first shows how a CLOB data type can be digested into smaller pieces. The max size of each piece is actually variable. I chose a smaller size than the 4,000 byte varchar2 maximum so that you can see a slightly smaller CLOB data value divided into many pieces.
Output From a View on the Remote Instance: MYREMOTEDATA
This was created using the CONVERT_CLOB_TO_VARCHAR PL/SQL Function.
Output From a View on the Local Instance: (Reassembled data in CLOB Format)
select id, my_clob from my_remote_data
where id between 1000 and 5000
ID MY_CLOB
1001
When can the expenditure enter behind a shock recovery? The strategy fishes unde
rneath the sugar. An after wrap masters a slim moron. The twenty dish hunts an a
unt opposite the credible zone. The phoenix copes. The diagonal flours a bag aga
inst the positive fan.
2001
A traveled concept hides a removed skin. A liquid steers whatever understandable
heart. A curve strips away an assembly. A wartime freezes the outcome.
3001
A lonely genre bolts beside the obliging prisoner. The freedom stamps! Its game
fluid dictates. How will her power imagine the quantum?
4001
The young office caps the travelled temper. A forum husbands the family. The det
ail peers. Her jammed agenda experiments against the regarding obstruction.
4 rows selected.
After querying the VARCHAR data pieces, this is the view that reconnects them all back to CLOB format.
Conclusions:
This is a proof-of-concept approach which illustrates how to bypass the Oracle RDBMS limitation between Custom User Types, Collections, CLOBs, and BLOBs when attempting a remote query over a database link.
Additional analysis may be necessary to evaluate any possible performance bottlenecks or issues related to the size, quantity and transfer rates of the data queried between a more specific local and remote instance (i.e., your own intended environment).
