OCI Question

I'm learning the OCI interface, and I have come up with some questions.

As I understand it so far, the only statements that need to have host variables bound are SELECT, INSERT, UPDATE and DELETE. No other statements can have host variables, and are executed immediately on being parsed.

My question relates to the use of the array interface in WHERE clauses.... in other words, when it is possible (if at all) to bind a placeholder to an array as opposed to a scalar.

SELECT ... WHERE Clause

   It generally does not make sense to bind placeholders (:xxxx)    appearing in a WHERE clause to arrays. I.e. in "WHERE    <column> = :someName" the buffer bound to :someName must be a    scalar.

   Question: what about "WHERE <column> IN (:someName)"? Can you use a    host array in this case to provide multiple values?

DELETE ... WHERE Clause

   Question: Can you bind an array to a placeholder in a DELETE    WHERE clause to delete multiple records? This sounds logical    for a simple statement such as "DELETE FROM table WHERE KEY =
:value" and an array bound to :value would give multiple keys
   to be deleted. However, what does the following mean? "DELETE    FROM table WHERE COL_A=:value1 AND COL_B=:value2" if both
:value1 and :value2 are bound to arrays?

There are similar questions for UPDATE.

What I need is a general rule (or rules) one can use to decide when the RDBMS allows you to use an array in a WHERE clause, and what the resulting semantics are.

Some possible candidates:

1. Arrays cannot be bound to placeholders in WHERE clauses
2. You can bind an array but only the first element gets used
3. You can bind arrays, but if you do all placeholders in the WHERE clause must be bound to arrays of the same size (number of elements), and results in the statement being executed once for each horizontal 'slice' through all the arrays

   I.e. If you have three placeholders :a :b and :c and you bind    all three to arrays of size 10, the statement is executed 10    times... once using a[0], b[0] and c[0], once using a[1],    b[1] and c[1] .... etc up to a[9], b[9] and c[9]).    

   How this interacts with a SELECT (All the results are queued    up and returned in order?) and UPDATE (The same set of    updates gets applied to all 10 record sets?) is another    interesting question.     

Any assistance or clarification would be greatly appreciated.

Thanks

(If you post a reply, I'd appreciate an e-mail copy also... thanks)

Jim Garrison
jhg_at_isd.tandem.com Received on Wed Sep 06 1995 - 00:00:00 CEST