How Do I use PL/SQL Tables

[tokerago13]

I have got the following problem...
I have a cursor that will retrieve multiple rows given below...
select to_number(c.description) description,
b.flex_value
from fnd_flex_value_sets a
,fnd_flex_values b
,fnd_flex_values_tl c
where a.flex_value_set_name = 'FSA_PA_INDEX_PERCENTAGE'
and a.flex_value_set_id = b.flex_value_set_id
and b.flex_value_id = c.flex_value_id
and to_date(b.flex_value,'YYYY') between (trunc(sysdate) + 365)
and to_date(x_start_date,'YYYY');
I would like to use the values returned in description and multiply against each other. But I might not know the number of rows that will be returned.
At the end of the fetch for the loop, I would like something like this (description1*description*..description_n).I have not used PL/SQL tables before and would not know how to handle the variables so that I can multiply them against each other at the end of the loop.
Thanks.

 

[SantaMufasa]

Toker,

Although I cannot imaging the business rationale/benefit behind multiplying the numeric value of description, here is code that will multiply the values of description without going back through your description values (stored in a PL/SQL) [BTW, the "dbms_output.put_line" command, below, exists for you to module-test the code before you incorporate the code into a larger code set.]:

set serveroutput on
declare
	hold_value	number;
	loop_cnt	number	:= 0;
begin
        -- Cursor definition and Cursor FOR LOOP follows:
	for r in (select to_number(c.description) description,
            b.flex_value
          from  fnd_flex_value_sets a
               ,fnd_flex_values     b
               ,fnd_flex_values_tl  c
          where a.flex_value_set_name = 'FSA_PA_INDEX_PERCENTAGE'
          and   a.flex_value_set_id = b.flex_value_set_id
          and   b.flex_value_id = c.flex_value_id
          and   to_date(b.flex_value,'YYYY')
                between (trunc(sysdate) + 365) and
                to_date(x_start_date,'YYYY')
		 ) loop
		loop_cnt := loop_cnt+1;
		if loop_cnt = 1 then
			hold_value := r.description;
		else
			hold_value := hold_value * r.description;
		end if;
		-- other processing here
	end loop;
	dbms_output.put_line(hold_value);
end;
/

...or did I misunderstand your question?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:02 (18May04) UTC (aka "GMT" and "Zulu"), 09:02 (18May04) Mountain Time)

 

[tokerago13]

Thanks a lot Mufasa... I've got it working now, using your idea