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