CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
Auguster
Auguster

Reputation: 375

Trying to insert data into multiple tables by using multiple tables on another linked database

Hello in my project I am creating new staging tables by coping data from another linked database. I have named my tables STG_ABC_EMPTABLE,STG_ABC_DEPTTABLE,.......... etc but the source DB I am pulling from has naming like employee_EMP,department_DEPT. I am trying to create PL/SQL block to insert table as it is and add couple columns when inserting. I am not able to come up with a statement that can loop through all source tables and insert data into target with couple extra columns. Also my tables are created from source tables with those 2 extra columns in each table. Also I can't figure out how to insert only like employee_EMP into STG_ABC_EMPTABLE and department_DEPT into STG_ABC_DEPTTABLE. Here is a something I came up with but got stuck trying to complete it.

 set SERVEROUTPUT ON;
DECLARE
cursor c1 IS 
select distinct A.table_name as table_name_S,B.table_name as table_name_T from all_tab_columns@db_link A inner join (select distinct table_name from all_tab_columns where table_name like 'STG_%') B 
ON A.table_name LIKE '%' || substr(B.table_name,9,3) || '' where A.owner in('source');
v_count number;
time_rec timestamp;
c_source_GC varchar2(28) := 'XYZ';
BEGIN
v_count:=0;
for items in c1
LOOP
Execute immediate 'insert into '|| items.table_name_T ||
'(select ' || items.table_name_S || '.*,'
|| c_source_GC || ' as SOURCE_SYSTEM,
current_timestamp as DATE_LOADED_TIMESTAMP
from gntc.' || items.table_name_S ||'@db_link)';
v_count:= sql%Rowcount;
dbms_output.put_line('Number of rows inserted '||v_count||' into '|| items.table_name_T);
END LOOP;

commit;
end;

Upvotes: 0

Views: 69

Answers (1)

BA.
BA.

Reputation: 934

Try this code. 

set SERVEROUTPUT ON;

declare
    cursor c1(p_source varchar2) is
        select 
                a.table_name as table_name_s,
                listagg(a.column_name, ', ') within group (order by a.column_id) columns
                (select b.table_name from all_tables b where b.table_name like 'STG_ABC_%' and substr(b.table_name,9,3) = substr(a.table_name, instr(a.table_name, '_') + 1, 30)) table_name_t
        from all_tab_columns@db_link a
        where a.owner = (p_source)
        group by a.table_name;

    v_count number;
    time_rec timestamp;

    c_source_gc varchar2(28) := 'XYZ';

begin
    v_count := 0;
    for items in c1('SOURCE') loop
        if items.table_name_t is null then 
            dbms_output.put_line('No target table found for source '||items.table_name_s);
        else
            execute immediate 'insert into '|| items.table_name_t ||' ('||items.columns||', source_system, date_loaded_timestamp)
                               select '||items.columns||', '''||c_source_gc||''', systimestamp 
                               from '||items.table_name_s||'@db_link'
            v_count:= sql%rowcount;
            dbms_output.put_line('Number of rows inserted '||v_count||' into '|| items.table_name_t);
        end if;
    end loop;

    commit;
end;

Upvotes: 1

Related Questions