Re: Input array string

roger,

in response to your request: 'you're wrong'

i'm not sure how you do it in .NET, but i seem to remember being able to pass an array via ADO as a PL/SQL indexed table

the sample referenced by dan morgan just needs to be modified to populate the type InStrTab variable from either a passed PL/SQL indexed table, or parse out the individual elements from a single parameter that contains a comma-separated list of values

bottom line is, the sample nicely solves the problem of having a varying number of parameters for the IN clause

• mcs

"roger" <rsr_at_rogerware.com> wrote in message news:Xns947D96AF35770rsrrogerwarecom_at_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 - 17:26:01 CST