CODEWITHSUNDEEP
plsqloracle11g
Mino1234
Mino1234

Reputation: 87

Dynamic variable for table name inside a loop statement

I am trying to have for-loop statement inside another for-loop statement where table_name used in the inner for-loop query is result of first for-loop:

declare
l_insert_count pls_integer := 0;
begin

for row_outer in (select distinct table_name, item_type from TRANSFORMATION_MAPPING) LOOP
  l_insert_count := 0;


  for row in (select id from ***row_outer.table_name*** where NVL(platnost_do,sysdate)>=sysdate) LOOP

    execute immediate 'insert into ITEM_MAPPING(GUID,ID) VALUES(''CENDB:''' || sys_guid() || ',' || row.id || ')';

    l_insert_count  := l_insert_count + 1;  

    IF (l_insert_count > 999) THEN
        l_insert_count := 0;
        commit;
    END IF;

  END LOOP;

end LOOP;

commit;

end;
/

Body of inner loop is much more complex. Provided code is only for purpose of showing what I want to do. row_outer.table_name is variable name of table that I want loop over to do transformations I need. In the inner loop I will need to insert few milions of rows into few different tables depending on the table from outer loop.

Thank you very much :)

Upvotes: 0

Views: 837

Answers (1)

William Robertson
William Robertson

Reputation: 15991

I make it something like this (untested):

declare
    l_insert_count pls_integer := 0;
    l_detail_cur sys_refcursor;
    l_detail_id integer;
begin
    for r in (
        select distinct table_name, item_type
        from   transformation_mapping
    )
    loop
        l_insert_count := 0;
        l_detail_id := null;

        open l_detail_cur for 'select id from '||r.table_name||' where nvl(platnost_do, sysdate) >= sysdate';

        loop
            fetch l_detail_cur into l_detail_id;
            exit when l_detail_cur%notfound;

            execute immediate 'insert into item_mapping(guid,id) values(''CENDB:''||sys_guid(), :id)' using l_detail_id;

            l_insert_count := l_insert_count + sql%rowcount;

            if l_insert_count > 999 then
                l_insert_count := 0;
                commit;
            end if;
        end loop;

        close l_detail_cur;

    end loop;

    commit;
end;

As a rule I would avoid committing in loops, especially for INSERTs (as they have the least overhead and are hardest to restart), but it's up to you.

Upvotes: 1

Related Questions