Updating a range of records

[lev_ko]

Using TOracleQuery, I'd like to select all records, for which a column's value is in a specified range.

Using the SQL IN sub-clause is inefficient and limited to a maximum allowed number of items (typically 1000). The most efficient way should be to use array variables:

SELECT * FROM CUSTOMER
WHERE CUSTOMER_ID = :ids

Unfortunately, when I use variant arrays with SELECT statements, such as the example above, the only records fetched are those with CUSTOMER_ID equal to the first item in the array.

What am I doing wrong ?

Thanks,
Lev

 

[F.Hinlopen]

The manual states that this is possible for update, insert an delete (not select). DOA is probably packaging these statements inside a FORALL, so I think that what you want to do is not possible in this manner.

I think that using IN is optimal in this situation. If the the 1000 item limit is an issue, then you have little alternative but to put your arguments in a table and do a join (or IN subselect).

Greetings,

------------------
Frans

 

[lev_ko]

Thank you very much for your help. I am looking for other solutions, though.

Using IN is less efficient than variables, because it requires much parsing and does not allow certain optimizations of Oracle.

When the number of items in IN exceeds the maximum, a UNION can be used to add more records, but that is extremely inefficient and also doesn't allow certain SQL constructs.

As for temporary tables with temporary data, I am looking for a more elegant solution.

Thanks,
Lev

 

[F.Hinlopen]

Good luck! If you find a better method I would be very interested. The UNION problem you mentioned is basically the reason why I think that what you want is not possible in Oracle, or any other DBMS for that matter, because it seems that what you want can only be achieved by internal use of a union.

Greetings,

------------------
Frans