Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL Help! Using Variable String as Argument in "where in" clause
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
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