Re: Passing an "in" string to a Stored Procedure

Hi Rudi,

Nice and neat.

The only problem might be that if the query was complex and the IN clause was to restrain the dataset from a collection of large tables, then the loop approach would mean multiple executions and parses of the query. Whereas the dynamic approach would execute in one.

Also if the IN claused query was to retrieve multiple rows for each iteration of the IN subscript the set handling might get a bit tricky. Although your inner select could just as easily be a cursor there is still the problem of data handling of the composite data set.

cheers

Rod

Rudi Demeulenaere wrote:
>
> Rod Corderey wrote:
>
> > What you could do would be to use DBMS_SQL to execute you're statement
> > dynamically, then you could build in the passed in-list.
>
> A way to do it without using DBMS_SQL is using a PL/SQL table as input for the
> stored procedureand then loop through it, style
> ...
> i number;
> ...
> i:=0;
> loop
> i:=i+1;
> exit when not pl-sql-table.exists(i);
> select * from thing
> where number_id = pl-sql-table(i);
> ...
> end loop;
> ...
>
> r u d i
> (remove anti-spam measure from reply-address)
Received on Sun May 10 1998 - 00:00:00 CDT