Query CLOB or BLOB from oracle table across DB-LINK with a simple view – Solution 2


I will continue my two-part post with second a bit twisted solution. I did not try to optimize this for speed, it’s a simple proof of concept. This solution requires NO staging tables, but you need tiny bit more grants on remote server (you need to be able to create function, type and a view).

Please read Solution 1 first before you continue with Solution 2 and why you can get error ORA-22992: cannot use LOB locators selected from remote tables

.

SOLUTION 2

This is a bit twisted solution, but works. Basically you first need to convert LOB (in these example I refer to CLOB, BLOB is similar but you will probably not use varchar2) to multiple varchar2(4000) on remote server – for example CLOB with size 8100 will be converted to 3 varchar2(4000), then read this data across db-links, then combine it back to CLOB on a local server. This can be all done on a fly without any staging tables.

.

1. REMOTE SERVER

let’s first define one table on remote server and load it with some data.

create table RemotetTableWithClob (myid number, myclob clob);

insert into RemotetTableWithClob (myid, myclob) values (1, 'This is small test');
insert into RemotetTableWithClob (myid, myclob) values (2, 'Another Test');
insert into RemotetTableWithClob (myid, myclob) values (3, 'One more test');
commit;

then we need to create a function for converting CLOB to multiple varchar2 and hide it behind view. Please note I tried to use collection here, but you cannot query collection across db-link, you will be facing error ORA-22804: remote operations not permitted on object tables or user-defined type columns thus I convert the clob to basically multi-row view (number of records is variable and depends on size of LOB). This is very simple

CREATE TYPE object_row_type AS OBJECT (
 MYORDER            NUMBER,
 MYID               NUMBER,
 MYCLOB_AS_VARCHAR  VARCHAR2(4000)    );

CREATE TYPE object_table_type AS TABLE OF object_row_type;

-- convert CLOB to multi-row varchar2
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 := 4000;

BEGIN

 FOR cur IN (SELECT myid, myclob from RemoteTableWithClob)
 LOOP
   v_clob_length := dbms_lob.getlength (cur.MyClob);
   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
     PIPE ROW(object_row_type(i + 1, cur.myid, dbms_lob.substr( cur.MyClob, v_varchar_size, v_varchar_size * i + 1 )));
   END LOOP;

 END LOOP;
 COMMIT;
 RETURN;
END;

now the view on remote server

-- create view to show the clob converted to varchar
-- please notice there is important column MYORDER (order of individual varchar chunks)
CREATE VIEW myRemoteData as
SELECT * FROM TABLE(CONVERT_CLOB_TO_VARCHAR) a;

gg.

2. LOCAL SERVER

on a local server, I read the records and convert them to collection (this can be done in a query using CAST-MULTISET), then I use a function to convert this collection back to CLOB.

.

CREATE PUBLIC DATABASE LINK RemoteServer  USING 'remoteserver';
CREATE OR REPLACE TYPE myTableType  as table of varchar2(4000);

CREATE OR REPLACE FUNCTION JIRI.F_VARCHAR_TO_CLOB (input_table_of_varchar myTableType) RETURN CLOB IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_clob  clob;
BEGIN
FOR i IN 1..input_table_of_varchar.COUNT
LOOP
 -- the concatenation below could be probably done using DBMS_LOB.WRITEAPPEND :-)
 v_clob := v_clob || input_table_of_varchar(i);
END LOOP;
RETURN v_clob;
END;

and here is the final view on local server

-- the view first converts separate varchar2 records to a collection
-- then function is used to convert the collection to CLOB
CREATE OR REPLACE VIEW myRemoteData as
SELECT a.myid,
       F_VARCHAR_TO_CLOB( CAST(MULTISET(SELECT b.MYCLOB_AS_VARCHAR
                                          FROM myRemoteData@remoteserver b
                                         WHERE a.MYID = b.MYID
                                      ORDER BY MYORDER   ) as myTableType)
                        ) myClob
FROM REMOTETABLEWITHCLOB@remoteserver a;

As you can see the process is very simple. I tested it with CLOB with more than 100,000 characters on my tiny VMWARE instance and got pretty decent timing. Please take the code above as a proof of concept, not as a tuned production ready solution.

Advertisements
Tagged , , , , , , ,

