Hi,
I have to call the store procedure which is returning the response of huge array of json object. When the data was low its working fine. But it seems due to the huge data store procedure is returning below error while calling it using groovy script:
executeScript[id=2c703968-acea-1a72-8299-09548a89bbec] Error during JSON operations: java.lang.reflect.UndeclaredThrowableException
- Caused by: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1
- Caused by: Error : 6502, Position : 0, Sql = BEGIN MY_DEMO_SP(:1 ); END;, OriginalSql = {call MY_DEMO_SP(?)}, Error Msg = ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1
09:05:14 ISTERROR2c703968-acea-1a72-8299-09548a89bbec
ExecuteScript[id=2c703968-acea-1a72-8299-09548a89bbec] Error during JSON operations: java.lang.reflect.UndeclaredThrowableException
- Caused by: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1
- Caused by: Error : 6502, Position : 0, Sql = BEGIN MY_DEMO_SP(:1 ); END;, OriginalSql = {call MY_DEMO_SP(?)}, Error Msg = ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1
I am using the below script:
import org.apache.commons.io.IOUtils
import org.apache.nifi.controller.ControllerService
import org.apache.nifi.processor.io.StreamCallback
import java.nio.charset.*
import groovy.sql.OutParameter
import groovy.sql.Sql
import java.sql.ResultSet
import java.sql.Clob
import java.nio.charset.StandardCharsets
import groovy.json.*
def flowFile = session.get()
if(!flowFile) return
def lookup = context.controllerServiceLookup
def dbServiceName = ConncationPool.value
def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find {
cs -> lookup.getControllerServiceName(cs) == dbServiceName
}
try {
def slurper = new groovy.json.JsonSlurper()
flowFile = session.write(flowFile,
{ inputStream, outputStream ->
def conn = lookup.getControllerService(dbcpServiceId).getConnection();
sql = Sql.newInstance(conn);
String sqlString ="""{call MY_DEMO_SP(?)}""";
def out_fg_data
def parametersList = [Sql.VARCHAR];
sql.call(sqlString, parametersList) {out_json_response ->
out_fg_data = out_json_response
};
def json = JsonOutput.toJson(out_fg_data)
def parser = new JsonSlurper()
outputStream.write(parser.parseText(json).getBytes(StandardCharsets.UTF_8))
} as StreamCallback)
session.transfer(flowFile, REL_SUCCESS)
} catch(Exception e) {
log.error('Error during JSON operations', e)
session.transfer(flowFile, REL_FAILURE)
}
Can you please help what's wrong I am doing here.
