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


This is not so unusual scenario: You have a table with one or more LOB fields on remote oracle server and you want to query that table across db-link. If you try simple SELECT statement you will be facing ORA-22992: cannot use LOB locators selected from remote tables

oooops, what next. There are several workarounds, let me try to tackle one easy solution.

This testing was done using oracle 10g running on remote windows machine and 11.2 running on local redhat5 server, but it should in general work for any 10g/11g servers.
.
for the second solution follow this link
.

.

SOLUTION 1

The first solution is actually very simple. Create a table (I prefer temporary table) on local oracle server, insert data from remote server to local server using INSERT INTO and then query local server. This can be all fully automated and hidden from users …

1. REMOTE SERVER

let’s first define one table on remote server and load it with some data, that’s all we need to do on remote server.

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;

.

2. LOCAL SERVER

first, let me create db-link (I have same userID on both servers, this will be passwordless db-link) and then try to run simple query

CREATE PUBLIC DATABASE LINK RemoteServer  USING 'remoteserver';

select myid, substr(myclob, 1, 5) mytext from remotetablewithclob@RemoteServer;
ORA-22992: cannot use LOB locators selected from remote tables

as you can see I cannot query the CLOB field.

.

3. SIMPLE WORKAROUND

The simply workaround is to bring the data from remote server to local server and then query local table. I use temporary tables simply because I don’t want to worry about deleting the data and concurrent users.

create global temporary table LocalTempTableWithClob
 (myid number,  myclob clob) ON COMMIT PRESERVE ROWS;

insert into  LocalTempTableWithClob Select * from RemoteTableWithClob@RemoteServer;
select  myid, substr(myclob, 1, 5) mytext from LocalTempTableWithClob;

MYID   MYTEXT
--------------
 1  This
 2  Anoth
 3  One m

.

4. NOW THE REAL TRICK

As you can see (3) works pretty good and it can be the end of the post, but … what if you have multiple users who want to query the data. Are you going to ask them to run the insert every time the want to query the table?

Here is my solution to that problem. I basically use a function to insert data from remote to local table and then display the result of local table instead. This is all done in a single pass in a single function and can be hidden from users via view.

I use pipelined function because I really love them and hope more users are going to start using them.

CREATE TYPE object_row_type AS OBJECT (
 MYID               NUMBER,
 MYCLOB             CLOB             );

CREATE TYPE object_table_type AS TABLE OF object_row_type;

CREATE OR REPLACE FUNCTION GETCLOB RETURN object_table_type PIPELINED IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 insert into LocalTempTableWithClob Select * from RemoteTableWithClob@RemoteServer;
 commit;
 FOR cur IN (SELECT myid, myclob from LocalTempTableWithClob)
 LOOP
   PIPE ROW(object_row_type(cur.myid, cur.myclob));
 END LOOP;
 DELETE FROM LocalTempTableWithClob;
 COMMIT;
 RETURN;
END;

create view myRemoteData as
SELECT * FROM TABLE(GetClob) a;

select myid, substr(myclob, 1, 5) mytext from myRemoteData;

MYID  MYTEXT
--------------
 1  This
 2  Anoth
 3  One m

As you can see the solution is very simple, the function is hidden behind the view and users do not even know about the data insert.

Please note this is a bit oversimplified scenario, you can easily extend the function to add custom WHERE clause (which I would highly recommend, you don’t want to move whole table over dblink every single time), you can even make the function very universal for any querying of LOB across db-link.

