Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help to pass XMLTYPE from Java to Stored Procedure

Posted on 2004-09-09
4
Medium Priority
?
2,904 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 1200 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 800 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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 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‚Ķ
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

715 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