Below is the code I have used for uploading data from excel sheet to oracle table.My issue is that this is not working in the server machine as well as in any of the client machines except one machine. In one of the client machines,the data gets loaded to the table successfully.Oracle forms 11g is installed in this machine.I need it to work on other client machines as well(forms will not be installed in those machines).
When I run this form(the link is from the server),it gives the following error message:
oracle.forms.webutil.ole.OleFunctions bean not found CLIENT_OLE2.CREATE_OBJ will not work.
I wonder if there is any other configuration need to be done in the client machines.Please help with your suggestions.
PROCEDURE get_from_xls_accs IS
application Client_OLE2.Obj_Type;
workbooks Client_OLE2.Obj_Type;
workbook Client_OLE2.Obj_Type;
worksheets Client_OLE2.Obj_Type;
worksheet Client_OLE2.Obj_Type;
worksheet2 Client_OLE2.Obj_Type;
cell Client_OLE2.OBJ_TYPE;
args Client_OLE2.OBJ_TYPE;
cell_value varchar2(100);
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod boolean:=false;
MyUsedRange client_ole2.obj_type; -- Range object
MyRows client_ole2.obj_type; -- Range object
ret_val number;
j integer:=1;
v_fName VARCHAR2(250);
v_PART_NO VARCHAR2(100);
v_DESCR VARCHAR2(50);
v_COLR_DESC VARCHAR2(30);
v_PACKING VARCHAR2(20) ;
v_FOB_US varchar2(10);
v_COST_KD varchar2(10);
v_SP_CUST varchar2(10);
v_LAB_KD varchar2(10);
v_COMM_MAC varchar2(10);
v_COMM_KD varchar2(10);
v_SP_COUNTER varchar2(10);
v_SP_TOT_CUST varchar2(10);
BEGIN
if :file_name is not null then
application:=client_ole2.create_obj('Excel.application');
client_ole2.set_property(application,'Visible','false');
message('APPLICATION:'||application);
workbooks:=client_ole2.get_obj_property(application,'Workbooks');
args:=client_ole2.create_arglist;
client_ole2.add_arg(args,:file_name );
workbook := client_ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
client_ole2.destroy_arglist(args);
worksheets := client_ole2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
num_wrkshts := client_ole2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet := client_ole2.GET_OBJ_PROPERTY(application,'activesheet');
MyUsedRange:=client_ole2.GET_OBJ_PROPERTY( worksheet, 'UsedRange' );
MyRows:=client_ole2.GET_OBJ_PROPERTY( MyUsedRange, 'Rows' );
ret_val:=client_ole2.GET_NUM_PROPERTY( MyRows, 'Count' );
message('No of rows in worksheet'||ret_val);
loop
for k in 1..12 loop --no of fields per record
args:= client_ole2.create_arglist;
client_ole2.add_arg(args, j);
client_ole2.add_arg(args, k);
cell:= client_ole2.get_obj_property(worksheet, 'Cells', args);
client_ole2.destroy_arglist(args);
cell_value :=client_ole2.get_char_property(cell, 'Value');
if cell_value = 'EOD' then
eod:=true;
Message('End of Data');
exit;
end if;
if k =1 then
v_part_no:=cell_value;
end if;
if k =2 then
v_DESCR:=cell_value;
end if;
if k =3 then
v_COLR_DESC:=cell_value;
end if;
if k =4 then
v_PACKING:=cell_value;
end if;
if k =5 then
v_FOB_US:=cell_value;
end if;
if k =6 then
v_COST_KD:=cell_value;
end if;
if k =7 then
v_SP_CUST:=cell_value;
end if;
if k =8 then
v_LAB_KD:=cell_value;
end if;
if k =9 then
v_COMM_MAC:=cell_value;
end if;
if k =10 then
v_COMM_KD:=cell_value;
end if;
if k =11 then
v_SP_COUNTER:=cell_value;
end if;
if k =12 then
v_SP_TOT_CUST:=cell_value;
end if;
next_item;
end loop;
j:=j+1;
insert into xx_table VALUES
(
substr(v_PART_NO,1,length(v_PART_NO)-2),
substr(v_DESCR,1,length(v_DESCR)-2),
substr(v_COLR_DESC,1,length(v_COLR_DESC)-2),
substr(v_PACKING,1,length(v_PACKING)-2),
substr(v_FOB_US,1,length(v_FOB_US)-2),
substr(v_COST_KD,1,length(v_COST_KD)-2),
substr(v_SP_CUST,1,length(v_SP_CUST)-2),
substr(v_LAB_KD,1,length(v_LAB_KD)-2),
substr(v_COMM_MAC,1,length(v_COMM_MAC)-2),
substr(v_COMM_KD,1,length(v_COMM_KD)-2),
substr(v_SP_COUNTER,1,length(v_SP_COUNTER)-2),
substr(v_SP_TOT_CUST,1,length(v_SP_TOT_CUST)-2),
:GLOBAL.USERID,
SYSDATE);
exit when j=ret_val+1;
end loop;--main loop
commit_form;
-- Release the Client_client_ole2 object handles
IF (cell IS NOT NULL) THEN
client_ole2.release_obj(cell);
-- message('released cell');
END IF;
IF (worksheet IS NOT NULL) THEN
client_ole2.release_obj(worksheet);
-- message('released worksheet');
END IF;
IF (worksheets IS NOT NULL) THEN
client_ole2.release_obj(worksheets);
-- message('released worksheets');
END IF;
IF (worksheet2 IS NOT NULL) THEN
client_ole2.release_obj(worksheet2);
-- message('released worksheet2');
END IF;
IF (workbook IS NOT NULL) THEN
client_ole2.release_obj(workbook);
--message('released workbook');
END IF;
IF (workbooks IS NOT NULL) THEN
client_ole2.release_obj(workbooks);
-- message('released workbooks');
END IF;
client_ole2.invoke(application,'Quit');
client_ole2.release_obj(application);
-- message('released application');
message('Data loaded successfully');
ELSE
Message('No File selected.');
message(' ');
RAISE Form_Trigger_Failure;
END IF;
message('Quitting application');
client_ole2.invoke(application,'Quit');
END;
Thanks in advance.