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

Question

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

Answer 1

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).