Re: Execute Immediate and updates

I found a way to deal with an arbitrary update statement with bind variables that resulted in approximately an 8% hit.

Following up on Thomas' idea, I made my dynamic SQL statement an anonymous declare/begin/end block. I made a variable for each field in the table and used a spurious assignment for each bind variable. I then custom built the update statment ,picking and choosing the appropriate bind variables. An example would be

Sql_String:= 'declare '||
'field1_val varchar2(20); '||
'field2_val varchar2(20); '||
'id_val NUMBER; '||
'BEGIN '||
'field1_val := :1; '||
'field2_val := :2; '||
'id_val := :3; '||
'UPDATE my_table SET field1 = :1 WHERE id = :3;
'||
'END;';
EXECUTE IMMEDIATE SQL_STRING USING my_field1, my_field2, my_id;

COMMIT;
....

My benchmarks compared to a stored procedure with static SQL showed only an 8% difference. Since this resolves the combinatorial problem of updating arbitrary fields, this is more than acceptable.

Thank you very much Thomas.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 29 1999 - 10:19:36 CST