CODEWITHSUNDEEP
javaoracle-database
Arpan Paliwal
Arpan Paliwal

Reputation: 264

How to convert Java String to Oracle Clob in stored procedure

I have a procedure as below 

create or replace PROCEDURE  PROCESS_MESSAGE(
    MESSAGE_ENTITY IN T_MESSAGE_ENTITY,
    STATUS OUT VARCHAR2
.
.

where T_MESSAGE_ENTITY is the type which has some fields as CLOB

create or replace TYPE T_MESSAGE_ENTITY FORCE AS OBJECT (
COLS CLOB,
VALS CLOB
.
.
)

I have to pass T_MESSAGE_ENTITY to the procedure as the input parameter with these CLOB value I tried by using ARRAY.

objMsgEntityArray[0] = colString.getBytes();
objMsgEntityArray[1] = valString.getBytes();

and passing this object using callableStmt.setObject()

it's giving me below error.

SQLException occurred while merging data into Issue table.java.sql.SQLException: Fail to convert to internal representation: [B@3cbbfe22 at oracle.jdbc.oracore.OracleTypeCLOB.toDatum(OracleTypeCLOB.java:71)

Upvotes: 0

Views: 2478

Answers (3)

David Simon
David Simon

Reputation: 11

You don't need to turn off / uncheck the Wrap Data Types . An alternate workaround is to use the following code

    if(obj instanceof weblogic.jdbc.wrapper.Clob) {
      weblogic.jdbc.wrapper.Clob clob = (weblogic.jdbc.wrapper.Clob)st1.getObject(2);
      **Clob oc = (oracle.sql.CLOB)clob.unwrap(Class.forName("oracle.sql.CLOB"));**
    }

Upvotes: 1

Kiran Sankpal
Kiran Sankpal

Reputation: 1

I have resolved this issue by using the below code to create CLOB object -

oracle.jdbc.OracleClob clobCols = (oracle.jdbc.OracleClob) conn.createClob();
clobCols.setString(1, "String value")

The second thing which you need is to uncheck the "Wrap Data Types" property of the data source for which you have acquired the DB connection. Even on production, it is a better idea to uncheck this property as doing that will improve the performance of the application.

By default, data type objects for Array, Blob, Clob, NClob, Ref, SQLXML, and Struct, 
plus ParameterMetaData and ResultSetMetaData objects are wrapped with a WebLogic 
wrapper.Which converts or wrappes Oracle type to Weblogic type which is extra and 
expensive operation so it is recommended to uncheck this property for getting better 
performance.

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

If you are want to use udt (user defined type) in java you have to create mapping class using java.sql.SQLData interface or take advantage of STRUCT class. In my example, i'm going to use the second option

Your type and procedure.

create or replace TYPE T_MESSAGE_ENTITY FORCE AS OBJECT (
  COLS CLOB,
  VALS CLOB    
);

create or replace PROCEDURE  PROCESS_MESSAGE(
    MESSAGE_ENTITY IN T_MESSAGE_ENTITY,
    STATUS OUT VARCHAR2) 
    is 
    begin         
     status := MESSAGE_ENTITY.cols||'-'|| MESSAGE_ENTITY.VALS;        
    end;

Java: 

StructDescriptor structdesc = StructDescriptor.createDescriptor("T_MESSAGE_ENTITY", con);
        Clob clob1 = con.createClob();
        clob1.setString(1,"First paramter");
        Clob clob2 = con.createClob();
        clob2.setString(1,"Secound paramter");
        Object[] attributes = {clob1,clob2};
        STRUCT struct = new STRUCT(structdesc, con, attributes);        
        String CALL_PROC = "{call PROCESS_MESSAGE(?,?)}";
        CallableStatement  callableStatement = con.prepareCall(CALL_PROC);
        callableStatement.setObject(1, struct, Types.STRUCT);
        callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
        callableStatement.executeUpdate();
        System.err.println(callableStatement.getString(2));

Upvotes: 1

Related Questions