Re: Help - Using parameters in SELECT statements

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/02/12
Message-ID: <3301CE34.5D2C_at_iol.ie>#1/1

Shari Fletcher wrote:
>
> Am trying to compare a numeric value against a list of values to in a SQL
> Statement in a PL/SQL stored procedure using IN. Am using SQL Windows as a
> front end and submitting the values as a comma-delimited string (i.e.
> '3,4,5') but cannot do because PL/SQL will literally translate this in the
> where clause and compare its numeric key as follows:
>
> WHERE excpt_ID IN ('3,4,5')
>
> Cannot send the parameter from SQL Windows without the single quotes
> because it will treat the values as additional parameters. also, for some
> reason, stripping out the single quotes did not work ---
>
> thanks in advance for any help
>
> s.fletcher
> terrace systems corporation

.
.
.

A) You clearly have to pass the list as a single argument (since PL/SQL lists are not accepted as formal parameters in SQL statements. B) This parameter must be a varchar2 type C) The syntax IN ('value,value,...') is clearly incorrect

As I see it, you therefore have two choices: 1) Unpack the varchar2 argument into separate numeric values and insert each of them into a work table so that the query becomes:

	select ... from table where ... excpt_ID in
		(select temp_val from work_table
		 [where session = userenv('sessionid')])

Note the use of 'sessionid' to distinguish values for the current session from those of other concurrent sessions.

2) The better solution IMHO is to use dynamic SQL inside the PL/SQL block as shown below:

create or replace procedure TEST

   (comma_list IN varchar2) as

   S1  varchar2 (200) := 'select col1, col2, ... from table where ...'||
                         'excpt_ID in '||'('||comma_list||')'||
                         '...';
   C1  integer;	-- cursor handle
   X1  number;		-- execution return code
   col1_val  ...;	-- values returned
   col2_val  ...;	-- from SELECT statement

begin

   C1 := dbms_sql.open_cursor;
   dbms_sql.parse(C1,S1,dbms_sql.v7);

   dbms_sql.define_column(C1,1,col1_val;
   dbms_sql.define_column(C1,2,...);
   X1 := dbms_sql.execute(C1);
   loop
      if dbms_sql.fetch_rows(C1) = 0
         then exit;
      end if;
      dbms_sql.column_value(C1,1,col1_val);
      dbms_sql.column_value(C1,2,...);

   end loop;
   dbms_sql.close_cursor(C1);
end TEST;
/

Good luck. Received on Wed Feb 12 1997 - 00:00:00 CET