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

Hmm... With a query like this:

> select * from table_a where id in (:l_var);

you are always passing in ONE inlist element. If l_var is VARCHAR2, then its size is limited to 4000 bytes (up to 2000 double-byte characters or 4000 single-byte characters), so you may be hitting this limit. Note that Oracle will not expand l_var into a list of separate values for you. If you want a string like '1,2,3,4' expanded into inlist, you may use a nested table type, a function that will parse the string, fill a nested table instance with values and return it, and WHERE id IN (SELECT * FROM TABLE( CAST( f(:l_var) AS nt_type ) ) ) syntax for the query itself. http://asktom.oracle.com has some examples of how this can be done (use search/archive to get to relevant articles.) Note that you will still be limited to 4000 bytes for l_var size because this is SQL limit for VARCHAR2 (PL/SQL limit is 32767 bytes so you may be able to call the f() function with longer string in an anonymous PL/SQL block, but since you are passing l_var as bind variable to the SQL statement, SQL limit will apply.)

Corrections and additions welcome.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"chandran" <steelbird_at_hotmail.com> wrote in message
news:cfc1bedb.0209230030.711e6251_at_posting.google.com...


> hi all,

>

> i have gone through the postings, but still could not find a

> satisfactory reason.

>

> am using :

> powerbuilder 7.02 + oracle 8.1.5

>

> my doubt is :

> 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);

>

> i found from the powerbuilder newsgroups that this is specific to

> Database vendor.  what parameter in the Oracle should i redefine to

> increase or support an unlimited number or elements in the "IN CLAUSE

> "

>

> TIA

>

> chandran

> steelbird_at_hotmail.com