Re: PL/SQL Help! Trying to Use Variable String as Argument in "where in" clause

The only other way I've done it is when I'm calling a pl/sql script from unix and passed parameters to it, instead of using a stored procedure. The pl/sql script is like this (call it xxx.sql):

define v_version=&1
define v_table=&2
begin
  update &v_table
  set modified_date = sysdate
  where load_version in (&v_version);
end;
/

and the unix script calls it like this:

version='xxx','aaa'
table=my_table
sqlplus -s username/password _at_xxx.sql &version &table

I don't think this is what you wanted, but it does allow sets to be defined.

-Eric

James Lorenzen wrote:

> In article <3560ec9b.112905855_at_news.mindspring.com>,
> theslays_at_mindspring.com wrote:
>
> >Please email any responses to als_at_akc.org
> >
> >Thank you in advance for your help
> >
> >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);
> I have the same needs and my solution was to use dynamic sql (the DBMS_SQL
> package). It is a little more work, but it solves my needs. Please note
> that the "IN" part of the WHERE clause works on "sets" of infomation. A
> variable is is not a set. If anyone has the magic work around to get a
> variable recognized as a set, I am very interested.
>
> HTH
> James
> --
> lorenzen_at_tiny.net | Life is complex; it has
> | real and imaginary parts
Received on Tue May 19 1998 - 20:45:30 CEST