Re: PL/SQL Help! Using Variable String as Argument in "where in" clause

From: Rohrbacher, Ing. Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: Mon, 01 Jun 1998 00:54:04 +0200
Message-ID: <3571DF8C.806FDEA0@sbox.tu-graz.ac.at>

Hi Alan,

> I'm trying to use a variable defined as a string of values in a SQL
> select statement. So far, defining a variable as a string of values
> works when I run the SQL but doesn't select anything when I try to
> execute the same statement as part of a PL/SQL procedure's cursor.
> Any tips?
>
> Example:
> IF some_condition = 1 then
> v_string := '''A'',''B'',''C''';
> ELSIF some_condition = 2 then
> v_string := '''D'',''E'',''F''';
> ELSE v_string := '''G'',''H'',''I''';
> END IF;
>
> Select 'x'
> from my_table
> where my_field in (v_string);

If you have handfull of values you want to vary you can simply us variables like this

declare

   X1     varchar2(1) ;
   X2     varchar2(1) ;
   X3     varchar2(1) ;
   X4     varchar2(1) ;
   X5     varchar2(1) ;
   X6     varchar2(1) ;

 if some_condition = 1  then
   X1 := 'A';    X2 := 'B';    X3 := 'C';      -- X4 .. 6 := null ;

implicit
 elsif some_condition = 2 then
   X1 := 'D'; X2 := 'E'; X3 := 'F'; else

   X1 := 'G'; X2 := 'H'; X3 := 'I'; end ;

select 'x' from my_table where my_filed in ( X1, X2, X3, X4, X5, X6 );

This really works. I used it very often. If youd don't have a handfull or realy dynamic set you have to use dynamic SQL.
As fare as I know there isn't any other way.

I always wonder why PL/SQL addressing the needs for database accesss can't really deal with sets.
Wouldn't it be amazing and a real great help if one can define a PL/SQL Table , fill it with some value a then say

select 'x' from a_database_table where column_value in ( select column_value from a_plsql_table ) ;

WHY is this not possible. ?? Sounds so easy.

Received on Sun May 31 1998 - 17:54:04 CDT