Solved

Need help to pass XMLTYPE from Java to Stored Procedure

Posted on 2004-09-09
4
2,601 Views
Last Modified: 2007-12-19
I have a Java application that is calling a Oracle 9i stored procedure.  The Java application is sending several fields which includes an XMLTYPE field.  When I tried to run the Java application I am receiving an error.  Here are the components:

Oracle Table Structure:

CREATE TABLE PRISM.TESTNODALXML
(
  TNODALXML_PK  NUMBER(10),
  TNODEID       NUMBER(10),
  TDOCTYPE      VARCHAR2(20 BYTE),
  TDOCREF       NUMBER(10),
  TXMLDOC      SYS.XMLTYPE
)
----------------------------------------------------------------------------------------------------------------------------------------
The procedure receiving the data from Java and loading the table is shown:

CREATE OR REPLACE PROCEDURE Sp_Ins_Xmldoc(iNODEID IN NUMBER,  iDOCTYPE IN VARCHAR2,  iDOCREF IN NUMBER,  iXMLDATA IN SYS.XMLTYPE)

AS

V_NODEID NUMBER(10);
V_DOCTYPE VARCHAR2(20);
V_DOCREF NUMBER(10);
V_XMLDATA  SYS.XMLTYPE;

BEGIN

/*  Stored procedure will interface with Prism Java Engine to stored generated */
/*  XML documents                                                                                      */

V_NODEID             := iNODEID;
V_DOCTYPE          := iDOCTYPE;
V_DOCREF            := iDOCREF;
V_XMLDATA          := iXMLDATA;

 
INSERT INTO PRISM.TESTNODALXML
(
 tNODALXML_PK,
 tNODEID,
 tDOCTYPE,
 tDOCREF,
 tXMLDOC  
)
VALUES
(
PRISM.NODALXML_SEQ.NEXTVAL,
V_NODEID,
V_DOCTYPE,
V_DOCREF,
V_XMLDATA
);

END;
/

-----------------------------------------------------------------------------------------------------------------------------
This is the Java application statement that calls the stored procedure passes the variables.  Note; I
tried two different methods and received the same error.

Method 1:
             CallableStatement cs =      connection.prepareCall("{call sp_ins_xmldoc(?,?,?,XMLTYPE(?)}");    
                  // Set the value for the IN parameter

                  cs.setInt(1,1041);
                  cs.setString(2,"RESERVATION");
                  cs.setInt(3,100);
                  cs.setObject(4,clob);        
                  cs.execute();

Method 2:
             CallableStatement cs =      connection.prepareCall("{call sp_ins_xmldoc(?,?,?,?)}");    
                  // Set the value for the IN parameter

                  cs.setInt(1,1041);
                  cs.setString(2,"RESERVATION");
                  cs.setInt(3,100);
                  cs.setObject(4,clob);        
                  cs.execute();
------------------------------------------------------------------------------------------------------------
This is the error I received while executing the Java application:

java.sql.SQLException: ORA-24805: LOB type mismatch

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

ORA-06512: at line 1
0
Comment
Question by:john1953
4 Comments
 
LVL 12

Expert Comment

by:geotiger
ID: 12021088

ORA-24805: LOB type mismatch
Cause: When copying or appending LOB locators, both source and destination LOB locators should be of the same type.

Action: Pass the same type of LOB locators for copying or appending
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 300 total points
ID: 12021242
Here Method 1:
            CallableStatement cs =      connection.prepareCall("{call sp_ins_xmldoc(?,?,?,XMLTYPE(?)}");    
                 // Set the value for the IN parameter

                 cs.setInt(1,1041);
                 cs.setString(2,"RESERVATION");
                 cs.setInt(3,100);
                 cs.setObject(4,clob);        
                  cs.execute();



is XMLTYPE() a java function to convert CLOB to XML data type?
if not, you might need to convert it first before you call cs.setObject(4, clob);
                                                                                                          *****
                                                                                                          make sure you convert this clob (if it's still a clob type) to XMLTYPE.
0
 
LVL 7

Accepted Solution

by:
grim_toaster earned 200 total points
ID: 12027545
XMLTYPE is the Oracle function to create the xml type.

You have one problem with the following line, you are missing a ")"
CallableStatement cs =      connection.prepareCall("{call sp_ins_xmldoc(?,?,?,XMLTYPE(?)}");    
It should instead be:
CallableStatement cs =      connection.prepareCall("{call sp_ins_xmldoc(?,?,?,XMLTYPE(?))}");

But that wouldn't cause the exception that you are indicating, so instead I have another question for you, how are you creating your clob variable, and is it actually a clob, or a string?

As an additional point, it may be easier if you simply pass a string or a clob to the stored procedure, and have the PL/SQL procedure body code do the conversion to the XMLType instead of trying to do it in the java code.
0
 

Author Comment

by:john1953
ID: 12044981
seazodiac and grim_toaster -

Both of you have been very helpful.  I made modifications to the code and it works!  I am pasting the complete source code for future reference.  I am going to split the points among both of you.
------------------------------------------------------------------------------------------------------------------------------------
Stored Procedure Code -

CREATE OR REPLACE PROCEDURE SP_INS_XMLDOC(iNODEID IN NUMBER,  iDOCTYPE IN VARCHAR2,  iDOCREF IN NUMBER,  iXMLDATA IN CLOB)

AS

V_NODEID NUMBER(10);
V_DOCTYPE VARCHAR2(20);
V_DOCREF NUMBER(10);
V_XMLDATA  CLOB;

BEGIN

/*  Stored procedure will interface with Prism Java Engine to stored generated */
/*  XML documents                                                                                      */

V_NODEID             := iNODEID;
V_DOCTYPE          := iDOCTYPE;
V_DOCREF            := iDOCREF;
V_XMLDATA          := iXMLDATA;

 
INSERT INTO PRISM.NODALXML
(
 NODALXML_PK,
 NODEID,
 DOCTYPE,
 DOCREF,
 XMLDOC  
)
VALUES
(
PRISM.NODALXML_SEQ.NEXTVAL,
V_NODEID,
V_DOCTYPE,
V_DOCREF,
XMLTYPE(V_XMLDATA)
);

END;
/
---------------------------------------------------------------------------------------------------------------------------------------
Table Code -

CREATE TABLE PRISM.NODALXML
(
  NODALXML_PK  NUMBER(10),
  NODEID       NUMBER(10),
  DOCTYPE      VARCHAR2(20 BYTE),
  DOCREF       NUMBER(10),
  XMLDOC       SYS.XMLTYPE
)
--------------------------------------------------------------------------------------------------------------------------------------
Java code -

CallableStatement cs =      connection.prepareCall("{call sp_ins_xmldoc(?,?,?,?)}");    

  // Set the value for the IN parameter
                  cs.setInt(1,1041);
                  cs.setString(2,"RESERVATION");
                  cs.setInt(3,100);
                  cs.setObject(4,clob);        
                  cs.execute();


0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now