Re: Input array string

Cowboy wrote:

> I have a .NET app that pulls a group of values from a listbox. I need
> to have that list in a WHERE IN clause:
>
> SELECT * FROM TABLE1
> WHERE Column1 IN {PASSED IN VALUES}
>
> Not sure how to do this in Oracle. I do know the following.
>
> 1. I cannot pass in an array or a table type as a parameter, as I am
> stuck with the Microsoft Oracle Client provider and not the Oracle
> downloadable provider. Input will have to be something like a
> comma-separated string as VARCHAR.
>
> 2. I know how to create a TABLE TYPE and fill it with a loop, so I can
> create the TABLE TYPE in the sproc and utilize it, if that is the
> answer.
>
> 3. I had to prove to the other developer that this does not work:
>
> CREATE PROCEDURE MyProc
> (
> IN_STRING VARCHAR2,
> OUT_CUR Types.cursor_type
> )
> AS
>
> BEGIN
> SELECT * FROM TABLE1
> WHERE Column1 IN IN_STRING ; -- Invalid numeric error
> END;
>
>
> NOTE: REF_CURSOR is fairly normal for output to .NET in our environ.
>
> I also have tried
>
> WHERE IN TableTypeNameHere
>
> WHERE IN (SELECT * FROM TableTypeNameHere)
>
> I know how I would solve this in SQL Server, but I am lost with the
> proper Oracle implementation, other than to embed a dynamic SQL query
> in the app (not my choice) or create a temp table (an option, but
> would prefer something that can use an array or TABLE TYPE in the
> WHERE IN clause).
>
> TIA,
>
> Gregory A. Beamer

Go to: http://www.psoug.org/reference/conditions.html

and scroll down to the demo titled: Complex IN Demo

And thank you Connor for the brilliant inspiration for this piece of work.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)