I have the following code behind a button on an Oracle Form 6i to display the data in an Excel spreadsheet.
However when the button is pressed to activate the code the whole process is taking too long. So long that the
program actually aborts half way through the process.
Please can someone have a look at the following code and explain how I can make the process work faster.
DECLARE
-- Declare handles to OLE objects
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
-- Declare handles to OLE argument lists
args OLE2.LIST_TYPE;
row_num number := 2;
CURSOR C1 IS select e.service_unit EngServUnit, st.system_group_code SysGpCode, sgc.meaning Modality,
e.plan_date PlanDate, sum(am_hours+pm_hours) SumHours from eng_planning e, systems s, system_types st,
system_group_codes sgc where e.config_id = s.config_id and s.system_type_corporate = st.system_type and
st.system_group_code = sgc.system_group_code and e.act_type = '20' and e.plan_date >= sysdate group by
e.service_unit,st.system_group_code,sgc.meaning,e.plan_date;
-- Declare the PL/SQL variables which will hold the data
-- returned from the database.
EngServUnit varchar2(10);
SysGpCode varchar2(10);
Modality varchar2(40);
PlanDate eng_planning.plan_date%TYPE;
SumHours number;
BEGIN
-- Create handle to application object
application:=OLE2.CREATE_OBJ('Excel.Application');
-- Create a Workbooks collection and add new Workbook to
-- Workbooks collection
workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
workbook:=OLE2.INVOKE_OBJ(workbooks,'Add');
-- Create a Worksheets collection and add new Worksheet to
-- Worksheets collection
worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add');
-- Insert Column Headings
-- write_column_header;
args:=ole2.create_arglist;
ole2.add_arg(args,1);
ole2.add_arg(args,1);
cell:=ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value','ServiceUnit');
ole2.release_obj(cell);
-- Fetch each employee record and pass values of employee name
-- and salary into Excel (employee names in first column of
-- worksheet and salaries in second column).
FOR ctr IN C1 LOOP
-- Create handle to cell in column 1 of appropriate row in
-- worksheet. (The arguments to the Cells method are the row
-- number and column number of the cell).
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 1);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
-- Put value of employee name into this cell
OLE2.SET_PROPERTY(cell, 'Value', ctr.EngServUnit);
OLE2.RELEASE_OBJ(cell);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 2);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', ctr.SysGpCode);
OLE2.RELEASE_OBJ(cell);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 3);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', ctr.Modality);
OLE2.RELEASE_OBJ(cell);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 4);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', to_char(ctr.PlanDate));
OLE2.RELEASE_OBJ(cell);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 5);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', SumHours);
OLE2.RELEASE_OBJ(cell);
row_num := row_num + 1;
END LOOP;
-- Enable user to view the Excel application to see results.
OLE2.SET_PROPERTY(application, 'Visible', 'True');
-- Release all OLE object handles
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;
Any help would be appreciated.
Many thanks
However when the button is pressed to activate the code the whole process is taking too long. So long that the
program actually aborts half way through the process.
Please can someone have a look at the following code and explain how I can make the process work faster.
DECLARE
-- Declare handles to OLE objects
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
-- Declare handles to OLE argument lists
args OLE2.LIST_TYPE;
row_num number := 2;
CURSOR C1 IS select e.service_unit EngServUnit, st.system_group_code SysGpCode, sgc.meaning Modality,
e.plan_date PlanDate, sum(am_hours+pm_hours) SumHours from eng_planning e, systems s, system_types st,
system_group_codes sgc where e.config_id = s.config_id and s.system_type_corporate = st.system_type and
st.system_group_code = sgc.system_group_code and e.act_type = '20' and e.plan_date >= sysdate group by
e.service_unit,st.system_group_code,sgc.meaning,e.plan_date;
-- Declare the PL/SQL variables which will hold the data
-- returned from the database.
EngServUnit varchar2(10);
SysGpCode varchar2(10);
Modality varchar2(40);
PlanDate eng_planning.plan_date%TYPE;
SumHours number;
BEGIN
-- Create handle to application object
application:=OLE2.CREATE_OBJ('Excel.Application');
-- Create a Workbooks collection and add new Workbook to
-- Workbooks collection
workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
workbook:=OLE2.INVOKE_OBJ(workbooks,'Add');
-- Create a Worksheets collection and add new Worksheet to
-- Worksheets collection
worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add');
-- Insert Column Headings
-- write_column_header;
args:=ole2.create_arglist;
ole2.add_arg(args,1);
ole2.add_arg(args,1);
cell:=ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value','ServiceUnit');
ole2.release_obj(cell);
-- Fetch each employee record and pass values of employee name
-- and salary into Excel (employee names in first column of
-- worksheet and salaries in second column).
FOR ctr IN C1 LOOP
-- Create handle to cell in column 1 of appropriate row in
-- worksheet. (The arguments to the Cells method are the row
-- number and column number of the cell).
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 1);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
-- Put value of employee name into this cell
OLE2.SET_PROPERTY(cell, 'Value', ctr.EngServUnit);
OLE2.RELEASE_OBJ(cell);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 2);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', ctr.SysGpCode);
OLE2.RELEASE_OBJ(cell);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 3);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', ctr.Modality);
OLE2.RELEASE_OBJ(cell);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 4);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', to_char(ctr.PlanDate));
OLE2.RELEASE_OBJ(cell);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_num);
OLE2.ADD_ARG(args, 5);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', SumHours);
OLE2.RELEASE_OBJ(cell);
row_num := row_num + 1;
END LOOP;
-- Enable user to view the Excel application to see results.
OLE2.SET_PROPERTY(application, 'Visible', 'True');
-- Release all OLE object handles
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;
Any help would be appreciated.
Many thanks