Home » SQL & PL/SQL » SQL & PL/SQL » Converting SYS_REFCURSOR to xml (Oracle 12c)
Converting SYS_REFCURSOR to xml [message #665872] Wed, 27 September 2017 23:54 Go to next message
sampathkore4444@gmail.com
Messages: 8
Registered: January 2014
Junior Member
Hi Guys,

I am facing below issue while converting SYS_REFCURSOR output to an xml.

ERROR CODE=-19202
ERROR MESSAGE=ORA-19202: Error occurred in XML processing
ORA-24374: define not done before fetch or execute and fetch


Please find the code below,

DECLARE
  l_refcursor SYS_REFCURSOR;
  l_xmltype   XMLTYPE;
  P_CUST_NO   VARCHAR2(100) := '0122304';
BEGIN
  TEST_PACKAGE.PR_GET_DETAILS(p_cust_no, l_refcursor);
  l_xmltype := XMLTYPE(l_refcursor);
 dbms_output.put_line(l_xmltype.getClobVal());
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR CODE=' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE=' || SQLERRM);
END;

Sometimes the code is working fine and most of the times i am getting above error.

Kindly help.

Regards,
Sampath

[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Sat, 30 September 2017 23:22] by Moderator

Report message to a moderator

Re: Converting SYS_REFCURSOR to xml [message #665907 is a reply to message #665872] Sat, 30 September 2017 23:48 Go to previous message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
The error is probably in the code for TEST_PACKAGE.PR_GET_DETAILS that you have not posted. You should comment out the exception section to get a more exact error message and also test your TEST_PACKAGE.PR_GET_DETAILS separately. Here is a link to a similar problem that produced the same error that may help you diagnose your problem.

http://www.orafaq.com/forum/t/147059/
Previous Topic: Restrict the other session for tables
Next Topic: how to run SQL Developer in debug
Goto Forum:
  


Current Time: Thu Dec 14 22:54:46 CST 2017

Total time taken to generate the page: 0.03599 seconds