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

max · *nix forums beginner Joined: 07 Jul 2006 Posts: 2

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:

psoug · *nix forums Guru Joined: 15 May 2005 Posts: 3492

Malcolm Dew-Jones wrote:

Malcolm Dew-Jones · *nix forums Guru Joined: 04 Mar 2005 Posts: 418

max (cccp.forever@gmail.com) wrote:
: Hi,

: What would be better (faster):

: 1.have a stored procedure loop thru a list of values and execute a
: statement:

: 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 || ')'

How about 3.

insert the values to be selected into a global temporary table and then

update customers
set active = 1
where customer_code in
( select selected_code from my_selection_table
)

psoug · *nix forums Guru Joined: 15 May 2005 Posts: 3492

max wrote:

max · *nix forums beginner Joined: 07 Jul 2006 Posts: 2

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.

Google