DevOps, CI/CD and Automation

Hi

I just wanted a help regarding excel.exe running still in the background engine i.e. in the background process even after work is finished. can u please help me locate and solve the problem. the package mentioned below is called in oracle reports.

package rpt2xls is
-- font style constants
bold constant binary_integer := 1;
italic constant binary_integer := 2;
underline constant binary_integer := 4;

-- horizontal alignment constants
subtype xlhalign is binary_integer;
center constant xlhalign := -4108;
centeracrossselection constant xlhalign := 7;
distributed constant xlhalign := -4117;
fill constant xlhalign := 5;
general constant xlhalign := 1;
justify constant xlhalign := -4130;
left constant xlhalign := -4131;
right constant xlhalign := -4152;

procedure put_cell (colno_from in binary_integer,
cellvalue in varchar2,
colno_to in binary_integer default null,
fontname in varchar2 default null,
fontsize in binary_integer default null,
fontstyle in binary_integer default null,
fontcolor in binary_integer default null,
bgrcolor in binary_integer default null,
format in varchar2 default null,
align in xlhalign default null);

procedure new_line;
procedure prev_line;
procedure run (p_file_name in varchar2);
procedure release_memory;
end;

package body rpt2xls is
type excelcell is record (rowno binary_integer, colno_from binary_integer, colno_to binary_integer,
val varchar2(2000), fontname varchar2(20), fontsize binary_integer,
fontstyle binary_integer, fontcolor binary_integer, bgrcolor binary_integer,
format varchar2(60), align xlhalign);
type excelcells is table of excelcell;
cell excelcells := excelcells();

currentrow binary_integer := 1;

procedure new_line is
begin
currentrow := currentrow + 1;
end;

procedure prev_line is
begin
currentrow := currentrow - 1;
end;

procedure put_cell (colno_from in binary_integer,
cellvalue in varchar2,
colno_to in binary_integer default null,
fontname in varchar2 default null,
fontsize in binary_integer default null,
fontstyle in binary_integer default null,
fontcolor in binary_integer default null,
bgrcolor in binary_integer default null,
format in varchar2 default null,
align in xlhalign default null) is
begin
cell.extend;
cell(cell.last).rowno := currentrow;
cell(cell.last).colno_from := colno_from;
cell(cell.last).val := cellvalue;
cell(cell.last).colno_to := colno_to;
cell(cell.last).fontname := fontname;
cell(cell.last).fontsize := fontsize;
cell(cell.last).fontstyle := fontstyle;
cell(cell.last).fontcolor := fontcolor;
cell(cell.last).bgrcolor := bgrcolor;
cell(cell.last).format := format;
cell(cell.last).align := align;
end;

procedure run (p_file_name varchar2) is
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
workcell ole2.obj_type;
workcolumn ole2.obj_type;
workfont ole2.obj_type;
workinterior ole2.obj_type;
workrange ole2.obj_type;
arglist ole2.list_type;
worksheet_idx number;
worksheet_cnt number;
v_desc varchar2(100);
v_row_count number;
begin
application := ole2.create_obj('Excel.Application');
ole2.set_property(application, 'DisplayAlerts', 0);
if p_file_name is null then
ole2.set_property(application, 'Visible', 1);
else
ole2.set_property(application, 'Visible', 0);
end if;
workbooks := ole2.get_obj_property(application, 'Workbooks');
workbook := ole2.invoke_obj(workbooks, 'Add');
worksheets := ole2.get_obj_property(workbook, 'Worksheets');
worksheet := ole2.get_obj_property(application, 'ActiveSheet');

-- Create Extra 7 Excel Sheets
for i in 1..7
loop
worksheet := ole2.invoke_obj(worksheets,'Add');
end loop;

-- Rename all 10 Excel Sheets
for i in 1..10
loop
arglist := ole2.create_arglist;
ole2.add_arg(arglist, i);
worksheet := ole2.get_obj_property(worksheets,'Item', arglist);
ole2.invoke(worksheet,'Select');
ole2.destroy_arglist(arglist);
ole2.set_property (worksheet,'Name', 'Page'||to_char(i,'FM99'));
end loop;

-- Go to First Sheet
arglist := ole2.create_arglist;
ole2.add_arg(arglist, 1 );
worksheet := ole2.get_obj_property(worksheets,'Item', arglist);
ole2.destroy_arglist(arglist);
ole2.invoke( worksheet, 'Activate');
worksheet_idx := ole2.get_num_property(worksheet, 'Index');
worksheet_cnt := ole2.get_num_property(worksheets, 'Count');

v_row_count := 1;
for i in cell.first .. cell.last
loop
if cell(i).val is not null then
arglist := ole2.create_arglist;
ole2.add_arg(arglist, v_row_count);
ole2.add_arg(arglist, cell(i).colno_from);
workcell := ole2.get_obj_property(worksheet, 'Cells', arglist);
ole2.destroy_arglist(arglist);
ole2.set_property(workcell, 'Value', cell(i).val);
ole2.set_property(workcell, 'NumberFormat', cell(i).format);

if cell(i).align is not null then
ole2.set_property(workcell, 'HorizontalAlignment', cell(i).align);
end if;

workfont := ole2.get_obj_property(workcell, 'Font');
workinterior := ole2.get_obj_property(workcell, 'Interior');

if cell(i).fontname is not null then
ole2.set_property(workfont, 'Name', cell(i).fontname);
end if;
if cell(i).fontsize is not null then
ole2.set_property(workfont, 'Size', cell(i).fontsize);
end if;
if mod(cell(i).fontstyle, 2) = 1 then
ole2.set_property(workfont, 'Bold', 1);
end if;
if mod(cell(i).fontstyle, 4) > 2 then
ole2.set_property(workfont, 'Italic', 1);
end if;
if mod(cell(i).fontstyle, 8) > 4 then
ole2.set_property(workfont, 'Underline', 2);
end if;
if cell(i).fontcolor is not null then
ole2.set_property(workfont, 'ColorIndex', cell(i).fontcolor);
end if;
if cell(i).bgrcolor is not null then
ole2.set_property(workinterior, 'ColorIndex', cell(i).bgrcolor);
end if;
if cell(i).colno_to is not null then
arglist := ole2.create_arglist;
v_desc := chr(64 + cell(i).colno_from) || v_row_count || ':' || chr(64 + cell(i).colno_to) || v_row_count;
ole2.add_arg(arglist, v_desc);
workrange := ole2.get_obj_property(worksheet, 'Range', arglist);
ole2.destroy_arglist(arglist);
ole2.invoke(workrange, 'Merge');
ole2.release_obj(workrange);
end if;
ole2.release_obj(workinterior);
ole2.release_obj(workfont);
ole2.release_obj(workcell);
end if;

if i <= cell.last-1 and cell(i).rowno <> cell(i+1).rowno then
v_row_count := v_row_count + (cell(i+1).rowno - cell(i).rowno);
if v_row_count > 65000 then
-- Autofit Excel Sheet
arglist := ole2.create_arglist;
ole2.add_arg(arglist, 'A:Z');
workcolumn := ole2.get_obj_property(worksheet, 'Columns', arglist);
ole2.destroy_arglist(arglist);
ole2.invoke(workcolumn, 'AutoFit');

-- Go to Next Sheet
worksheet_idx := worksheet_idx + 1;
if worksheet_idx > worksheet_cnt then
exit;
end if;
arglist := ole2.create_arglist;
ole2.add_arg(arglist, worksheet_idx);
worksheet := ole2.get_obj_property(worksheets,'Item', arglist);
ole2.invoke(worksheet,'Select');
ole2.destroy_arglist(arglist);

-- Initialize Row Count
v_row_count := 1;
end if;
end if;
end loop;

-- Autofit Excel Sheet
arglist := ole2.create_arglist;
ole2.add_arg(arglist, 'A:Z');
workcolumn := ole2.get_obj_property(worksheet, 'Columns', arglist);
ole2.destroy_arglist(arglist);
ole2.invoke(workcolumn, 'AutoFit');

-- Delete Blank Excel Extra Sheets
for i in worksheet_idx+1 .. worksheet_cnt
loop
arglist := ole2.create_arglist;
ole2.add_arg(arglist, worksheet_idx+1);
worksheet := ole2.get_obj_property(worksheets,'Item', arglist);
ole2.invoke(worksheet,'Delete');
ole2.destroy_arglist(arglist);
end loop;

if p_file_name is not null then
arglist := ole2.create_arglist;
ole2.add_arg(arglist, p_file_name);
ole2.invoke(workbook, 'SaveAs', arglist);
ole2.destroy_arglist(arglist);
ole2.invoke(workbook, 'Close');
ole2.invoke(application, 'Quit');
end if;
ole2.release_obj(workcolumn);
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
end;

procedure release_memory is
begin
cell := excelcells();
sys.dbms_session.free_unused_user_memory;
end;
end;

function AfterReport return boolean is
begin
if :p_excel_flg = 'Y' and :p_output = 'Y' then
rpt2xls.run (:desname);
rpt2xls.release_memory;
end if;
return (TRUE);
end;

Regards
Nagaraj