When Passing clob size more than 3 MB to java stored procedure I get java.lang.OutOfMemoryError.
The data is in the JSON format which we try to deserialize into an object using a java json library in the java stored procedure.
We have tried the following to resolve the error, but didnt work.
--> Increased Memory_Target to 5 GB when using Automatic Memory Management.
--> Using Manual Memory Management increased java pool size and shared pool size to 4 GB by altering SYSTEM table.
--> Increased JAVA_MAX_SESSIONSPACE_SIZE to 4 GB.
--> Added JavaOptions to $OracleHome\javavm\jahome\Settings.properties file java.options = -Xmx4G -Xms4G
Code Snippets://Call from Java to PL/SQL function
connection connection = getConnection();// gives connection object from driverManager
OracleCallableStatement callableStatement = (OracleCallableStatement) connection.prepareCall("{? = call CREATE_EMPLOYEE(?)}");
callableStatement.setString(2, requestString);
callableStatement.registerOutParameter(1, java.sql.Types.CLOB);
callableStatement.execute();
//PL/SQL stored function
create or replace FUNCTION CREATE_EMPLOYEE(
INPUT_MODEL CLOB
)
RETURN CLOB
AS LANGUAGE JAVA
NAME 'CreateEmployee.execute(oracle.sql.CLOB) return oracle.sql.CLOB';
Also We tried to pass HashMap directly to java stored procedure using oracle.jpub.reflect.Client.invoke API but gives numeric or value error If we increase size of the Map.
ORA-06502: PL/SQL: numeric or value error
Code for Client.invoke API call is as follows
Connnection conn = getConnection(); // gives connection object from driverManager
HashMap<String, Object> requestMap = new HashMap<String, Object>();
requestMap.put("key", stringValue);
oracle.jpub.reflect.Client.invoke(conn, null, "CreateEmployee", "execute", new Class[] { HashMap.class }, new Object[] { requestMap });
There is a bug related to ORA-29532 OutOfMemoryError exceptions. This affects versions up to and including 11.2.0.1.
I don't know if this is your issue, but it's worth checking.
There's a patch available. See MOS note 9700101.8.