Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Multiple updates w. static SQL vs. single dynamic SQL update

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

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 07 Jul 2006 15:57:35 -0700
Message-ID: <1152313060.344807@bubbleator.drizzle.com>


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
Received on Fri Jul 07 2006 - 17:57:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US