Hello,
I have a program which is written PL/SQL. This program is creating excell
file by using hssfworkbook, and write data to this excell file. I'm using
oracle ias version 10.1.2.0.2 Forms and Reports application server. JVM heap
size of the server is 1024().The server using JDK version 1.4.2. The server
works on Redhat linux version 4. In the program I can only write 12000
records to the excell file. After 12000 records I get out of memory errors.
My program is in below.
Can anyone help me to solve this problem.
Thank you
Here is the program :
BEGIN
go_block(pCurrentBlock);
dFileName:='EXPORT'||TO_CHAR(sysdate,'miss')||'.XLS';
dworkbook:=hssfworkbook.new;
for i in 1 .. 1500 loop
first_record;
dCurrentItem := Get_Block_Property(pCurrentBlock, FIRST_ITEM);
while not (name_in('system.last_record')='TRUE') loop
begin
/* The data would be written to the excell file column order. */
if (dRow=0) then
/* Create a new sheet
*/
dCurrentItem :=
Get_Block_Property(pCurrentBlock, FIRST_ITEM);
dWorkSheetName:='sheet'||dWorksheetNumber;
dworksheet:=hssfworkbook.createSheet(dworkbook,dWorkSheetName);
dWorkCellStyle:=hssfworkbook.createcellstyle(dworkbook);
hssfcellstyle.setAlignment(dWorkCellStyle,hssfcellstyle.ALIGN_CENTER);
/* Set hearder font */
dHeaderFont:=
hssfworkbook.createFont(dworkbook);
hssffont.setFontHeightInPoints(dHeaderFont,12);
hssffont.setFontName(dHeaderFont,'Courier New');
hssffont.setBoldweight(dHeaderFont,hssffont.BOLDWEIGHT_BOLD);
hssffont.setColor(dHeaderFont,10);
hssfcellstyle.setFont(dWorkCellStyle,dHeaderFont);
/*Create a row for
title */
dWorkRow:=hssfsheet.createRow(dworksheet,0);
isLast:=false;
/* Write the title of
the excell file */
while not isLast loop
dWorkCell:=hssfRow.createCell(dWorkRow,dColumn);
hssfCell.setCellValue(dWorkCell,nvl(BUNDLE_AL(dCurrentItem),Get_Item_Property(dCurrentItem,ITEM_NAME)));
hssfCell.setCellStyle(dWorkCell,dWorkCellStyle);
dCurrentItem := Get_ITEM_Property(dCurrentItem, NEXTITEM);
if
dCurrentItem is null then
isLast:=true;
elsif
dCurrentItem is not null and
Get_ITEM_Property(dCurrentItem, enabled)='TRUE' then
go_item(dCurrentItem);
dColumn:=dColumn+1;
end if;
end loop;
dColumn:=0;
elsif (dRow <= dMaxWorksheetNum) then
/* Data of the report are written here. The
data are written in column
order */
isLast :=false;
dWorkRow:=hssfsheet.createRow(dworksheet,dRow);
dCurrentItem :=
Get_Block_Property(pCurrentBlock, FIRST_ITEM);
while not isLast loop
if
name_in('system.last_record')='TRUE' then
isLast:=true;
elsif
dCurrentItem is not null and Get_ITEM_Property(dCurrentItem,
enabled)='TRUE' then
go_item(dCurrentItem);
dDataType := Get_Item_Property(dCurrentItem,DataType);
dWorkCell:=hssfRow.createCell(dWorkRow,dColumn);
dColumn:=dColumn+1;
If
Get_Item_Property(dCurrentItem,Database_Value) is not null
and dDataType = 'NUMBER' Then
hssfCell.setCellValue(dWorkCell,to_number(Get_Item_Property(dCurrentItem,Database_Value)));
Elsif
dDataType = 'DATE' Then
hssfCell.setCellValue(dWorkCell,to_date(Get_Item_Property(dCurrentItem,Database_Value)));
Else
hssfCell.setCellValue(dWorkCell,Get_Item_Property(dCurrentItem,Database_Value));
End If;
dCurrentItem := Get_ITEM_Property(dCurrentItem, NEXTITEM);
elsif
dCurrentItem is null then
isLast:=true;
end if;
end loop;
isWritten :=true;
dColumn:=0;
end if;
if (dRow > dMaxWorksheetNum) then
dRow:=-1; /* Because of
dRowNum increased by one , set the dRow value
-1 */
dColumn:=0;
dWorksheetNumber:=dWorksheetNumber+1;
isWritten :=false;
end if; /* End of if (dRow=1) */
dRow:=dRow+1;
if (isWritten) and not
name_in('system.last_record')='TRUE'then
/* if not at the end of the record and the
previously read record is
written to the file
, then go to next record */
next_record;
end if;
end;
end loop;
end loop;
workbookwriter.save(dworkbook,global.gethome||dFileName);
web.show_document('/users/'||dFileName,'_BLANK');
EXCEPTION
WHEN ORA_JAVA.EXCEPTION_THROWN THEN
begin
javaException := ORA_JAVA.LAST_EXCEPTION;
-- Print out the Exception by using the toString()
-- Method of the exception Object
javaException2 := Exception_.new(javaException);
mess(27002,Exception_.getMessage(javaException2));
-- and clean up
ORA_JAVA.CLEAR_EXCEPTION;
exception
WHEN ORA_JAVA.JAVA_ERROR THEN
-- In this case, the error would be
-- Argument 1 can not be null
mess(27002,ORA_JAVA.LAST_ERROR);
--Clean up
ORA_JAVA.CLEAR_ERROR;
end;
WHEN ORA_JAVA.JAVA_ERROR THEN
-- In this case, the error would be
-- Argument 1 can not be null
message(ORA_JAVA.LAST_ERROR);
--Clean up
ORA_JAVA.CLEAR_ERROR;
end;
--
View this message in context:
http://www.nabble.com/How-can-I-solve-the-outof-memory-error-on-excell-file-which-is-created-hssfworkbook-tp14412958p14412958.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
