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