LPX-00209: PI names starting with XML are reserved, XMLTYPE.transform

We started getting the LPX-00209 problem when we recently upgraded our database system from Oracle llg to 12c. I'm trying to find the solution to why I'm now getting the error.

I have found what maybe the problem below. We moved to Oracle 12c and found that l_xml.transform(XMLTYPE (l_xslt)) no longer works in later version of Oracle. Below is my procedure, trying to transform the XML using the XSL style sheet. Is there another function I can use instead of the l_xml.transform(XMLTYPE (l_xslt)). Once it's transformed the XML it then passes it back out and try's to then put this XMl into a clob using p_resulting_xml.getclobval() and pass to the procedure write_file_email.

Oracle support documentation below explaining the problem.

https://support.oracle.com/knowledge/Oracle%20Database%20Products/1642080_1.html

PROCEDURE end_workbook(p_report_clob  IN OUT    CLOB,
                       p_xml          IN OUT XMLTYPE)
IS
  l_xslt         CLOB;
  l_xml          XMLTYPE;
BEGIN

  Dbms_Lob.Writeappend(p_report_clob, 13, '</WORKSHEETS>');

  l_xml := XMLTYPE(p_report_clob,NULL,0,1);
  Dbms_Lob.Freetemporary (p_report_clob);

  l_xslt := load_file('EXT_XSL_IN_DIR', 'ndu_sfich_report.xsl');
  p_xml := l_xml.transform(XMLTYPE (l_xslt));

END end_workbook;



PROCEDURE write_file_email(p_filename    IN VARCHAR2
                          ,p_resulting_xml             IN XMLTYPE
                          ,p_first_visible_worksheet   IN PLS_INTEGER DEFAULT 0)
  IS 

  BEGIN    

    write_file (p_dir                     => pb_gen_report_dir  -- VARCHAR2
               ,p_filename                => p_filename         -- VARCHAR2
               ,p_file                    => p_resulting_xml.getclobval() -- CLOB
               ,p_openmode                => 'W' -- VARCHAR2
               ,p_first_visible_worksheet => p_first_visible_worksheet);  --PLS_INTEGER

Error message below

15:02:40 Error: ORA-31011: XML parsing failed 
ORA-19213: error occurred in XML processing at lines 1 
LPX-00209: PI names starting with XML are reserved 
ORA-06512: at "SYS.XMLTYPE", line 138 
ORA-06512: at "PRBLK.NDU_REPORTING", line 330 
ORA-06512: at "PRBLK.NDU_SFICH_REPORTING", line 1299 ORA-06512: at line 1

Solution

The l_xml.transform doesn't work in later versions of oracle (i.e. 12c) unless you get a patch from Oracle.

The work around is below. You can either put this as an execute immediate statement in PL/SQL or in SQL near to where the error occurs.

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET EVENTS='31151 trace name context forever, level 0x40000';

thanks Shaun