Re: Multiple updates w. static SQL vs. single dynamic SQL update

max wrote:
> Hi,
>
> What would be better (faster):
>
> 1.have a stored procedure loop thru a list of values and execute a
> statement:
>
> update customers
> set active = 1
> where customer_code = value
>
> 2. create a comma delimited string of values like so:
>
> list := value1 || ',' || value2 || ',' || value3....
>
> and using it in a dynamic SQL like so:
>
> v_sql = 'update customers
> set active = 1
> where customer_code in ( ' || list || ')'
>
> Thanks.

Single row processing, by definition, is always a bad idea so throw away the loop if there is an alternative.

Native dynamic SQL is never a first choice so that's not necessarily the best idea either.

What is impossible to tell from what you've written is why you need either. Go to www.psoug.org and click on Morgan's Library. Click on "Conditions" and scroll down to "Complex IN Demo."

Why won't this work?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org