Re: powerbuilder unable to send more than 1000 elements in the number array - dw parameter

chandran wrote:

> powerbuilder 7.02 + oracle 8.1.5
> my datawindow works fine till it has around 950 elements (a long
> array)
> when i have more, the retrieve returns -1.
> eg:
> select * from table_a where id in (:l_var);

The limit on the number of literals that can be supplied in an IN clause in Oracle is IMO not the issue.

The problem is design an application that throws 950 or more literals (or items/elements) in such a clause. That makes no sense.

On the performance side - let's say for argument sake that the elapsed time from transmitting the query from the client, to receiving the row on the client, is 1 second. Now you want Oracle to do this 950 times. That means 15 minutes elapsed time for the user in waiting for the completion of the query.

On the design side - if application logic demands that 950 literals be passed via a SELECT clause, then there is something very seriously wrong with the application logic, database design, or both.

If you need to perform single row processing then it is better to rather loop thru the processing routine that does a single row select at a time. Call this routine from a FOR loop that cycles through the list of 950 elements. I am btw heavily opposed to cursor based processing - do it with a single SQL statement and think very, very, carefully when implementing cursor based processing.

If the element values are from an Oracle table, then why not perform a JOIN?

PowerBuilder does not imply that the power of proper application design and database design, are now removed from the programmer. The basic principles of software engineering applies. Always. Period.

--
Billy