Re: PL/SQL variables

From: eugen <eugennyffeler_at_netscape.net>
Date: Tue, 04 Apr 2000 20:57:31 +0200
Message-ID: <38EA3B1B.AFFB8BAF@netscape.net>

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_at_caput.com Nygade 6 Fax +45 33 91 24 42
> http://www.caput.com DK-1164 Kbh K
Received on Tue Apr 04 2000 - 13:57:31 CDT