PL/SQL (MOSC)

MOSC Banner

Question about execute immediate or simple update

edited Mar 4, 2013 4:57AM in PL/SQL (MOSC) 6 commentsAnswered ✓
Hi,
I've a question about two ways of making a script.

Every days, I load something like 1.5 to 2 millions rows  in à temporary table with sqlldr.

Then I treat this data with cursor and bulk collect to update into the master table.

I was wondering if it was better to use :
    cursor linkmembersite_cur is select .........;
    TYPE  linkmembersite_typ is TABLE  OF  linkmembersite_cur%ROWTYPE INDEX BY PLS_INTEGER;
    t_linkmembersite linkmembersite_typ;
    BEGIN
      open  linkmembersite_cur;
        loop
             FETCH  linkmembersite_cur BULK COLLECT INTO t_linkmembersite LIMIT 50000;
             FOR t_loop in 1..t_linkmembersite.COUNT
                LOOP
                   execute immediate 'update table set col1=:a where id=:b' using t_linkmembersite(t_loop).val1,t_linkmembersite(t_loop).id;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center