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

Inserting XML into Oracle Database failure

 Forum

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"?>
    <catalog>

          <book id="bk101">

                <author>Gambardella, Matthew</author>

                <title>XML Developers Guide</title>
       <AnotherBlank></AnotherBlank>
                <genre>Computer</genre>

                <price>6</price>

                <publish_date>2000-10-01</publish_date>
       <test_blank></test_blank>
                <description>An in-depth look at creating applications with XML.</description>

          </book>

    </catalog>

    Here is my stored procedure:

    create or replace PROCEDURE loadxmlData (xmlFile CLOB)
    IS

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

    --Handle the exceptions
    EXCEPTION
      WHEN OTHERS THEN
        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

    Thanks

    David..............

  • 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
            Computer
            6
            2000-10-01
            An in-depth look at creating applications with XML.
       

    I connect no problem to Oracle in the SQL Connections:

    JDBC Driver:  com.sonicsw.jdbc.oracle.OracleDriver

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

    Error Message:Service call failed.
    Step Name:Use InsertXMLIntoOracle.esbdbService Type:ny.gcc.oracle.database.Service
    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(DatabaseService.java:737)
         at com.sonicsw.esb.service.rdbms.DatabaseService.service(DatabaseService.java:704)
         at com.sonicsw.xqimpl.service.debug.DebugServiceInterceptor.intercept(DebugServiceInterceptor.java:118)
         at com.sonicsw.xqimpl.service.XQServiceChain$XQInterceptorServiceWrapper.intercept(XQServiceChain.java:481)
         at com.sonicsw.xqimpl.service.XQServiceChain$XQInterceptorServiceWrapper.service(XQServiceChain.java:470)
         at com.sonicsw.xqimpl.service.XQServiceChain.service(XQServiceChain.java:151)
         at com.sonicsw.xqimpl.service.ServiceMessageHandler.callService(ServiceMessageHandler.java:413)
         at com.sonicsw.xqimpl.service.ServiceMessageHandler.handleMessage(ServiceMessageHandler.java:182)
         at com.sonicsw.xqimpl.service.ProcessMessageHandler.doHandleMessage(ProcessMessageHandler.java:308)
         at com.sonicsw.xqimpl.service.ProcessMessageHandler.handleMessage(ProcessMessageHandler.java:90)
         at com.sonicsw.xqimpl.service.XQDispatcher.onMessage(XQDispatcher.java:422)
         at com.sonicsw.xqimpl.service.MessageSendingHelper.sendToService(MessageSendingHelper.java:328)
         at com.sonicsw.xqimpl.service.MessageSendingHelper.sendToAddress(MessageSendingHelper.java:76)
         at com.sonicsw.xqimpl.service.MessageSendingHelper.sendEnvelopes(MessageSendingHelper.java:951)
         at com.sonicsw.xqimpl.service.XQDispatcher.onMessage(XQDispatcher.java:493)
         at com.sonicsw.xqimpl.endpoint.container.EndpointContextContainer.onMessage(EndpointContextContainer.java:84)
         at com.sonicsw.xq.connector.jms.JMSEndpoint$JMSEndpointListener.onMessage(JMSEndpoint.java:570)
         at progress.message.jimpl.Session.deliver(Session.java:2998)
         at progress.message.jimpl.Session.run(Session.java:2390)
         at progress.message.jimpl.Session$SessionThread.run(Session.java:2775)
    Caused by: java.sql.SQLException: No suitable driver
         at java.sql.DriverManager.getConnection(DriverManager.java:559)
         at java.sql.DriverManager.getConnection(DriverManager.java:189)
         at org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS.getPooledConnection(DriverAdapterCPDS.java:162)
         at org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS.getPooledConnection(DriverAdapterCPDS.java:132)
         at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.testCPDS(InstanceKeyDataSource.java:711)
         at org.apache.commons.dbcp.datasources.SharedPoolDataSource.registerPool(SharedPoolDataSource.java:188)
         at org.apache.commons.dbcp.datasources.SharedPoolDataSource.getPooledConnectionAndInfo(SharedPoolDataSource.java:157)
         at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:631)
         at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:615)
         at com.sonicsw.esb.service.rdbms.connections.DBConnectionPools.getConnectionFromPool(DBConnectionPools.java:293)
         at com.sonicsw.esb.service.rdbms.DatabaseService.getConnectionFromPool(DatabaseService.java:729)
         ... 19 more

    Thanks

    David...........

  • Fixed issue was with the JDBC..

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

    jdbc:sonic:oracle://

    Once this change was made all things work as expected.