About these ads
Tagged , , , , , , ,

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

  1. […] Informatica ← Query CLOB or BLOB from oracle table across DB-LINK with a simple view – Solution&nb… […]

  2. Vic Douse says:

    Nice…..solution.
    I just build a version of it here.
    Did you every figure out how to pass myid = 1 to the GETCLOB function from the view
    ie. select myid, substr(myclob, 1, 5) mytext from myRemoteData where myid = 1
    Thanks
    Vic

  3. jiri says:

    it should be really easy, simply say use the pipeline function with a parameter, I did not test the code below, but in a nutshell it should work like that.

    CREATE OR REPLACE FUNCTION GETCLOB RETURN object_table_type (v_my_id number) PIPELINED IS
    ...
    insert into LocalTempTableWithClob Select * from RemoteTableWithClob@RemoteServer where my_id = v_my_id;
    ...
    END;

    then call the SQL
    SELECT * FROM TABLE(GetClob(1) ) a

    1. you should probably add more error checking code to the function
    2. you can make it truly universal by passing whole SQL or whole WHERE clause as a varchar to the GETCLOB function and running insert into statement as dynamic SQL statement using execute immediate

  4. Vic Douse says:

    Jiri, Thanks for the prompt reply
    Yes, passing the parameter to the function is an important step.
    The key is getting the sql statement that fired the function.
    sys_context(‘USERENV’,’CURRENT_SQL’) only work for FGA and then only after select, not before.
    .
    Do you know a method of getting the sql statement that was the root of this chain of events.
    Once I have the statement, I can parse out the where clause and add it to the insert statement that populates the temp table.
    .
    Thanks for your time and consideration
    Vic

  5. jiri says:

    I am out of my computer and just thinking loud, you have few options here …

    Are you looking to get this on a fly? SELECT * FROM TABLE(GetClob(1) ) a Can I ask more details why please.

  6. Lorne Allaire says:

    I have clob table A on database DB1
    I have clob table B on database DB2
    I have a view on database DB1 defined as …

    create view AB as
    select * from A
    union all
    select * from TABLE(GETBLOB)

    I would like the GETBLOB function to “bring over” only those records from DB2 that satisfy the WHERE clause … not the entire table.

    ie: select * from AB where myid = 1

    This works but the GETBLOB function inserts the entire table from DB2 into DB1 temp table. This is not practical with very large tables.
    It would be ideal if the function could receive the WHERE clause (or entire SQL for parsing) and insert only those records.
    Hope this helps clarify things.
    Thanks

  7. jiri says:

    I am out oftown today, but I think there is an easy solution. Let me respond tonight when get back.

  8. jiri says:

    I did not test this, but theoretically it could work. Basically I first pull primary key from remote table only (all records but only primary key), then filter records I need and then get clob for those records (record by record)

    let me know if this works, I would be really interested to see if it does


    select table_primary_key
    field_clob
    from table1_local A
    union all
    select table_primary_key,
    (select * from TABLE(GETBLOB(b.table_primary_key))
    from table2_remote@remoteserver b

  9. Lorne Allaire says:

    The following sql fails to run due to the “select *” in the sub-query … ORA-00913 too many values

    select table_primary_key,
    (select * from TABLE(GETBLOB(b.table_primary_key)))
    from table2_remote@remoteserver b

    Changed it to the following but now getting ORA-01427 single-row subquery returns more than one row

    select table_primary_key,
    (select table_blob_col from TABLE(GETBLOB(b.table_primary_key)))
    from table2_remote@remoteserver b

    Still cannot figure out how to pass the WHERE clause to the getblob funtion from within the view.

  10. jiri says:

    really? does this return more than one record?

    (select table_blob_col from TABLE(GETBLOB(b.table_primary_key)))

    I’m unfortunately out of town whole week, let’ me try to recreate it on my VMWARE when I get back

  11. Lorne Allaire says:

    No, it only returns 1 row. Both tables on both bases have the same structure and the same pk. Again, thanks for all your help with this. It would be nice to get this sql working.

  12. manabu says:

    What happens when the first COMMIT finishes,
    and before the process goes to FOR-loop, another transaction calls this Function?

    Will the records of LocalTempTableWithClob table be doubled?
    Will the first transaction get doubled records at (SELECT myid, myclob from LocalTempTableWithClob)?

    Sorry for poor English…
    Regards,

  13. jiri says:

    Try it :-)

    I am 3000 miles from my computer on loooong vacation and typing on iPhone….

    It uses global temporary table and thus only the session which inserts the data can see the data. In another words if you run the code in 10 sessions at the same time, you will have no issues because each session will see only it’s own data.

  14. manabu says:

    Ops! the word, “GLOBAL TEMPORARY” slipped my mind. so sorry…
    This entry helped me a lot and made me be much more interested in Oracle.

    Thank you very much.

  15. jiri says:

    No problem, glad I could help

  16. rg15664 says:

    Hi Guys, Please also explain the scenario where we have to “insert row that are manipulated from same/another table.”

  17. jiri says:

    are you trying to read and later insert data into CLOB over db-link?

  18. Sai Pokala says:

    Jiri, Your example is excellent. Saved me lots of time. Thank you describing it so nicely…I literally cut and past the code and did minor changes it worked like gem.

  19. shashanka says:

    wonderful.

  20. What’s up to every one, it’s genuinely a pleasant for me to
    visit this site, it contains precious Information.

  21. I think this is one of the most significant info for me.
    And i’m glad reading your article. But wanna remark on some general things, The web site style is ideal, the articles is really excellent : D. Good job, cheers

  22. Raymond says:

    Does anyone have any performance tips on moving CLOBs accross DB Links. We move around 200000 records each night and this takes a few hours, not what you would expect from an Oracle database.

  23. jiri says:

    1. how do you move the CLOBs (what method)?
    2. I’d say fastest is data pump export + copy files + data pump import (I would enable compression as well)
    3. if you must stay on oracle level and must use db-link, did you look into compression? CLOBs have usually very good compression and UTL_COMPRESS should work with few methods I described

  24. Raymond says:

    We move hundreds of tables across every night and use the simple “insert into local_table (columns) select columns from external_table@db_link”.

    For the CLOB columns we would like to use the same method. I will give the UTL_COMPRESS a try and let you know what the results are.

    Only thing is that the majority of the data has a length that is less than 1000, so the data isn’t that big.

  25. jiri says:

    just to add… inserting to CLOB by itself can be slow (really depends on your settings), DBLINK adds a lot of time on top of it

    I am not sure if you can chance your application, but if size of your CLOBs is small, then using compression and storing them as RAW or VARCHAR2 (perhaps as BASE64) might be better option – I had similar task and picked compressed RAW for data storage (I got 10:1 compression in my case)

    on a side, Oracle 12c introduced larger varchar2, it might be worth trying as well

  26. gnedved says:

    Thanks for this post! It really helped me find a great solution for the issue I was having. I ended up creating a package that has mutliple get_clob functions for different tables that all have CLOB datatypes and are needed to be selected remotely from multiple users. Thanks again!

  27. Panneer says:

    Hi All,
    We just tried below simple option and it worked fine. Anyone seeing any issue with our approach ??

    CREATE GLOBAL TEMPORARY TABLE TMP_OBJCT_T
    (
    OBJCT_ID NUMBER,
    TXT CLOB
    ) ON COMMIT PRESERVE ROWS;

    INSERT INTO TMP_OBJCT_T(OBJCT_ID, TXT)
    SELECT MYID, MYCLOB FROM REMOTETTABLEWITHCLOB@REP_R3_LINK WHERE MYID = 3;

    SELECT TXT FROM TMP_OBJCT_T;

    DELETE FROM TMP_OBJCT_T;
    COMMIT;

  28. Suresh says:

    /*
    –Our existing query
    select refno,
    — other fields
    — other fields
    — other fields
    –clob_data, — This is the clob field if we uncomment we get, ORA-22992: cannot use LOB locators selected from remote tables

    getclob(refno) — New function to return the remote field’s clob data .

    from remotetable@remotedatabase
    where refno between 2146139 and 2146158
    */

    — Ensure LocalTempTableWithClobo table does not exists before creating the temp table.
    drop table LocalTempTableWithClob;
    /

    — Create the temp table LocalTempTableWithClobo to hold the remote clob data.
    create table LocalTempTableWithClob(myid number, myclob clob);
    /

    — Create the procedure to insert the remote clob data to temp table, for to call in the GetClob function, as the DML are not allowed in functions.

    create or replace procedure insert_myclob (id in NUMBER)
    is
    begin
    — Ensure no data is availble in the temptable
    delete from LocalTempTableWithClob;

    — Insert the remote clob data into the temp table
    insert into LocalTempTableWithClob
    select refno, –Reference number
    clob_data — Clob Data
    from remotetable@remotedatabase
    where refno = id;

    — Commit insert transaction
    commit;

    end;
    /

    — Create the function to get the clob data from the temp table.
    create or replace function getclob (id in number) return clob
    is
    result clob;
    PRAGMA AUTONOMOUS_TRANSACTION;
    begin
    — Call the insert_clob procedure to insert the remote clob data.
    insert_myclob(id);

    — Capture the remote clob data for the respective id into the result out variable.
    select myclob into result
    from localtemptablewithclob
    where myid = id;

    — Output the result clob
    return result;
    end;

  29. Suresh says:

    /*We had a requirement to get data from remote db, we could able to use our existing query for both sql and oracle, by using the DATABASE LINK concept, however we faced a problem in oracle, is that, one of the field is with clob data which our existing query failed with the “ORA-22992: cannot use LOB locators selected” in oracle hence after much analysis and with the help of your site, I achieved getting the clob data using our existing query with a minor modification with the above procedure and function. Please feel free to update/suggest any other feasible solution to get clob data from remote db. Thanks in advance.*/

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: