CODEWITHSUNDEEP
spring-bootjdbctemplate
Gourav Malhotra
Gourav Malhotra

Reputation: 51

Issues while calling Oracle stored procedure with CLOB type input parameter from java JDBCTemplate

I am using JDBCtemplate feature in Sring boot and trying to call oracle stored procedure having CLOB type input parameter. I have used LobHandler to convert input string to Clob, but I am getting error as "Caused by: java.sql.SQLException: ORA-22922: nonexistent LOB value"

Below is the sample code:

@Override
    public Map<String, Object> getNewRequestFids(String cisarResponse){
        Map<String, Object> resultMap = new HashMap<String,Object>();
        LobHandler lobHandler = new DefaultLobHandler();

        SimpleJdbcCall executor = new SimpleJdbcCall(jdbcTemplate).withProcedureName("CA_FID.GETREQUESTFIDS")
                .withoutProcedureColumnMetaDataAccess();
        executor.addDeclaredParameter(new SqlParameter("P_FIDLIST", Types.CLOB));
        executor.addDeclaredParameter(new SqlOutParameter("P_RESULT", OracleTypes.CURSOR, new FidExtractor()));

        executor.compile();
        SqlParameterSource params = new MapSqlParameterSource().addValue("P_FIDLIST", new SqlLobValue(cisarResponse, lobHandler));

        resultMap = executor.execute(params);
        System.out.println("The resultMap is: " + resultMap);

return resultMap;
}


Input to this function is like "finalJsonString.toString" where finalJsonString is "{"items":[{"id":7849081,"username":"marcinTest","description":"TEST DESCRIPTION","privileged":true,"critical":true,"system":{"id":4648,"systemName":"CBDEMLA1","description":"WPB - DEV","environment":"Production","type":"MIDRANGE","platform":null,"platformDetails":null,"sourceSystemId":null,"hostName":"CBDEMLA1","databaseAttributes":null},"owner":{"id":1010132677,"firstName":"MICHAL","lastName":"KOSTRZEWSKI","soeId":"MK32677"}}]}"

Upvotes: 5

Views: 2158

Answers (1)

nokieng
nokieng

Reputation: 2126

I am following your code using SqlLobValue. and I solved my problem inserting Clob data into Oracle 11G database.
My error was that ORA-22922: nonexistent LOB value


jdbcTemplate.setResultsMapCaseInsensitive(true);
simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withCatalogName(FIDProcedures.PKG_FID);
simpleJdbcCall.withProcedureName(FIDProcedures.FID_LOG_UPDATE).declareParameters(
        new SqlParameter("P_LOG_ID", Types.NUMERIC), new SqlParameter("P_REQUEST", Types.CLOB),
        new SqlParameter("P_RESPONSE", Types.CLOB), new SqlParameter("P_ERROR_EXCEPTION", Types.CLOB),
        new SqlParameter("P_ENGINE_ELAPSE", Types.VARCHAR),
        new SqlParameter("P_STATUS", Types.VARCHAR));

Map<String, Object> paramaters = new HashMap<String, Object>();
paramaters.put("P_LOG_ID", logId);
paramaters.put("P_REQUEST", new SqlLobValue(request));
paramaters.put("P_RESPONSE", new SqlLobValue(response));
paramaters.put("P_ERROR_EXCEPTION", new SqlLobValue(error));
paramaters.put("P_ENGINE_ELAPSE", String.valueOf(elapse));
paramaters.put("P_STATUS", status);

simpleJdbcCall.execute(paramaters);

I hope the code above could help!.

Upvotes: 1

Related Questions