Re: PL/SQL variables

Thanks Eugen and Jason. I got it to work, the counter was just what I needed.

Morten

eugen wrote:
>
> 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

--

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