oracle 使用 DBlink 插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21898400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle insert using DBlink
提问by Ashok Kumar Dabbadi
I have two different databases, say DB1 and DB2. In both DBs I have a common table called test_emp, but there is a difference in the columns of the tables:
我有两个不同的数据库，比如说 DB1 和 DB2。在两个数据库中，我都有一个名为 test_emp 的公用表，但表的列有所不同：
DB1
数据库1
-----
desc table1
--------------
empno number
ename varchar2
sal number
hiredate date
deptno number
DB2
数据库2
-----
desc table2
--------------
empno number
ename varchar2
sal number
insert into table1 select * from table2@DBLink_Name
But here I have a problem with number of columns, target table has more columns than the source.
Here I can't specify column_namesbecause I am passing table as dynamic.
但是在这里我遇到了列数问题，目标表的列数比源表多。在这里我无法指定，column_names因为我将表作为动态传递。
Can you somebody please help?
你有人可以帮忙吗？
回答by Rajesh Chamarthi
You could look at the oracle data dictionary/metadata tables on both databases and generate the necessary sql for the insert. if i_table_nameis your input.
您可以查看两个数据库上的 oracle 数据字典/元数据表，并为插入生成必要的 sql。如果i_table_name是您的输入。
select list_agg(column_name,',') columns -- comma separated list of columns
from (
select column_name
from all_tab_cols
where owner = 'XYZ'
and table_name = i_table_name
intersect
select column_name
from all_tab_cols@remote_database
where owner = 'XYZ' -- could be different (?)
and table_name = i_table_name
)
You could then use this string (comma separated list of colums) to do something along the lines of..
然后，您可以使用此字符串（逗号分隔的列列表）来执行以下操作。
v_sql := 'insert into ' || i_table_name || ' ' || '(' ||
v_column_list || ')' ||
' select ' || '( ' || v_column_list ||
' from ' || i_table_name || '@' || remote_db_name;
dbms_output.put_line(v_sql);
execute immediate v_sql;
I haven't tested the code. Make sure you print the insert and confirm it matches your expectations.
我还没有测试代码。确保打印插页并确认它符合您的期望。
回答by San
use
用
INSERT INTO table1
SELECT a.*,
NULL,
NULL
FROM table2@dblink_
