Inserting XML into Oracle Database failure

Posted by Admin on 26-Jul-2009 15:39

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>

<author>Gambardella, Matthew</author>

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

            <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..............

Technology Partner - Forum

All Replies

Posted by Admin on 27-Jul-2009 18:12

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.esbdb	Service Type:	ny.gcc.oracle.database.Service
Process Name:	OracleProcessing	Top Process:	OracleProcessing
Container Name:	nygccsandp	Host:	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...........

Posted by Admin on 15-Aug-2009 23:49

Fixed issue was with the JDBC..

Posted by Admin on 19-Aug-2009 05:28

The jdbc setting in the DB service is set to :

jdbc:sonic:oracle://

Once this change was made all things work as expected.

This thread is closed