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: 8751
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: regexp_replace append 0 to single digit date,month,hour,minute,second
Next Topic: Cuimulative totals
Goto Forum:
  


Current Time: Mon Nov 20 15:07:15 CST 2017

Total time taken to generate the page: 0.01818 seconds