Is there a way to close the cursor after reading it?

Question

working with oracle sql and oracle service bus. I have a problem with closing the connection. An example of a simple procedure I use is the following :

create or replace procedure getempdata(
  v_id in number,
  q out sys_refcursor
)
as
begin
  open q for select * from employees where id = v_id;
end;

I need a way to close the cursor after getting the data from it. but if I use close Q; , I can't read the data returned in service bus adapter . The question is : Is there a way to close the cursor (for memory management) from service bus after reading it? And if there is not, Is there a way to do so from the procedure with returning the data as output?

Note: I don't call the procedure from sql code, so I can close it. I call it as a webservice using service bus webservice that calls the procedure by a database adapter; so I need the data returned in service bus

score 0 · Answer 1 · answered Sep 29 '22 at 11:50

You'd close it from the caller.

Your procedure (adjusted to Scott's sample schema):

SQL> create or replace procedure getempdata(
  2    v_id in number,
  3    q out sys_refcursor
  4  )
  5  as
  6  begin
  7    open q for select ename from emp where deptno = v_id;
  8  end;
  9  /

Procedure created.

Let's call it:

SQL> set serveroutput on
SQL> declare
  2    l_rc sys_refcursor;
  3    l_ename emp.ename%type;
  4  begin
  5    getempdata(10, l_rc);             --> procedure is called here
  6    loop
  7      fetch l_rc into l_ename;
  8      exit when l_rc%notfound;
  9      dbms_output.put_line(l_ename);
 10    end loop;
 11    close l_rc;                       --> cursor is closed here
 12  end;
 13  /
CLARK
KING
MILLER

PL/SQL procedure successfully completed.

SQL>

score 0 · Answer 2 · answered Sep 29 '22 at 11:50

0

Yes, close it once you have read from it (or if an exception occurs that prevents reading from it) using the CLOSE statement.

DECLARE
  v_cur SYS_REFCURSOR;
  v_row EMPLOYEES%ROWTYPE;
BEGIN
  -- call the procedure
  getempdata(1, v_cur);

  -- read the rows
  LOOP
    FETCH v_cur INTO v_row;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_row.id);
  END LOOP;
  
  -- close the cursor
  CLOSE v_cur;
EXCEPTION
  WHEN OTHERS THEN -- Handling OTHERS is bad practice, normally you should be more specific
    IF v_cur%ISOPEN THEN
      CLOSE v_cur;
    END IF;
END;
/

fiddle

answered Sep 29 '22 at 11:50

MT0

143,790
11
59
117

I don't call the procedure from sql code, so I can close it. I call it as a webservice using service bus webservice that calls the procedure by a database adapter; so I need the data returned in service bus – Verina Sep 29 '22 at 12:09
@Verina Then you need to make sure that the service bus closes the cursor in whatever code you are using there. You cannot close the cursor in the PL/SQL procedure where you open it as it would be closed before you can read from it; it needs to be afterwards and so it needs to be the service bus that handles closing it. – MT0 Sep 29 '22 at 12:42

Is there a way to close the cursor after reading it?

2 Answers2