Home » Developer & Programmer » Forms » How to Use Excel With Oracle
How to Use Excel With Oracle [message #77713] Tue, 06 November 2001 23:00 Go to next message
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 Go to previous messageGo to next message
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;

----------------------------------------------------------------------
Re: How to Use Excel With Oracle [message #78060 is a reply to message #77713] Wed, 09 January 2002 18:18 Go to previous message
Manoj
Messages: 101
Registered: August 2000
Senior Member
Tell me how can i convert excel data to oracle tables
Previous Topic: Re: search Calendar.pll urgent !!!
Next Topic: How to locate forms60_path
Goto Forum:
  


Current Time: Thu Mar 28 13:20:20 CDT 2024