Skip to Main Content
  • Questions

  • Find the select statement for a sys_refcursor

Breadcrumb

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
    is
      l_rc sys_refcursor;
    begin
      open l_rc for select * from emp where empno=id;
      return l_rc;
   end;

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
is
  l_rc sys_refcursor;
begin
  open l_rc for select * from employees where employee_id=id;
  return l_rc;
end;
/

select text from user_statements
where  object_name = 'F_MY'
and    type = 'SELECT';
/*
TEXT                                              
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:B1     
*/

Rating

  (2 ratings)

Comments

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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