Dynamic variable for table name inside a loop statement

Question

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 :)

William Robertson · Accepted Answer · 2018-06-28 13:25:19Z

1

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.

answered Jun 28, 2018 at 13:25

William Robertson

16.1k4 gold badges41 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mino1234 Over a year ago

Thanks for reply, I will try it. I am gonna insert few milions rows in every single outer loop, so I think it is better to commit regularly than having big pending transaction. Btw, is it "safe" to use open cursor if result of that cursor leads to few milions of rows?

William Robertson Over a year ago

A big pending transaction isn't an issue in itself (and 1000 rows is tiny). I would think about restartability in the event of failure. Perhaps you can somehow track which table names you have processed in the outer loop, and commit once per table? Then maybe you could restart the outer loop where you left off.

Collectives™ on Stack Overflow

Dynamic variable for table name inside a loop statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related