Could someone explain the behaviour when a ref cursor is passed back to a client program in the particular instance described below.
Fist, create a procedure that passes back a ref cursor.
create or replace procedure abc (rc out sys_refcursor)
is
begin
dbms_session.set_identifier('Me');
open rc for
select 'Row returned' myrow,
sys_context('USERENV', 'CLIENT_IDENTIFIER') ctxt
from dual
where sys_context('USERENV', 'CLIENT_IDENTIFIER') = 'Me';
dbms_session.clear_identifier();
end;
This proc will set a coxtext variable, open a ref cursor using the value of this context, then clear the contxt.
Now call the proc from a client (eg SQL*Plus) and processes the refcursor:
SQL> col ctxt format a30
SQL> variable g_ref refcursor
SQL> exec abc(:g_ref)
SQL> print g_ref
MYROW CTXT
------------ ------------------------------
Row returned
Clearly the where clause sees the context as being set (becuase a row is returned), but not when selecting the column (as the value is null).
My questions are:
When is the cursor "opened" and what exactly happens when a cursor is opened.
Is the fetch doing more than just fetching?
Any enlightenment around this behaviour is greatly appreciated.