Integration

Hi all,

Our Environment
===============
OS - Windows XP Service Pack 3
Oracle Developer Suite - 10.1.2.3.0
Oracle Forms & Reports Service 10.1.2.3.0
Oracle Database 10.2.0.1.0
JDK 1.5
Jinitiator 1.3.1.30
Apache POI 3.5

From forms we are writing to excel files after copying XL template using Apache POI 3.5 and JDK 1.5. This XL template file has got lot of macros.

We have imported the Java class files into form as pl/sql library. We are able to write upto 7Mb size of XL file. Beyond that size it comes with the error Ora-105101.
We tried to increase the JVM Heap Size to 640M by setting values -Xmx640M everywhere in OC4J_BI_FORMS/Server Properties/Java Options, Home/Server Properties/Java Options through Enterprise Manager console. Also manually set the values in OPMN.XML and reloaded the same. Also set -Xmx640M in Jinitiator 1.3.1.30 Java Runtime Parameters. Also set in Java console. All settings have no effect.

We have written a small program to display the run time memory from forms, which displays only maximum memory 63M all the time.

PACKAGE BODY HeapSize IS

--
-- DO NOT EDIT THIS FILE - it is machine generated!
--

args JNI.ARGLIST;

-- Constructor for signature ()V
FUNCTION new RETURN ORA_JAVA.JOBJECT IS
BEGIN
args := NULL;
RETURN (JNI.NEW_OBJECT('HeapSize', '()V', args));
END;

-- Method: getTotalMemory ()D
FUNCTION getTotalMemory(
obj ORA_JAVA.JOBJECT) RETURN NUMBER IS
BEGIN
args := NULL;
RETURN JNI.CALL_DOUBLE_METHOD(FALSE, obj, 'HeapSize', 'getTotalMemory', '()D', args);
END;

-- Method: getMaxMemory ()D
FUNCTION getMaxMemory(
obj ORA_JAVA.JOBJECT) RETURN NUMBER IS
BEGIN
args := NULL;
RETURN JNI.CALL_DOUBLE_METHOD(FALSE, obj, 'HeapSize', 'getMaxMemory', '()D', args);
END;


BEGIN
NULL;
END;



declare
obj ORA_JAVA.JOBJECT;
BEGIN
obj:=HeapSize.new;
message('Total memory '||HeapSize.getTotalMemory(obj));
message('Max memory '||HeapSize.getMaxMemory(obj));

END;




Below procedure is for writing to Excel file.
============================================


PACKAGE BODY UWWriteExcel IS

--
-- DO NOT EDIT THIS FILE - it is machine generated!
--

args JNI.ARGLIST;

-- Constructor for signature ()V
FUNCTION new RETURN ORA_JAVA.JOBJECT IS
BEGIN
args := NULL;
RETURN (JNI.NEW_OBJECT('UWWriteExcel', '()V', args));
END;

-- Method: copyExcel (Ljava/lang/String;Ljava/lang/String;)V
PROCEDURE copyExcel(
obj ORA_JAVA.JOBJECT,
a0 VARCHAR2,
a1 VARCHAR2) IS
BEGIN
args := JNI.CREATE_ARG_LIST(2);
JNI.ADD_STRING_ARG(args, a0);
JNI.ADD_STRING_ARG(args, a1);
JNI.CALL_VOID_METHOD(FALSE, obj, 'UWWriteExcel', 'copyExcel', '(Ljava/lang/String;Ljava/lang/String;)V', args);
END;

-- Method: getSpreadSheetPara (Ljava/lang/String;)V
PROCEDURE getSpreadSheetPara(
obj ORA_JAVA.JOBJECT,
a0 VARCHAR2) IS
BEGIN
args := JNI.CREATE_ARG_LIST(1);
JNI.ADD_STRING_ARG(args, a0);
JNI.CALL_VOID_METHOD(FALSE, obj, 'UWWriteExcel', 'getSpreadSheetPara', '(Ljava/lang/String;)V', args);
END;

-- Method: openSheet (I)V
PROCEDURE openSheet(
obj ORA_JAVA.JOBJECT,
a0 NUMBER) IS
BEGIN
args := JNI.CREATE_ARG_LIST(1);
JNI.ADD_INT_ARG(args, a0);
JNI.CALL_VOID_METHOD(FALSE, obj, 'UWWriteExcel', 'openSheet', '(I)V', args);
END;

-- Method: getCellValues (IID)V
PROCEDURE getCellValues(
obj ORA_JAVA.JOBJECT,
a0 NUMBER,
a1 NUMBER,
a2 NUMBER) IS
BEGIN
args := JNI.CREATE_ARG_LIST(3);
JNI.ADD_INT_ARG(args, a0);
JNI.ADD_INT_ARG(args, a1);
JNI.ADD_DOUBLE_ARG(args, a2);
JNI.CALL_VOID_METHOD(FALSE, obj, 'UWWriteExcel', 'getCellValues', '(IID)V', args);
END;

-- Method: getCellValues (IILjava/lang/String;)V
PROCEDURE getCellValues(
obj ORA_JAVA.JOBJECT,
a0 NUMBER,
a1 NUMBER,
a2 VARCHAR2) IS
BEGIN
args := JNI.CREATE_ARG_LIST(3);
JNI.ADD_INT_ARG(args, a0);
JNI.ADD_INT_ARG(args, a1);
JNI.ADD_STRING_ARG(args, a2);
JNI.CALL_VOID_METHOD(FALSE, obj, 'UWWriteExcel', 'getCellValues', '(IILjava/lang/String;)V', args);
END;

-- Method: exportExcel ()V
PROCEDURE exportExcel(
obj ORA_JAVA.JOBJECT) IS
BEGIN
args := NULL;
JNI.CALL_VOID_METHOD(FALSE, obj, 'UWWriteExcel', 'exportExcel', '()V', args);
END;

-- Method: copy (Ljava/lang/String;Ljava/lang/String;)V
PROCEDURE copy(
a0 VARCHAR2,
a1 VARCHAR2) IS
BEGIN
args := JNI.CREATE_ARG_LIST(2);
JNI.ADD_STRING_ARG(args, a0);
JNI.ADD_STRING_ARG(args, a1);
JNI.CALL_VOID_METHOD(TRUE, NULL, 'UWWriteExcel', 'copy', '(Ljava/lang/String;Ljava/lang/String;)V', args);
END;


BEGIN
NULL;
END;




declare
obj ORA_JAVA.JOBJECT;
BEGIN
message('-1');pause;
obj:=UWWriteExcel.new;
message('0');pause;
UWWriteExcel.copyExcel(obj,'C:\\excel\\CAT2009WS.XLS','C:\\excel\\CAT2009WS.XLS');
message('1');pause;
UWWriteExcel.openSheet(obj,0);
message('2');pause;
UWWriteExcel.getCellValues(obj,6,2,900);
message('3');pause;
UWWriteExcel.getCellValues(obj,7,2,911);
message('4');pause;
UWWriteExcel.exportExcel(obj);
END;


When the size of XL is more than 7Mb, after message(0) it will be display oracle error.

From command prompt if we run the same java class file by passing -Xmx256m parameter we are able to write to big XL file.


Can anyone tell me where I am wrong... Can we increase the JVM Heap Size from forms...