Skip to Main Content
  • Questions

  • pass cursor as parameter like in xmltype (PLS-00405: subquery not allowed in this context)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Markus.

Asked: May 13, 2020 - 11:53 am UTC

Last updated: May 26, 2020 - 4:36 pm UTC

Version: 18c

Viewed 1000+ times

You Asked

Hey,
I found this to create xmltype from SQL:

select xmltype(cursor(select * from emp)) from dual;


Now I want to do the same in PLSQL. But in my opinion it's not so elegant because you have to open and close the cursor...
declare
  l_cur sys_refcursor;
  l_xml xmltype;
begin
  open l_cur for select * from test_tab;
  l_xml:=xmltype(l_cur);
--> did not work :(
--  l_xml:=xmltype( cursor(select * from test_tab) );
  close l_cur;
end;
/


Is there a possibility to pass the cursor directly in the constructor function? How can I do that?

and Chris said...

Perhaps there's some detail in what you're trying to do I'm missing...

But can't you just SELECT ... INTO ... FROM dual?

declare
  l_xml xmltype;
begin
  select xmltype(cursor(select * from hr.employees))
  into   l_xml
  from   dual;    

  dbms_output.put_line ( substr ( l_xml.getClobVal(), 1 , 107 ) );
end;
/

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ok, but...

A reader, May 13, 2020 - 5:34 pm UTC

...the question is if it is possible to pass a cursor directly to whatever (type, procedure etc.) without to open it before and to close it after passing. The way the SQL engine handles the cursor is pretty much shorter (better?).

So are there other "techniques" to pass the cursor in PLSQL???
Chris Saxon
May 14, 2020 - 10:12 am UTC

Ok, but...

Why do you NEED to do this?

To use a cursor variable you need to open it. You could create a function that opens the cursor. And pass this function to XMLTYPE.

You can only pass a cursor expression to a function if it's in a select statement:

create or replace function f ( p sys_refcursor ) 
  return int as
  retval int;
begin
  return 1;
end f;
/

declare
  l int;
begin
  l := f ( cursor ( select * from dual ) );
end;
/

PLS-00405: subquery not allowed in this context

select f ( cursor ( select * from dual ) ) from dual;

F(CURSOR(SELECT*FROMDUAL))   
                            1

nested cursors can appear only as REF CURSOR arguments of a procedure.

Mikhail Velikikh, May 14, 2020 - 11:13 am UTC

Hi TOM,

Could you please explain what exactly the following sentence is referring to?
https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/CURSOR-Expressions.html#GUID-B28362BE-8831-4687-89CF-9F77DB3698D2

> If the enclosing statement is not a SELECT statement, then nested cursors can appear only as REF CURSOR arguments of a procedure.

In the code below, I created a procedure with a REF_CURSOR argument and tried to pass a nested cursor to it, which should work per the comment above.

create or replace procedure my_proc(rc sys_refcursor)
is
begin
  dbms_output.put_line(xmltype.createXML(rc).getStringVal());
end;
/

exec my_proc(cursor(select * from dual))

Obviously, it failed with the PLS-00405 error.
Looks like a documentation bug unless I am missing something.

Chris Saxon
May 26, 2020 - 4:36 pm UTC

I agree - that's unclear at best.
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 channel and Chris's channel 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