Ref Cursor correct usage ?

I just can seem to get any procedure executed that uses refcursor for returning the records selected by a stored procedure..

It give the ERROR - wrong number or type of arguments in call to this procedure.....

here is the spec of the package and the body and the code i am using in VB to call this procedure...

Create or replace package FileFormRTTP IS
TYPE cur_TP_type IS REF CURSOR;
Procedure SP_FileFormRTTP(
intCLUID IN Number,
intTUID IN Number,
intRTUID IN Number,
cur_TP IN OUT cur_TP_Type);
end;

Create or replace package body FileFormRTTP AS
Procedure SP_FileFormRTTP(
intCLUID IN Number,
intTUID IN Number,
intRTUID IN Number,
cur_TP IN OUT cur_TP_Type) IS

the query that i am running is something like

Open cur_TP FOR
SELECT Distinct a.RTTPUID, b.TP From Form_Index a, Index_TP b Where
a.TUID = intTUID And a.RTUID = intRTUID And
a.CLUID = intCLUID And a.CLUID = b.CLUID And
a.RTTPUID = b.TPUID;

------------------
vb code to call the above procedure
.............

With cmdRT
.CommandText = "FileFormRTTP.SP_FileFormRTTP"
.CommandType = adCmdStoredProc
.ActiveConnection = connCmd

Set paramCLUID = .CreateParameter("CLUID", adInteger, adParamInput)
Set paramTUID = .CreateParameter("TUID", adInteger, adParamInput)
Set paramRTUID = .CreateParameter("RTUID", adInteger, adParamInput)

paramCLUID.Value = CLUID
paramTUID.Value = TUID
paramRTUID.Value = RTUID

.Parameters.Append paramCLUID
.Parameters.Append paramTUID
.Parameters.Append paramRTUID

Set rsRT = .Execute
------------------------------------------------------------------

Please advise why am i getting the error - wrong number or type of arguments in call to procedure.

thanks very very much

hi i think this may give u some idea :)

create or replace package attendance
as
type att_cur is ref cursor;
procedure get_single_entry(v_sdate in date, v_edate in date, s_cursor in out att_cur);
end attendance;

create or replace package body attendance
as
procedure get_single_entry(v_sdate in date, v_edate in date, s_cursor in out att_cur)
as
lv_record_to_date date;
begin
open s_cursor for
select record_to_date,record_to_date from
l_sec_account where record_to_date between v_sdate and v_edate;
loop
fetch s_cursor into lv_record_to_date, lv_record_to_date;
exit when s_cursor%notfound;
dbms_output.put_line('lv_record_to_date'||lv_record_to_date);
end loop;
end get_single_entry;
end attendance;

declare
type att_cur is ref cursor;
lv_s att_cur;
lv_record_to_date date;
lv_record_to_date_ins date := '23-SEP-01';
begin
attendance.get_single_entry(lv_record_to_date_ins,lv_record_to_date_ins,lv_s);
loop
fetch lv_s into lv_record_to_date, lv_record_to_date;
exit when lv_s%notfound;
dbms_output.put_line(lv_record_to_date);
end loop;
end;

rgds,

Thanks a lot for your reply..

But still not looking good for me.. i used exactly the same procedure as you specified.

IS there something that I can change in VB.

my earlier post in this thread has that code can u find anything that i can add to that.

thank you very much

Gaurav

Instead of writing procedure go for Function u will get it.
I am using refcursor from VB. If u need more help i can give u the code also.