Will your Access and Excel users be able to view data within an Oracle database right now, or do they have to wait for a SQL or SQL*Plus class next month?
Fortunately, for you and your users, giving them the ability to access an Oracle database using either Access or Excel is easy to do.
However, as a new Oracle DBA in a small to medium sized business where all of your users are Access and Excel wizards, should you be expected to teach all of your Office users how to become Oracle experts when you yourself barely know what to do?
So far, you have been armed with the Oracle 10g 2 Day DBA course because your business has taken the plunge into the Oracle world.
GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell ole2.destroy_arglist(args); END; BEGIN filename :=GET_FILE_NAME('c:\', File_Filter='Excel Files (*.xls)|*.xls|'); -- to pick the file application := OLE2. Application'); ole2.set_property(application,'Visible','true'); workbooks := OLE2. GET_OBJ_PROPERTY(application, 'Workbooks'); args := OLE2. CREATE_ARGLIST; ole2.add_arg(args,filename); --'c:s002.xls'); -- file path and name workbook := ole2.
Shown below is a typical pre-Oracle view of the window. In the window shown below, simply enter the name of your Oracle database, give it a description, enter the name of the service and provide a user ID (I added Scott's password so as not to be prompted for it down the road).
If you want to test the connectivity, click on the Test Connection button (good idea).
Changes are pushed back to the My SQL Server as a transaction batch with the click of a button, or can be pushed as soon as they are done with no further clicks if users prefer it.
This is a powerful feature since Excel is a natural user interface to operate with data, and these changes can be reflected in the database immediately.