Re: Deadly sins againts database performance/scalability

> Hello,
>
> Why
> (3)
> LOOP
> EXECUTE IMMEDIATE some_sql;
> END LOOP;
> should be avoided ??
>
> OK, it's Dynamical SQL but it could be useful to write scalable batch
> scripts.
> Tell me if i'm wrong.
>
> Cheers,
> Alkos
>

If you want it truely scalable, then you would do something like this (Pseudo code following, so don't trash me for inaccuracies or not existing function/procedures - it's just supposed to give the general idea)

dbms_sql.open_cursor
dbms_sql.parse
LOOP
   dbms_sql.bind
   dbms_sql.execute
END LOOP
dbms_sql.close_cursor

This way, you avoid soft parses (and why you should avoid them is something that you will know if you hang around Tom Kytes website for a few days at http://asktom.oracle.com )

Personally, I use execute immediate only for administrative tasks. But then, I'm most of the time the DBA ;-)

Cheers,

Holger Received on Fri Nov 28 2003 - 05:00:16 CST