PL/SQL variables

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