Re: Dynamic SQL or Stored procedure?

As a general rule you should always use static SQL over dynamic SQL as Oracle is optimized to perform static SQL (reusable shared SQL).

>From your description the procedures could be replace by prepared SQL
using bind variables; however, the stored procedures are likely ensuring the use of reusable shareable SQL statements within the database. The use of procedures for simple SQL statements like this may be because the front-end tool generates its SQL every time throught so it I issue a query for part_number x and then query for part number y the database sees:

select * from part_table where part_no = 'x' select * from part_table where part_no = 'y'

Two different SQL statements built using constants. But when a stored procedure is used the database sees:

select * from part_table where part_no = :bind_variable

The same SQL statement which can be soft-parsed saving overhead.

The use of stored procedures to replace SQL in the front-end application can also be used as a means of supporting portable code. Differences in vendor implementations of SQL can be separated from the front-end.

You should make sure you understand the effects of moving the SQL from the database to the application before you make any such change.

HTH -- Mark D Powell -- Received on Tue Jul 19 2005 - 09:25:54 CDT