Unable to use XMLTYPE in Oracle PL/SQL web service

I have been trying to provide my Oracle PL/SQL package as a web service in Oracle 11g (11.2.0.2.0). While everything else is going well, I seem to have a problem in using any procedure/function that uses XMLTYPE over the web service. While I would be very surprised, if XMLTYPE was not supported in Oracle web service, and also because I haven't found any resources that indicated such limitation, I believe I'm doing something wrong. However, I cannot figure out where the problem would be and therefore the question.

I have configured a native web service in my Oracle database according to Native Oracle XML DB Web Services in Oracle 11g Release 1 and I have been successful in testing procedures with basic datatypes using SoapUI.

To rule out any other sources for the problem I created the following minimized test package that reproduces the problem:

CREATE OR REPLACE PACKAGE web_test_package AUTHID CURRENT_USER AS
        PROCEDURE xmltype_test (dummy IN varchar2);
END;
/

CREATE OR REPLACE PACKAGE BODY web_test_package AS
        PROCEDURE xmltype_test (dummy IN varchar2)
        IS
                xt XmlType;
        BEGIN
                select XMLTYPE('<MyXmlTag></MyXmlTag>') into xt from dual;
        END xmltype_test;
END;
/

If I now run the defined procedure in SQLPlus, it runs nicely:

SQL> exec web_test_package.xmltype_test;

PL/SQL procedure successfully completed.

When the service is configured and the package has been defined into the database, I can get the WSDL for the package from http://host:port/orawsv/DBSCHEMA/WEB_TEST_PACKAGE?wsdl. Using the WSDL I create a SoapUI project, which contains a sample request. Then I configure the request to use Basic Authentication, and I'm ready to test the query.

When running the query with SoapUI, I get the following errors in the response:

               <OracleError>
                  <ErrorNumber>ORA-19202</ErrorNumber>
                  <Message>Error occurred in XML processing</Message>
               </OracleError>
               <OracleError>
                  <ErrorNumber>ORA-00904</ErrorNumber>
                  <Message>: invalid identifier</Message>
               </OracleError>
               <OracleError>
                  <ErrorNumber>ORA-06512</ErrorNumber>
                  <Message>at "MY_USER.WEB_TEST_PACKAGE", line 6</Message>
               </OracleError>
               <OracleError>
                  <ErrorNumber>ORA-06512</ErrorNumber>
                  <Message>at line 1</Message>
               </OracleError>

If I now interpret the errors correctly, they mean that I get the error ORA-00904 "invalid identifier" on the line where I have my select-clause. And in my understanding "XMLTYPE" is the only identifier on that line, which means that it is not recognized when the procedure is executed through the web service.

So finally the question: Is there a limitation that XMLTYPE cannot be used in procedures that are called through web service? Or am I missing something here?

For the record, I have also tried to replace the constructor format XMLTYPE(...) with method format XMLTYPE.CreateXML(...), but that didn't help.

Solution

I was finally able to solve my issue. The solution consisted of two parts:

Firstly, I needed to give my web service user the following permission:

grant XDB_WEBSERVICES_WITH_PUBLIC to my_user;

This was in comments in some guides that I used, but apparently it is required for the XMLTYPE to be visible for a session launched by the web service.

Secondly I learned that having XMLTYPE as a return type of a function is a problem, when the function is called by a web service. I had one function like that and the solution was to change its return type to CLOB and add the following conversion in the end of it.

RETURN return_xml.getClobVal();

This returns the return value nicely as XML to my SOAPUI request.

Edit. There seems to be a limit of 4000 chars in returning CLOB from Oracle native web service. This can be awkwardly resolved by casting it back to XMLTYPE. So to correctly return XML from Oracle native web service, you will actually need to do the following:

RETURN XMLTYPE(return_xml.getClobVal());

Even though looking odd, this back and forth casting changes the format of the XMLTYPE enough so that it can be returned nicely and without character limitation.