Re: Input array string

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in news:eaSdne_0meU6bovdRVn-hg_at_comcast.com:

> roger,
>
> in response to your request: 'you're wrong'

I wish:)

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

OK, we'll stick with ADO then, since I know that it can't be done with ADO.NET...

How do you create that PL/SQL indexed table in ADO?

Are you suggesting there is some sort of host variable that you create, populate, and then pass as a host parameter to your stored proc?

Or, are you suggesting that one create a string that contains a PL/SQL block to create the indexed table variable, and pass it to the stored procedure?

Those are two ways I can see to go about it, and neither of those is possible in ADO.NET - at least not with the provider that comes with the .NET 1.1 framework.

Please enlighten me further...

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

Oh - you're suggesting we take our array of values, format it to a single string variable, pass that to the stored proc, and then parse it with PL/SQL, loading it to a temporary PL/SQL variable as we go, and then query that.

Well, ya, I guess that would work, but - yuk.

Besides, at that point, you could just use dynamic SQL to compose a statement that used the composed string of values directly, and not bother with the manual parsing and variable initialization, and the oracle special magic syntax to query from the table variable.

I just don't think it should be so complicated to do something so bleedin useful.

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

Yes, I agree with that, with the stipulation that it's only good if you're working entirely within PL/SQL. If you're trying to communicate with anything on the outside, then I think there's quite a bit of room for improvement.

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