Skip to Main Content
  • Questions
  • Find the select statement for a sys_refcursor


Question and Answer

Chris Saxon

Thanks for the question, Shashi.

Asked: September 29, 2023 - 7:17 am UTC

Last updated: September 29, 2023 - 5:48 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

Hello All ,

can we able to find the select statement which is used for sys_refcursor ?

function f_my (id in number )    return sys_refcursor
      l_rc sys_refcursor;
      open l_rc for select * from emp where empno=id;
      return l_rc;

Here what happens, it returns the result set , but i would like to see which select statement has been used to get the result set?

In a simple word— contents of the sys_refcursor

Expecting the output /return value as select * from emp where empno=id

Thanks in advance.

and Chris said...

There are no attributes or other properties of a cursor variable that will tell you its statement that I know of.

Assuming it was opened in PL/SQL, you have PL/Scope enabled, and you know which function opened it you can get the query from the *_statements views:

create or replace function f_my ( id in number ) return sys_refcursor
  l_rc sys_refcursor;
  open l_rc for select * from employees where employee_id=id;
  return l_rc;

select text from user_statements
where  object_name = 'F_MY'
and    type = 'SELECT';


  (2 ratings)


Shashi, September 29, 2023 - 4:06 pm UTC

Thanks much Chris for the response. One last query . If we do not have the access to DBA_statements /user_statements/all_statements , do we have any alternate options to check this select statement ?

Kindly advise.
Chris Saxon
September 29, 2023 - 5:48 pm UTC

You always have access to user_statements (provided you can connect as the code owner & there are no VPD or other policies disabling it).

You can check user_source too, though parsing this to find your statement can be trickier.

A reader, September 29, 2023 - 7:26 pm UTC

Ok sure. thanks chris..

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library