12 Replies Latest reply: Oct 16, 2009 5:51 PM by 728498 RSS

    Using cursor as OUT parameter in Stored Procedure

    487280
      Hi guys,

      I have a serious problem.

      I need to use my cursor as an out parameter, but the problem is, HOW CAN I CLOSE THE CURSOR??????

      If I dont close the cursor, my server is getting really slow because of the open cursors, cause I have more than 100 stored procedures, which have a cursor as an out-parameter.

      Here's one of my stored procedures :


      create or replace PACKAGE pkgRes
      IS
      TYPE resType IS REF CURSOR RETURN res%ROWTYPE;
      END pkgRes;

      create or replace procedure res_sel_val
      (p_id in number,cs out pkgRes.resType)
      as
      BEGIN
      open cs for
      select * from res where res_id = p_id;
      --close cs;
      EXCEPTION
      when others then
      raise_application_error(-20970, 'record kan niet geselecteerd worden');
      END res_sel_val;


      How can I close my cursor?
      If I write the "close cursor" (in the code above), it returns an empty cursor, which is not my intention.

      Please help me with this

      Thanks in advance

      Morph 'n Nike