Re: Input array string

From: roger <rsr_at_rogerware.com>
Date: Tue, 27 Jan 2004 22:45:33 GMT
Message-ID: <Xns947D96AF35770rsrrogerwarecom@63.240.76.16>

That's a handy reference page with some interesting info. Unfortunately, it doesn't address the question the poster is asking.

He is saying that he's got some application with an array of values that he wishes to pass to a stored procedure, for use in an SQL statement IN clause.

Nothing about using the InStrTab table type is going to help him to pass those values to the stored procedure.

Unfortunately, I don't have a good answer for him, because as far as I can see, there is no good answer to this problem, which fundamentally is - how do you pass an array to a stored procedure.

Answer is - you can't. If you could, then you could construct a variety of solutions for building up the IN clause, including initializing a pl/sql table variable and querying it as shown.

But, since you can't pass the array of values to the stored procedure, it's all moot.

Please, tell me I'm wrong!:)

Daniel Morgan <damorgan_at_x.washington.edu> wrote in news:1074892854.75017_at_yasure:

> 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. 
> 

Received on Tue Jan 27 2004 - 16:45:33 CST