Passing V array type in, SYS_REFCURSOR OUT parameter in PL/SQL procedures.
957828Oct 6 2012 — edited Oct 12 2012Hi,
I am writing a procedure as -
Create or replace procedure as testing(v_type IN VARCHAR2, v_ssid IN ID_LIST,
v_from_date IN DATE, v_to_date IN DATE,
io_cursor OUT SYS_REFCURSOR)
AS
BEGIN
If v_type = 'NONE' THEN
BEGIN
For i IN 1..v_ssid.count LOOP
OPEN io_cursor for
Select id,curr,sum(value)
From curr_summ
where id=v_ssid(i)
and tr_date between to_date (v_from_date) and to_date (v_to_date)
GROUP by id, curr ;
END LOOP ;
END ;
END IF ;
END ;
/
Create or replace type ID_LIST AS VARRAY(500) of number(10) ;
/
I have created above procedure to execute from java for passing varray to the procedure but it is returning the result set for last value from the varray.
For Eg.
I am passing 15, 105, 2335 as varray for v_ssid, with all other proper parameters, the procedure returns result set to java for v_ssid 23365
What should I add to the procedure to get result set for all values passed.
Help is really appreciated.
Thanks in advance.