asked on

Need help to pass XMLTYPE from Java to Stored Procedure

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

geotiger

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

SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

grim_toaster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

john1953

ASKER

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();