Re: passing array of numbers to stored procedure

In article <Pine.LNX.4.33.0107032105090.20244-100000_at_thenut.eti.pg.gda.pl>, <kazelot_at_thenut.eti.pg.gda.pl says...
>
>I'm using Oracle 8.1.6.
>
>I connect to it through JDBC. I would like to have a stored procedure
>which would take array as a parameter. And then use the array to do
>something like this:
>
>select * from table1
>where table1.id in myArray;
>
>
>My questions:
>1. How can I pass array to stored procedure?

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:712625135727 shows java passing an array of numbers to plsql...

>2. How to create an array in the body of stored procedure?

you'll actually create it in SQL as demonstrated there.

>3. Is select like above legal?
>

not quite but

select * from table1
where table1.id in ( select * from TABLE (cast (some_table as NUM_ARRAY) ) )

is -- where NUM_ARRAY is defined as in the example above.

this works in Oracle8i and up. In 8.0 the syntax for selecting from a SQL Table type is a little different (search for

cast multiset

on that site above for 8.0 examples)

>Tank you,
>kazelot
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp