Cfrgtkky

I want to generate report in Excel File using oracle forms 10g and implement trigger when-button-pressed. When button press then getting following error:

40735: when-button-pressed trigger raised unhandled exception ORA-305500

Code:

declare

  application ole2.obj_type;

  workbooks ole2.obj_type;

  workbook ole2.obj_type;

  worksheets ole2.obj_type;

  worksheet ole2.obj_type;

 cell ole2.obj_type;

 arglist ole2.list_type;

 row_num number;

 col_num number;

 fontObj ole2.obj_type;



cursor rec is SELECT so.descr saleorgdescr,ih.invdate invdatemaster, ih.docNUM docnum,

   TO_CHAR(ih.invdate,'mon-yyyy') invmonth

FROM ARMINVHEAD ih, SDMSALEORG so

WHERE

ih.status='69'

AND  TO_DATE(ih.INVDATE,'DD-MM-RRRR')

              BETWEEN

                   TO_DATE('01-01-2008','DD-MM-RRRR') 

              AND  

                   TO_DATE('01-01-2009','DD-MM-RRRR') 

order by IH.INVDATE, ih.docnum;



procedure SetCellValue(rowid number,colid number,cellValue varchar) is

begin

arglist := ole2.create_arglist;

ole2.add_arg(arglist,rowid);

ole2.add_arg(arglist,colid);

cell:= ole2.get_obj_property(worksheet,'Cells',arglist);

fontObj := ole2.get_obj_property(cell,'Font');

ole2.destroy_arglist(arglist);

ole2.set_property(cell,'value',cellValue);

ole2.set_property(fontObj,'Size',16);

ole2.set_property(fontObj,'BOLD',1);

ole2.set_property(fontObj,'ColorIndex',7);

ole2.release_obj(cell);

end SetCellValue;



procedure app_init is

begin

application := ole2.create_obj('Excel.Application');

ole2.set_property(application,'Visible',true);

workbooks := ole2.get_obj_property(application,'workbooks');

workbook := ole2.Get_Obj_Property(workbooks,'add');

worksheets := ole2.get_obj_property(application,'worksheets');

worksheet := ole2.Get_Obj_Property(worksheets,'add');

ole2.set_property(worksheet,'Name','Emp Sheet');

end app_init;



procedure save_excel(path varchar,filename varchar) is

begin

OLE2.Release_Obj(worksheet);

OLE2.Release_Obj(worksheets);

-- Save the Excel file created

If path is not null then

Arglist := OLE2.Create_Arglist;

OLE2.Add_Arg(Arglist,path||''||file_name||'.xls');

OLE2.Invoke(workbook, 'SaveAs', Arglist);

OLE2.Destroy_Arglist(Arglist);

end if;

end save_excel;



begin

app_init;

row_num:=1;

col_num:=1;

SetCellValue(row_num,col_num,'saleorgdescr');

col_num:=col_num + 1;

SetCellValue(row_num,col_num,'invdatemaster');

col_num:=col_num + 1;

SetCellValue(row_num,col_num,'docnum');

col_num:=col_num + 1;

SetCellValue(row_num,col_num,'invmonth');

for i in rec loop

row_num:=row_num + 1;

col_num:=1;

SetCellValue(row_num,col_num,i.saleorgdescr);    

col_num:=2;

SetCellValue(row_num,col_num,i.invdatemaster);    

col_num:=3;

SetCellValue(row_num,col_num,i.docnum);

col_num:=4;

SetCellValue(row_num,col_num,i.invmonth);   

end loop;    

save_excel('C:excel_export','emp_data');       

OLE2.Release_Obj(workbook);

OLE2.Release_Obj(workbooks);

OLE2.Release_Obj(application); 

end;

How to solve this problem in oracle forms 10g?

I am using oracle forms 10g with Oracle Apps ERP Module

ORA-305500 is a generic OLE2 error, indicating some problem between Forms and a non-Oracle resource, in this case Excel.

The following error raise at line application := ole2.create_obj('Excel.Application');

The basic problem is you are using Forms 10g, which uses an n-tier architecture. This is a massive change from client-server of Forms 6i or earlier. It means Forms OLE runs applications like Excel locally to the Forms application i.e. on the Forms app server not your desktop. So OLE won't work, unless your Forms app server runs on Windows OS and has Excel installed, and even then it will generate the file on the server.

Forms 10g comes with the Webutils library to support client-server style functionally in a multi-tier system. You need to use CLIENT_OLE2() functions to wrangle Excel in Forms 10g and WebUtil_File.File_Save_Dialog() to save the file to a local directory.

There is a good example on the OTN Forum here (link).

Webutil Error: Oracle.forms.webutil.ole.OleFunctions bean not found

Sounds like you haven't configured Forms to use Webutil properly (or at all). There's some stuff in the Forms Help document or you can follow this article (link).