You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by peri <er...@hotmail.com> on 2007/12/19 09:29:36 UTC
How can I solve the outof memory error on excell file which is
created hssfworkbook
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How can I solve the outof memory error on excell file which is
created hssfworkbook
Posted by peri <er...@hotmail.com>.
I would like to explain my problem properly. This problem is a report which
is using for banking operation. So The user wants to take the report in
excell file. So I use hssfworkbook object for creating excell file in
PL/SQL. I should create excell file. If there is an alternate way to create
excell file in Pl/SQL which has less memory and have more flexibility I can
use it. I added my code in my first mail.
So I should create excell file and this file has many records. I just want
to lear how can I use Csv in Pl/SQL and also it is create an excell file ?
Thank you
Note: wiki pedia gives general information not information about its usage
Nick Burch wrote:
>
> On Wed, 19 Dec 2007, peri wrote:
>> The jvm heap size is 1024. So I cannot improve much.
>
> Memory's fairly cheap currently...
>
>> How can I use CSV? I didn't use before and I don't know how I can use
>> it.
>
> http://en.wikipedia.org/wiki/Comma-separated_values
>
> You just write out plain text, as long as you quote strings it's about as
> simple as a data interchange format gets...
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/How-can-I-solve-the-outof-memory-error-on-excell-file-which-is-created-hssfworkbook-tp14412958p14420815.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How can I solve the outof memory error on excell file which is
created hssfworkbook
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 19 Dec 2007, peri wrote:
> The jvm heap size is 1024. So I cannot improve much.
Memory's fairly cheap currently...
> How can I use CSV? I didn't use before and I don't know how I can use
> it.
http://en.wikipedia.org/wiki/Comma-separated_values
You just write out plain text, as long as you quote strings it's about as
simple as a data interchange format gets...
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How can I solve the outof memory error on excell file which is
created hssfworkbook
Posted by peri <er...@hotmail.com>.
The jvm heap size is 1024. So I cannot improve much. How can I use CSV? I
didn't use before and I don't know how I can use it.
Nick Burch wrote:
>
> On Wed, 19 Dec 2007, peri wrote:
>> In the program I can only write 12000 records to the excell file. After
>> 12000 records I get out of memory errors.
>
> I'd suggest either writing out as a csv (much less memory hungry), or
> increasing your java heap size.
>
> Unfortunately, building up excel files requires lots of memory, and
> there's no way to do a streaming write (the file format isn't really
> designed for that)
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/How-can-I-solve-the-outof-memory-error-on-excell-file-which-is-created-hssfworkbook-tp14412958p14420276.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How can I solve the outof memory error on excell file which is
created hssfworkbook
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 19 Dec 2007, peri wrote:
> In the program I can only write 12000 records to the excell file. After
> 12000 records I get out of memory errors.
I'd suggest either writing out as a csv (much less memory hungry), or
increasing your java heap size.
Unfortunately, building up excel files requires lots of memory, and
there's no way to do a streaming write (the file format isn't really
designed for that)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org