PROCEDURE PROC_READ_EXCEL
(
p_file_name in varchar2
)

IS
-- Declare handles to OLE objects
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheet ole2.obj_type;
cell ole2.obj_type;
cell1 ole2.obj_type;
ctr NUMBER(12);
cols NUMBER(2);
-- Declare handles to OLE argument lists
args ole2.list_type;

Check_file text_io.file_type;
no_file exception;
PRAGMA exception_INIT (no_file, -302000);
cell_value1 varchar2(2000);
cell_value2 varchar2(2000);
cell_value3 varchar2(2000);
cell_value4 varchar2(2000);
sor_start_row number;
sor_start_col number;
temp number := 0;


PROCEDURE OLEARG IS
args OLE2.OBJ_TYPE;
BEGIN
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,ctr); -- row value
ole2.add_arg(args,cols); -- column value
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell
ole2.destroy_arglist(args);
END;

BEGIN
message('1');
Check_file := TEXT_IO.FOPEN(p_file_name,'R');
TEXT_IO.FCLOSE(Check_file);
message('2'||sqlerrm);
application:= ole2.create_obj('Excel.Application');
message('2.1'||sqlerrm);


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

-- Open the required workbook
args:= ole2.create_arglist;
message('4.1'||sqlerrm);
ole2.add_arg(args,p_file_name); --ole2.add_arg(args, 'C:test.XLS');
message('4.2'||sqlerrm);
workbook := ole2.invoke_obj(workbooks, 'Open', args);
message('4.3'||sqlerrm);
ole2.destroy_arglist(args);
message('4.4'||sqlerrm);
-- Open worksheet Sheet1 of that Workbook
worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');

OLE2.SET_PROPERTY(worksheet , 'Value','Sheet1');
message('6'||sqlerrm);
-- Get value of cell A1 of worksheet Domestic Layout
args:= ole2.create_arglist;
message('7'||sqlerrm);
/* To find X-Y Co-ordinates of SOR */
ctr := 2; --row number from where reading will start
cols :=1; -- column number from where reading will start
message('7'||sqlerrm);
loop
OLEARG;
cell_value1 := ole2.get_num_property(cell,'Value'); --cell value of the argument
cols := cols+1;
message('1.1'||sqlerrm);
OLEARG;
cell_value2 := ole2.get_num_property(cell,'Value'); --cell value of the argument
cols := cols+1;
message('1.2'||sqlerrm);
OLEARG;
cell_value3 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;
message('1.3'||sqlerrm);
OLEARG;
cell_value4 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;
message('1.4'||sqlerrm);
IF cell_value1 is null or cell_value1 = 0 THEN
EXIT;
END IF;
message('1.5'||sqlerrm);
insert into AMHRS_EMP_PLACEMENT_TMP_TBL
(
Employee_code,
Manager_Code,
new_project_code,
change_date
)
values
(
cell_value1,
cell_value2,
cell_value3,
cell_value4
);
standard.commit;
message('1.6'||sqlerrm);
ctr := ctr + 1;
cols := 1;

END LOOP;
message('1.7'||sqlerrm);
sor_start_col:= sor_start_col - 2;
temp:= 0;
ole2.invoke(application,'Quit');
-- Release the OLE2 object handles
ole2.release_obj(cell);
ole2.release_obj(worksheet);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
exception
WHEN no_file THEN
MESSAGE('file not found.');
message('1.8'||sqlerrm);
WHEN OTHERS THEN
message('1.9'||sqlerrm);
ole2.invoke(application,'Quit');
ole2.release_obj(cell);
ole2.release_obj(worksheet);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
MESSAGE('Error in inserting data to temporary table');
rollback;
message('1.99'||sqlerrm);
END;