DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Ref Cursor correct usage ?

  1. #1
    Join Date
    Dec 2001
    Posts
    26

    Unhappy

    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

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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,
    Cheers!
    OraKid.

  3. #3
    Join Date
    Dec 2001
    Posts
    26

    Unhappy still no good

    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

  4. #4
    Join Date
    Dec 2001
    Posts
    5

    Cool

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width