使用Oracle中的DATABASE LINK从具有相同模式的两个表中选择数据

Question

我有两个具有相同模式的数据库，我需要使用Oracle中的DATABASE LINK从具有相同模式（相同名称，相同列）的两个表中选择数据？

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

我想得到以下结果：

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

问候，

Answer 1

使用UNION ALL运算符

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

编辑：

新增功能草案：

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')));

您需要确保类型和表之间的数据类型匹配。