| How to Use Excel With Oracle [message #77713] |
Tue, 06 November 2001 23:00  |
Senem
Messages: 1
Registered: November 2001
|
Junior Member |
|
|
I would like to know how to use an Excel file in Oracle Forms. I want to transfer data inside an Excel file to a table in Oracle. And I want to do it by coding in Oracle Forms. First I want to open an Excel file by coding, and then read data and then transfer data by coding again.
If you tried to do it before, please let me know.
Thanks,...
----------------------------------------------------------------------
|
|
|
|
| Re: How to Use Excel With Oracle [message #77716 is a reply to message #77713] |
Wed, 07 November 2001 03:50  |
Shilpa
Messages: 59
Registered: November 2001
|
Member |
|
|
One way to solve your problem is to save the excel file as a text file and load the data using SQL LOADER from forms.
The following example from Oracle will give you an idea of another way.
The following example opens a specific workbook in the background and
returns the contents of cell A1 in worksheet Sheet1 of that workbook
to Oracle Forms:
DECLARE
-- 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;
-- 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_value varchar2(2000);
BEGIN
-- Check the file can be found, if not exception no_file will be raised
check_file := TEXT_IO.FOPEN('C:TESTEXAMPLE.XLS','R');
TEXT_IO.FCLOSE(check_file);
application:= ole2.create_obj('Excel.Application');
workbooks := ole2.invoke_obj(application, 'Workbooks');
-- Open the required workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'C:TESTEXAMPLE.XLS');
workbook := ole2.invoke_obj(workbooks, 'Open', args);
ole2.destroy_arglist(args);
-- Open worksheet Sheet1 of that Workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'Sheet1');
worksheet := ole2.invoke_obj(workbook, 'Worksheets', args);
ole2.destroy_arglist(args);
-- Get value of cell A1 of worksheet Sheet1
args:= ole2.create_arglist;
ole2.add_arg(args, 1);
ole2.add_arg(args, 1);
cell:= ole2.invoke_obj(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
cell_value :=ole2.get_char_property(cell, 'Value');
message(cell_value);
ole2.invoke(application,'Quit');
-- Release the OLE2 object handles
ole2.release_obj(application);
ole2.release_obj(workbooks);
ole2.release_obj(workbook);
ole2.release_obj(worksheets);
ole2.release_obj(cell);
EXCEPTION
WHEN no_file THEN
MESSAGE('File not found.');
WHEN OTHERS THEN
MESSAGE(sqlerrm);
PAUSE;
FOR i IN 1 .. tool_err.nerrors LOOP
MESSAGE(tool_err.message);
PAUSE;
tool_err.pop;
END LOOP;
END;
----------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
