Re: Deadly sins againts database performance/scalability

Alkos wrote:
>>
>>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
>>

> 
> Hi Holger,
> 
> Ok, the advice should be :
> "don't use execute immediate within loops to execute DML queries"*

That is exactly right.

> what i understood first. I was wondering whether there was any hindrance
> using exec immdt for DDL in some cases. According to me, there is not
> example :
> open a cursor to list all tables owned by the current user.
> loop throuh the cursor
>     exec immdt to grant another user with s,i,u,d on this table
> end loop
> (this is really pseudo-code,isn't it ? ;-)

As pseudo as can be, eh? ;-)

But you're right, that's the way to use execute immediate, at least, that's what I use it for.

Cheers

Holger Received on Fri Nov 28 2003 - 07:03:06 CST