Inserting XML into Oracle Database failure - Forum - Technology Partner - Progress Community

Inserting XML into Oracle Database failure


Inserting XML into Oracle Database failure

  • Hi All,

    I am having great problems with inserting XML data into Oracle database using Sonic ESB database service:

    Here is my XML:

    <?xml version="1.0" encoding="UTF-8"?>

          <book id="bk101">

                <author>Gambardella, Matthew</author>

                <title>XML Developers Guide</title>


                <description>An in-depth look at creating applications with XML.</description>



    Here is my stored procedure:

    create or replace PROCEDURE loadxmlData (xmlFile CLOB)

      -- Insert the XML into an XMLType column
      INSERT INTO testXML (testData) VALUES (XMLType(xmlFile));

    --Handle the exceptions
        raise_application_error(-20101, 'Exception occurred in loadxmlData procedure :'||SQLERRM);
    END loadxmlData;

    Here is my database SQL calling the procedure:

    { call loadxmlData(@xmlData)}

    IN/OUT           Query Parameter          DB Type      Interface Parameter

    IN                   xmlData                       CLOB         xmlData_IN(xsd:any)

    I keep getting an error when trying to insert by calling the stored procedure by Database Service

    I have no problem inserting the XML while working in the PL/SQL Editor on Oracle?

    The error returned is:

    [Progress][Oracle JDBC Driver][Oracle]ORA-20101: Exception occured in loadxmlData procedure:ORA-31011: XML parsing failed

    ORA-19202: Error occurred in XML processing

    LPX-00210: expected '<' instead of '@'

    Error at Line 1

    ORA-06512: at "SYS.XMLTYPE", line 254

    ORA-06512: at line 1

    ORA-06512: at "SYSTEM.LOADXMLDATA", line 12

    ORA-06512: at line 1

    I have looked up the errors and nothing I have tried has worked, I also check the Sonic documentation with no results. Has anyone managed to insert XML into an XMLTYPE oracle column using Sonic Database service?

    I would appreciate any help



  • I think I have been working on this too long something I'm not seeing

    I managed to get some help from a very good friend who suggested this:

    Set transformation to XPath

    Set Transformation Detail to /

    Use Query: INSERT INTO testxml(xmldata) VALUES (@esbGlob)

    This did get me past the first error, but I get the following:

    XML Test File

            Gambardella, Matthew
            XML Developers Guide
            An in-depth look at creating applications with XML.

    I connect no problem to Oracle in the SQL Connections:

    JDBC Driver:

    Connection:  jdbc:progress:oracle://localhost:1521;SID=XE

    Error Message:Service call failed.
    Step Name:Use InsertXMLIntoOracle.esbdbService
    Process Name:OracleProcessingTop Process:OracleProcessing
    Container Name:nygccsandpHost:BANDIT
    Stack Trace:
    com.sonicsw.esb.service.rdbms.DatabaseServiceException: Service call failed.
         at com.sonicsw.esb.service.rdbms.DatabaseService.getConnectionFromPool(
         at com.sonicsw.esb.service.rdbms.DatabaseService.service(
         at com.sonicsw.xqimpl.service.debug.DebugServiceInterceptor.intercept(
         at com.sonicsw.xqimpl.service.XQServiceChain$XQInterceptorServiceWrapper.intercept(
         at com.sonicsw.xqimpl.service.XQServiceChain$XQInterceptorServiceWrapper.service(
         at com.sonicsw.xqimpl.service.XQServiceChain.service(
         at com.sonicsw.xqimpl.service.ServiceMessageHandler.callService(
         at com.sonicsw.xqimpl.service.ServiceMessageHandler.handleMessage(
         at com.sonicsw.xqimpl.service.ProcessMessageHandler.doHandleMessage(
         at com.sonicsw.xqimpl.service.ProcessMessageHandler.handleMessage(
         at com.sonicsw.xqimpl.service.XQDispatcher.onMessage(
         at com.sonicsw.xqimpl.service.MessageSendingHelper.sendToService(
         at com.sonicsw.xqimpl.service.MessageSendingHelper.sendToAddress(
         at com.sonicsw.xqimpl.service.MessageSendingHelper.sendEnvelopes(
         at com.sonicsw.xqimpl.service.XQDispatcher.onMessage(
         at com.sonicsw.xqimpl.endpoint.container.EndpointContextContainer.onMessage(
         at com.sonicsw.xq.connector.jms.JMSEndpoint$JMSEndpointListener.onMessage(
         at progress.message.jimpl.Session.deliver(
         at progress.message.jimpl.Session$
    Caused by: java.sql.SQLException: No suitable driver
         at java.sql.DriverManager.getConnection(
         at java.sql.DriverManager.getConnection(
         at org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS.getPooledConnection(
         at org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS.getPooledConnection(
         at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.testCPDS(
         at org.apache.commons.dbcp.datasources.SharedPoolDataSource.registerPool(
         at org.apache.commons.dbcp.datasources.SharedPoolDataSource.getPooledConnectionAndInfo(
         at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection(
         at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection(
         at com.sonicsw.esb.service.rdbms.connections.DBConnectionPools.getConnectionFromPool(
         at com.sonicsw.esb.service.rdbms.DatabaseService.getConnectionFromPool(
         ... 19 more



  • Fixed issue was with the JDBC..

  • The jdbc setting in the DB service is set to :


    Once this change was made all things work as expected.