CODEWITHSUNDEEP
javaoracle-databasestored-proceduresjdbcclob
TAugusti
TAugusti

Reputation: 71

How to pass an array of Java strings as CLOBS to an oracle stored procedure that takes an array of CLOBS

I have an Oracle stored procedure that takes an array of clobs that need to be invoked from Java using JDBC. I have the data as a Set in my java code. Tried several different approaches and nothing seems to work. Anyone has sample code to do this, please post.

Developer of the stored procedure has defined a custom data type called "CLOB_ARRAY" which is a TABLE of CLOBS. When it is an array of VARCHAR it works fine.

Upvotes: 1

Views: 2485

Answers (1)

TAugusti
TAugusti

Reputation: 71

I found a work around using Oracle Type and Struct. Below is a summary of the solution. STEP 1: Create a type as below - this has to be done at the database level using SQL Developer or SQL Plus. Not within the package

create or replace TYPE TYPE_DTAP_RECORD_STORE AS OBJECT( DATA_STORE_ID VARCHAR2(300), INDEX_RECORD CLOB);

STEP 2 : In the package define an array of the above type TYPE RECORD_ARRAY IS TABLE OF TYPE_DTAP_RECORD_STORE INDEX BY BINARY_INTEGER

STEP 3 : Create the stored procedure as below procedure baseline_record_insert_bulk (i_record in record_array);

STEP 4:

In Java, write a DAO method as below to call the stored procedure

public void bulkAddToRecordStore(Map<String,String> jsonArray) throws SQLException {

    List<Object>recordList = new ArrayList<>(); 
    OracleConnection oraConnection = getConnection();
    OracleCallableStatement cs =(OracleCallableStatement) getConnection().prepareCall("BEGIN SCHEMA.PACKAGE.baseline_record_insert_bulk(?, ?); END;")
    for(String key :jsonArray.keySet()){
            Clob clob=oraConnection.createClob();
            clob.setString(1, jsonArray.get(key));
            Struct rec=oraConnection.createStruct("SCHEMA.TYPE_DTAP_RECORD_STORE", new Object[]{key,clob});
            recordList.add(rec);
        }
        Array sqlArray = oraConnection.createOracleArray("SCHEMA.PACKAGE.RECORD_ARRAY", recordList.toArray());
        cs.setObject(1, sqlArray);
        cs.execute();
        sqlArray.free();
        cs.close();


}

Upvotes: 1

Related Questions