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

From: Slava Krivonos <kvv_at_cmtk.net>
Date: Wed, 20 May 1998 17:54:24 GMT
Message-ID: <01bd8428$e7842f80$ab652ca6@none.mcit.com>

The only way - use dbms_sql package (I do not remember Oracle Server version but in v7.2 it exists).

Compile the following procedure and use it for run your select from PL/SQL block :

procedure do_sql(str in varchar2) as

cur1 integer;
ret  integer;
lep  integer;

txt varchar2(9);
BEGIN
cur1:=dbms_sql.open_cursor;
txt:=' PARSE ';
dbms_sql.parse(cur1, str,dbms_sql.v7);
txt:=' EXECUTE ';
ret:=dbms_sql.execute(cur1);
dbms_sql.close_cursor(cur1);
return;
EXCEPTION
 when others then
  lep := dbms_sql.LAST_ERROR_POSITION;
  if dbms_sql.is_open(cur1) then

     dbms_sql.close_cursor(cur1);
  end if;
  raise_application_error(-20000,
  'DO_SQL. Can''t' || txt || str || '. Error in ' || lep || ' char.'); END; Slava

Alan Slay <theslays_at_mindspring.com> wrote in article <3560ec86.112884517_at_news.mindspring.com>...
> 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);
>
Received on Wed May 20 1998 - 12:54:24 CDT