PROCEDURE XLS IS
BEGIN
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; 
args OLE2.OBJ_TYPE; 
ctr NUMBER(12); 
SER1 NUMBER;
cols NUMBER(2);
name_var1 VARCHAR2(2000);
name_var2 VARCHAR2(2000); 
name_var3 VARCHAR2(2000); 
name_var4 VARCHAR2(2000); 
name_var5  VARCHAR2(2000); 
name_var6 varchar2(2000) ;

name_var7 varchar2(2000) ;
name_var8 varchar2(2000);
name_var9 date;
name_var10 varchar2(2000) ;
name_var11 varchar2(2000) ;
name_var12 date;
name_var13 date;
name_var14 VARCHAR2(2000);
name_var15 date;

name_var16 VARCHAR2(2000); 
name_var17 VARCHAR2(2000); 
name_var18  varchar2(2000);
name_var19  varchar2(2000);
name_var20  varchar2(2000);
filename varchar2(200);

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
delete from li_employees_temp;
 forms_ddl('commit') ;

CLEAR_BLOCK(NO_VALIDATE);
filename := :block7.FILE_NAME;

application := OLE2.CREATE_OBJ('Excel.Application'); 
ole2.set_property(application,'Visible','TRUE'); 
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); 
args := OLE2.CREATE_ARGLIST; 
ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args); 
ole2.destroy_arglist(args); 
args := OLE2.CREATE_ARGLIST; 
ole2.add_arg(args,'Sheet1'); 
worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args); 
ole2.destroy_arglist(args); 

ctr := 2; --row number
cols := 1; -- column number
FIRST_RECORD; 

LOOP 
OLEARG;
--get_char_property FOR VARCHAR
--get_num_property FOR NUMBER
name_var1 := ole2.get_char_property(cell,'Text'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var2 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var3 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var4 := ole2.get_char_property(cell,'Value'); --cell number value of the argument
cols := cols+1;


OLEARG;
name_var5 :=ole2.get_char_property(cell,'Value');
cols := cols+1;

--e_rec.oa_date:= to_date(OLE2.GET_CHAR_PROPERTY(MyCell, 'Text' ),'DD-MON-RRRR');

OLEARG;
name_var6 :=ole2.get_char_property(cell,'Value');
cols := cols+1;


OLEARG;
name_var7 :=ole2.get_char_property(cell,'Value');
cols := cols+1;


OLEARG;
name_var8 :=ole2.get_char_property(cell,'Value');
cols := cols+1;

OLEARG;
name_var9 := to_date( ole2.get_char_property(cell,'Text'),'dd-mm-rrrr'); --cell number value of the argument
cols := cols+1;



OLEARG;
name_var10 :=ole2.get_char_property(cell,'Value');
cols := cols+1;



OLEARG;
name_var11 :=ole2.get_char_property(cell,'Value');
cols := cols+1;





OLEARG;
name_var12 := to_date( ole2.get_char_property(cell,'Text'),'dd-mm-rrrr'); --cell number value of the argument
cols := cols+1;




OLEARG;
name_var13 := to_date( ole2.get_char_property(cell,'Text'),'dd-mm-rrrr'); --cell number value of the argument
cols := cols+1;






OLEARG;
name_var14 := ole2.get_char_property(cell,'Value'); --cell number value of the argument
cols := cols+1;



OLEARG;
name_var15 := to_date( ole2.get_char_property(cell,'Text'),'dd-mm-rrrr'); --cell number value of the argument
cols := cols+1;



OLEARG;
name_var19 := to_date( ole2.get_char_property(cell,'Text'),'dd-mm-rrrr'); --cell number value of the argument
cols := cols+1;


OLEARG;
name_var20 := to_date( ole2.get_char_property(cell,'Text'),'dd-mm-rrrr'); --cell number value of the argument
cols := cols+1;

OLEARG;
name_var16 := ole2.get_char_property(cell,'Value'); --cell number value of the argument----job_dgree

cols := cols+1;

OLEARG;
name_var17 := ole2.get_char_property(cell,'Value'); --cell number value of the argument ----medical_program


cols := cols+1;


OLEARG;
name_var18 := ole2.get_char_property(cell,'Value'); --cell number value of the argument ----medical_program

---ole2.get_num_property


EXIT WHEN name_var1 is null;--length(name_var1) = 0 or length(name_var1) is null; 
IF ctr = 1 then 
:A:=name_var1;
:B:=name_var2;
:C:=name_var3;
:D:=name_var4;
:E:=name_var5;
:F:=name_var6;
:G:=name_var7;
:H:=name_var8;
:I:=name_var9;
:J:=name_var10;
:K:=name_var11;
:L:=name_var12;
:M:=name_var13;
:N:=name_var14;
:O:=name_var15;
:S:=name_var19;
:T:=name_var20;
:P:=name_var16;
:Q:=name_var17;
:R:=name_var18;

 
 
 
 
  
NULL;
ELSE
:A:=name_var1;
:B:=name_var2;
:C:=name_var3;
:D:=name_var4;
:E:=name_var5;
:F:=name_var6;
:G:=name_var7;
:H:=name_var8;
:I:=name_var9;
:J:=name_var10;
:K:=name_var11;
:L:=name_var12;
:M:=name_var13;
:N:=name_var14;
:O:=name_var15;
:S:=name_var19;
:T:=name_var20;
:P:=name_var16;
:Q:=name_var17;
:R:=name_var18;

NEXT_RECORD;
END IF; 
SYNCHRONIZE;
ctr := ctr + 1; 
cols := 1;

END LOOP;
DELETE_RECORD;
FIRST_RECORD;
OLE2.INVOKE(application,'Quit'); 
OLE2.RELEASE_OBJ(cell); 
OLE2.RELEASE_OBJ(worksheet); 
--OLE2.RELEASE_OBJ(worksheets); 
OLE2.RELEASE_OBJ(workbook); 
--OLE2.RELEASE_OBJ(workbooks); 
OLE2.RELEASE_OBJ(application); 
END; 

commit_form;
COMMIT;

END;