Select data from two tables having same schema using DATABASE LINK in Oracle?

Question

I've two databases with the same schema, I need to select data from two tables having same schema (same name, same column) using DATABASE LINK in Oracle ?

SQL> select * from TEST1;

        ID NAME
---------- ----------
         2 Two
         4 Foor

SQL> select * from TEST1@link22;

        ID NAME
---------- ----------
         1 One
         3 Three

SQL> select * from TEST1, TEST1@link22;
select * from TEST1, TEST1@link22
       *
ERROR at line 1:
ORA-00918: column ambiguously defined

I want to get the following result:

        ID NAME
---------- ----------
         2 Two
         4 Foor
         1 One
         3 Three

Regards,

Husqvik · Accepted Answer · 2017-02-08 12:41:45Z

3

Use UNION ALL operator

select * from TEST1
UNION ALL
select * from TEST1@link22;

EDIT:

Added function draft:

CREATE OR REPLACE TYPE site IS OBJECT (id NUMBER, name VARCHAR2(255));
/

CREATE OR REPLACE TYPE site_collection IS TABLE OF site;
/

CREATE OR REPLACE FUNCTION merge_sites (sites SYS.ODCIVARCHAR2LIST) RETURN site_collection PIPELINED
IS
    commandText VARCHAR2(4000);
    c SYS_REFCURSOR;
    sid test.id%type;
    sname test.name%type;
BEGIN
    FOR i IN 1..sites.COUNT LOOP
        commandText := 'SELECT id, name FROM ' || sites(i);
        OPEN c FOR commandText;

        LOOP
            FETCH c INTO sid, sname;
            EXIT WHEN c%NOTFOUND;           
            PIPE ROW (site(sid, sname));
        END LOOP;
    END LOOP;
END;
/

SELECT * FROM TABLE(merge_sites(sys.ODCIVARCHAR2LIST('test1', 'TEST1@link22')));

You need to secure the data types match between type and tables.

edited Feb 8, 2017 at 12:41

answered Nov 27, 2015 at 21:50

Husqvik

5,8191 gold badge23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bacara Over a year ago

Is there another solution than using UNION ALL. As we can't use N union for N sites.

Husqvik Over a year ago

What's wrong with UNION ALL? Anything else will be more complex. This operator serves exactly to the purpose, to concatenate two row sources into one.

Bacara Over a year ago

Let's say we have 100 sites, each one has the same schema (same situation I asked for), should I write 100 select queries, and use UNION ALL ?

Husqvik Over a year ago

If you want to have all rows from all the sites in one output, yes. If you have list of sites somewhere or the sites are added/removed over time you can use PL/SQL to build dynamic SQL and hide it behind PIPELINED function.

Justin Cave Over a year ago

@BoubakrNOUR - Why would you be trying to write a query to read data from 100 different databases? The pipelined table function mostly works but it's not going to be very efficient and if 1 of the 100 databases is down or unreachable, it's going to die. It sounds like you really want to implement real multi-master replication between instances rather than coding this manually.

|

Collectives™ on Stack Overflow

Select data from two tables having same schema using DATABASE LINK in Oracle?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related