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

max wrote:
> Daniel, thanks for your reply. I got this from the the "Complex IN
> Demo":
>
> DECLARE
>
> i PLS_INTEGER;
> InStr VARCHAR2(20) := '10,30';
>
> BEGIN
> SELECT COUNT(*)
> INTO i
> FROM emp
> WHERE deptno IN (InStr);
>
> dbms_output.put_line(i);
> END;
> /
>
> This will never work. Assuming deptno is a number you'll get ORA-01722
> Invalid Number.
> What happens is Oracle tries to convert InStr which is '10,30' to a
> number and it can't. It will not try to parse it into 10 and 30 and put
> those as the IN clause parameters.
>
> The CAST example is good but we found it would kill the indexes, making
> update expensive.
>
> The question is more theoretic. I would like to know what is more
> expensive hitting the table a 1000 times to update 1 record at a time
> or go with a dynamic SQL with the expense of it being compiled along
> with the execution plan being calculated every time the procedure is
> executed.
>
> Thanks.
>
> DA Morgan wrote:

>> 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

1. Don't top post.
2. It is not intended to work. It is intended to show what DOESN'T work. Continue with the demo until you find the TABLE(CAST( code.

-- 
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