Xref: alice comp.databases.oracle.misc:56681
From: eugen <eugennyffeler@netscape.net>
Newsgroups: comp.databases.oracle.misc
Subject: Re: PL/SQL variables
Date: Tue, 04 Apr 2000 20:57:31 +0200
Organization: --
Lines: 93
Message-ID: <38EA3B1B.AFFB8BAF@netscape.net>
References: <38EA222D.DB6C2248@caput.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: bw107zhb.bluewin.ch 954874620 19326 195.186.142.125 (4 Apr 2000 18:57:00 GMT)
X-Complaints-To: newsmaster@bluewin.ch
NNTP-Posting-Date: 4 Apr 2000 18:57:00 GMT
X-Mailer: Mozilla 4.72 [en] (Win95; I)
X-Accept-Language: en
Path: alice!news-feed.fnsi.net!netnews.com!cpk-news-hub1.bbnplanet.com!news.gtei.net!npeer.kpnqwest.net!blackbush.xlink.net!newscore.gigabell.net!news.datacomm.ch!newsfeed-zh.ip-plus.net!news.ip-plus.net!news.bluewin.ch!not-for-mail

Just a wild guess, but something like this shoud work

hth
eugen

declare
  v_msg   varchar2(2000) := null;
  v_crs    integer  := 0;
  v_cnt    number :=1;
begin
  v_crs := dbms_sql.open_cursor();
  v_msg := "SELECT a FROM b WHERE c = ";
  for crs in (SELECT x FROM y WHERE z = p)
  loop
     if (v_cnt = 1) then
       v_msg := v_msg ||crs.x;
     else
       v_msg := v_msg||' '||crs.x;
     end if;
     v_cnt := v_cnt + 1;
  end loop;
  dbms_sql.parse(v_crs, v_msg, dbms_sql.native);
  // and your other processing
end;


Morten Primdahl wrote:

> Hi,
>
> I'm new to PL/SQL and I could use a hint. I am selecting
> from a TABLE where the result can contain either 1 or two
> rows. I'd like to store the values of those rows, as I need
> to make a second query based on those.
>
> v_Value1  VARCHAR2(10);
> v_Value2  VARCHAR2(10);
> v_Result  VARCHAR2(10);
> v_Query1  VARCHAR2(50);
> v_Query2  VARCHAR2(50);
> v_Dummy   INTEGER;
> v_Cursor  NUMBER;
>
> v_Cursor := DBMS_SQL.OPEN_CURSOR;
> v_Query1 := 'SELECT x FROM y WHERE z = p';
>
> DBMS_SQL.PARSE(v_Cursor, v_Query, DBMS_SQL.V7);
> DBMS_SQL.DEFINE_COLUMN(v_Cursor, 1, v_Result);
> v_Dummy := DBMS_SQL.EXECUTE(v_Cursor);
> DBMS_SQL.COLUMN_VALUE(v_Cursor, 1, v_Result);
>
> LOOP
>   IF DBMS_SQL.FETCH_ROWS(v_Cursor) = 0 THEN
>     EXIT;
>   END IF;
>
>   --This is where I'm stuck, if there's only one
>   --row in the resultset, I want to be able to
>   --create v_Query2 based on that, if there are two
>   --rows, I want to base the query on that instead
>   --and I need both values to do so. The below demonstrates
>   --what I need, but is hopelessly wrong...
>
>   IF DBMS_SQL.FETCH_ROWS(v_Cursor) = 1 THEN
>    v_Query2 := 'SELECT a FROM b WHERE c = ''v_Result''';
>   END_IF;
>
>   IF DBMS_SQL.FETCH_ROWS(v_Cursor) = 2 THEN
>    v_Query2 :=
> 'SELECT a FROM b WHERE c = ''v_Result(1)''||'' ''||''v_Result(2)''';
>   END_IF;
>
> END LOOP;
>
> After this, v_Query could eg. be:
>
> SELECT a FROM b WHERE c = 'horse';
>
> or
>
> SELECT a FROM b WHERE c = 'horse'||' '||'cow'
>
> Eeek.
>
> Thanks,
>
> Morten
>
> --
> Morten Primdahl         Caput ApS       Tel +45 33 12 24 42
> morten@caput.com        Nygade 6        Fax +45 33 91 24 42
> http://www.caput.com    DK-1164 Kbh K