Solved

Need help to pass XMLTYPE from Java to Stored Procedure

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle -- identify blocking session 24 52
Oracle - Stored Procedure Privilge access 3 53
Concat multi row values of a field in oracle 6 63
PL/SQL Two changes 7 27
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

776 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