Solved

Need help to pass XMLTYPE from Java to Stored Procedure

Posted on 2004-09-09
4
2,684 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query question 8 92
capture vmstat info and insert it into an oracle table 31 35
date show only hh:mm 2 27
Error in creating a view. 8 23
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

838 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