Bulk binding tips

I am writing a PL/SQl standards document. I am thinking about including a section suggesting the use of Bulk Binding with the execute immediate. Does anyone have any suggestions on when to use it and when not to use it?


[Edited by gandolf989 on 10-21-2002 at 11:23 AM]

Maybe appropriate guidance would be that the two approaches should be benchmarked for each case where bulk-binds might be appropriate, and that the extra hassle of bulk binding should be offset by benefit greater than either or .

I guess that requires you to define "might be appropriate". sorry.

Why don't you look around first? Lots of books and Oracle docs have already invented that wheel for you.

Advantages of Dynamic SQL over DBMS_SQL
---------------------------------------
(i) Easier to use.
(ii) Faster.
(iii) Supports object types and collections.
(iv) Can assert purity levels that will be checked at runtime.
(v) Can return results of a query into PL/SQL record types.
(vi) Can open a dynamic ref cursor in PL/SQL and then fetch from it using OCI, the precompilers etc.

Disadvantages
-------------
(i) Dynamic statements are limited to 32K.
(ii) There is no bulk bind capability.
(iii) There is no method of performing piecewise fetches of longs.
(iv) There is no DESCRIBE capability for describing select list items.
(v) There is no equivalent of such functions as DBMS_SQL.LAST_ERROR_POSITION, DBMS_SQL.LAST_SQL_FUNCTION_CODE, etc.
(vi) It is not possible to change the values of bind variables without reissuing the execute or open (and so reparsing) the statement. Note that although a reparse is performed, if the SQL is already in the shared pool then this will simply be a "soft" parse with minimum overhead.

Other Restrictions
------------------
(i) The new bulk bind syntax is not permissable with either the EXECUTE IMMEDIATE or ref cursor methods.
(ii) Pre 8.1.5 the new bulk fetch operations may only be used with ref cursors. With 8.1.5+ neither method may be used with bulk collection.
(iii) It is not possible to bind variables to place holders by name.
(iv) PL/SQL datatypes cannot by used as bind or define variables, with the exception of PL/SQL record types which may be used in INTO clauses.
(v) Where CURRENT OF may not be used.

Further Information
-------------------
PL/SQL User's Guide and Reference, Chapter 10.

[Edited by stecal on 10-21-2002 at 02:02 PM]

I have already done a lot of looking around. I have not seen a definitive source that covered the topic. It seems that there is a lot of things that aren't well documented concerning how bulk binding operates with relation to the execute immediate, which is a 9i SR2 feature. So I am looking for whatever information that I can find on the subject. I have spent a considerable amount of time looking at new PL/SQL features for 9i SR2. It seems that people are just starting to look at what the "Best Practives" should be for PL/SQL in 9i SR2.

The biggest detraction that I have found is that you can't use bulk bind with execute immediate and have a single transaction where the entire transaction works or fails. I.E. When a row fails during an update all of the other changes remain while the row that fails does not change.

It seems like this is a violation of the acid principle. It might be possible to track the rows that failed and to log or build some business logic to handle those rows.

Other Restrictions
------------------
(i) The new bulk bind syntax is not permissable with either the EXECUTE IMMEDIATE or ref cursor methods.