13 thoughts on “Query CLOB or BLOB from oracle table across DB-LINK with a simple view – Solution 2

  1. -Ash- says:

    I have followed your solution but i am getting the error:

    ORA-06502: PL/SQL: numeric or value error ORA-06512: at “SCHEMA_NAME.PLAN_CONVERT_CLOB_TO_VARCHAR”, line 15 ORA-02063: preceding 2 lines from DB_LINK

    (have changed my schema name and dblink for words)

  2. jiri says:

    it’s a bit harder to guess what is not working, what is somewhat good on this solution is that you can test it in between.

    1. Did you try to replicate my sample above or used it on your data?
    2. Can you run this query on remote server with no issues? select * from myRemoteData
    3 Can you run this query on local server with no issues? myRemoteData@remoteserver

  3. -Ash- says:

    Hi,

    thanks for your response,

    I have used it on my own data, which can be found here: http://forums.oracle.com/forums/thread.jspa?threadID=1108492&tstart=15

    when i run the query on the remote sever it displays this error: “PL/SQL: numeric or value error%s”

    when i run the query on the local server it displays the same error.

  4. jiri says:

    it’s kinda hard for me to recreate it … my guess is that this returns error as well, correct?

    SELECT * FROM TABLE(PLAN_CONVERT_CLOB_TO_VARCHAR)

    can you try to change the first function and pull one or two records which have clean (non-null) dates and clobs (basically change the query below)

    SELECT MON_SQL_ID, MON_PROG_DATE, MON_SQL_PLAN from monitored_sql_plan

  5. -Ash- says:

    Hi, sorry for the late reply.

    this code still errors:
    SELECT * FROM TABLE(PLAN_CONVERT_CLOB_TO_VARCHAR)

    i changed the query to this:

    SELECT MON_SQL_ID, MON_PROG_DATE, MON_SQL_PLAN from monitored_sql_plan where MON_SQL_ID = 1

    as this row has all clean (non-null) data.

    in the local server, the SELECT * FROM PLANMYREMOTEDATA statement now returns data for all the rows in the MON_SQL_ID and MON_PROG_DATE columns, and for the row ‘where MON_SQL_ID = 1’ , it returns MON_SQL_PLAN column, showing all other columns as null.

  6. jiri says:

    I am out of town for few days. I won’t be able to recreate your setup till next week.

    1. Can you send me DDL of your source table and if possible few dummy records

    2. Did you try 1st solution with temporary table? I was using it heavily with apex for few years with great success

  7. -Ash- says:

    Hi, no worries, i think i have worked out how to get round NULL values in the CLOB column.

    In the function on the remote server, i added: ‘where is NOT NULL;’ after the select statement, Firstly to just test the data to see if it would bring up just the values that weren’t NULL, but it actually returned all the rows (including NULL values) when i queried it in APEX. Happy Days!

    FOR cur IN (SELECT MON_SQL_ID, MON_PROG_DATE, MON_SQL_PLAN from monitored_sql_plan where MON_SQL_plan is not null)

    I tried using the temporary table solution but i had no luck whatsoever with that.

    Thanks for all your help!

    Ashleigh

  8. Andrey says:

    v_varchar_size number := 2000;

  9. Vishal says:

    I am trying to use the same concept but not working for me. Could you please help me out in finding the issue or what i am doing wrong.

    CREATE OR REPLACE TYPE myTableType as table of varchar2(4000);

    CREATE OR REPLACE FUNCTION F_VARCHAR_TO_BLOB (input_table_of_varchar myTableType) RETURN BLOB IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_blob BLOB;
    BEGIN
    FOR i IN 1..input_table_of_varchar.COUNT
    LOOP
    v_blob := v_blob || input_table_of_varchar(i);
    END LOOP;
    RETURN v_blob;
    END;

    CREATE OR REPLACE VIEW myRemoteData as
    SELECT a.ENTRYID,
    F_VARCHAR_TO_BLOB( CAST(MULTISET(SELECT b.MYBLOB_AS_VARCHAR
    FROM myRemoteData@T3T2 b
    WHERE a.ENTRYID = b.MYID
    ORDER BY MYORDER ) as myTableType)
    ) C1201032010
    FROM B282C1201032010@T3T2 a;

    insert into archive_b288c1201032010 select * from myremotedata

  10. sun says:

    I just couldn’t go away your site before suggesting that I really enjoyed the usual info a person provide in your visitors? Is going to be back frequently to inspect new posts

  11. Celeste says:

    I think this is among the most significant information for me. And i am glad reading your article. But wanna remark on few general things, The website style is wonderful, the articles is really excellent : D. Good job, cheers

  12. Your post dates waaay back to 2010, yet it still holds relevance today. Search found your article for me and helped me figure out something I once thought not possible. You present a clever (working) workaround for Oracle DBLinks and large objects or custom user types. There is a similar solution out there within the Oracle tutorials, yet there are reports that it is lacking and doesn’t produce anything that works. Way to go!

  13. Flado says:

    You may want to go the “lateral LOB” route in your remote view definition – this way any predicates on the non-CLOB columns will be applied before the clob-to-varchar function gets called with a parameter (either the ROWID from the table or the CLOB itself). Details here: https://jonathanlewis.wordpress.com/2008/11/19/lateral-lobs/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: