CODEWITHSUNDEEP
oracle-databasedblink
Bacara
Bacara

Reputation: 7223

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

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,

Upvotes: 0

Views: 2632

Answers (1)

Husqvik
Husqvik

Reputation: 5809

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.

Upvotes: 3

Related Questions