Re: binding lists/tables as input

George Thomas <george_at_cc.gatech.edu> wrote in message news:<Pine.LNX.4.44.0207111633320.20786-100000_at_helsinki.cc.gatech.edu>...
> Hi everyone,
> I have the following problem:
> I would like to execute an SQL SELECT query involving the IN subclause.
> The query is operated via a cursor and invoked from a procedure. What goes
> into the cursor as an argument to the 'IN' is a list of strings. This is
> all wrapped in a package. My problem is: what should be the type of the
> argument for the list? I would like to be able to plug in a bind variable,
> but would also like to not have to generate dynamic SQL by string
> concatenation. In browsing the documentation and the archives of this
> group I have encountered clunky solutions as well as hints referring to
> the DBMS_SQL package as well as TABLE OF ... INDEX BY BINARY_INTEGER. I am
> still unclear on what would work best. I am using oracle 8.1.5.
> Any input would be highly appreciated.
>
> george
> -------------------------------------------------------------------------

George, I am pretty sure that you cannot use a single bind variable as Oracle will treat the variable as one value. You will need dynamic SQL to parse the SQL statement with every execution. You should be able to use execute immediate on the statement as a character string concatenated to the in string value clause.

There was a post recently with the subject Dynamic "IN" dated June 20.  Use the advanced search feature of Google search to find solution examples for a dynamic in clause.

HTH -- Mark D Powell -- Received on Fri Jul 12 2002 - 13:18:58 CDT