ORA-22922: nonexistent LOB value while passing CLOB as part of a UDT Array

Question

I am trying to pass an User defined type (UDT) as an input parameter to an Oracle Stored Procedure

The UDT array - FILTER_EXPR_TBL:

CREATE OR REPLACE
TYPE            schema.FILTER_EXPR_TBL AS TABLE OF schema.FILTER_EXPR_T

The UDT member - FILTER_EXPR_T :

CREATE OR REPLACE
TYPE            schema.FILTER_EXPR_T AS OBJECT  (
                                filter_name        varchar2(50 CHAR),
                                Comparison_opr     varchar2(50 CHAR),
                                Search_value       clob,
                                logical_opr        varchar2(30 CHAR)
                                )

My Java code to prepare input for this UDT part:

// Preparing filter_expr_t and adding it to an array
            StructDescriptor StructDesc_Filterexpr = StructDescriptor.createDescriptor("schema.FILTER_EXPR_T", con);
            Object[] ObjArray = new Object[4];
            ObjArray[0] = "val1";
            ObjArray[1] = "=";
            oracle.jdbc.OracleClob clob =  (oracle.jdbc.OracleClob) con.createClob();
            clob.setString(1,"val2");
            ObjArray[2] = clob;
            ObjArray[3] = "";
            STRUCT filter_expr = new STRUCT(StructDesc_Filterexpr, con, ObjArray);
            STRUCT[] filter_expr_tbl = {filter_expr};
            //Declaring filter_expr_tbl
            ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "schema.FILTER_EXPR_TBL", con);
            ARRAY array_to_pass = new ARRAY( descriptor, con, filter_expr_tbl);

where 'con' is the OracleConnection object

The part where I add it to the callable statement :

   stmnt.setArray(1, array_to_pass);

Closing this question as I found the solution.

score 0 · Answer 1 · answered Oct 08 '18 at 19:14

Solution : We need to create the clob using the same connection that is used to create the callable statement

In this case the clob was created using con object. But the callable statement was created without using con object

The following

OracleConnection con =  (emeaJdbcTemplate.getDataSource().getConnection()).unwrap(OracleConnection.class);

OracleCallableStatement stmnt = (OracleCallableStatement) emeaJdbcTemplate.getDataSource().getConnection().prepareCall("{call pkg.proc(?,?,?,?,?,?,?,?)}");

Should be

OracleConnection con =  (emeaJdbcTemplate.getDataSource().getConnection()).unwrap(OracleConnection.class);

OracleCallableStatement stmnt = (OracleCallableStatement) con.prepareCall("{call pkg.proc(?,?,?,?,?,?,?,?)}");

ORA-22922: nonexistent LOB value while passing CLOB as part of a UDT Array

1 Answers1