Solved

Need help to pass XMLTYPE from Java to Stored Procedure

Posted on 2004-09-09
4
2,839 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

615 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