oracle insert using DBlink

Question

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

-----
desc table1
--------------
empno               number
ename               varchar2
sal                 number
hiredate            date
deptno              number

DB2

-----
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_names because I am passing table as dynamic.

Can you somebody please help?

Solution 1

You could look at the oracle data dictionary/metadata tables on both databases and generate the necessary sql for the insert. if i_table_name is your input.

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.

Solution 2

use

INSERT INTO table1 
SELECT a.*, 
       NULL, 
       NULL 
FROM   table2@dblink_