Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 1, 2003

Excel Document Management with Oracle iFS (Internet File System)

By Marin Komadina

Project managers spend most of their working time producing reports, charts, graphs and calculations using the MS Excel spreadsheet program. Working with the spreadsheets, they are faced with several problems:

  • My spreadsheets are spread all around network servers and local disks.
  • How fast and how easy is it to find them?
  • Is the data secured?
  • Which spreadsheet document is the most current copy?
  • What if documents are deleted and then needed later?
  • Who was previously working on the same spreadsheet?
  • How to notify others when a document is updated or even being read?

Because of this information chaos, project managers definitely need help. The Oracle DBA is right guy to help them. The main the question, is how?

There are a couple of the ways to approach this, and this article will explain one of the lesser-known methods, using Oracle iFS. With iFS, Excel documents can be stored and retrieved from the Oracle database using a secure environment.

This article covers:

  • Different Methods to Save and Retrieve Spreadsheets from an Oracle Database
  • Oracle iFS Option
  • Oracle iFS Installation and Administration
  • Practical use for Managers
  • Conclusion

Different Methods to Save and Retrieve Spreadsheets from Oracle Database

There are several methods to load data from Excel to Oracle and vice versa. Some of the methods are widely used while others are complicated and very seldom used.

Several of the most used "Oracle2Excel" unload and "Excel2Oracle" load methods are:


UNLOAD Methods (Oracle2Excel)


spools your query into delimited text file of any size (csv comma-separated values format file) and open the csv file as an Excel spreadsheet. Absolute limit of 2000 bytes in 7.x and 4000 bytes in 8.x per line/row for unloaded data.


stored procedure or pl/sql anonyms block, writing in Excel file in several formats (csv to SYLK)

Oracle ODBC

uses ODBC to query data from Oracle into an MS Access table (ordinary ODBC external data)


data converted by interMedia into HTML, and parsed by PLSQL

Java, C, Pro*C

user made programs for data extraction and manipulation in a flat file, prepared to load in Excel

Oracle Objects for OLE (OO4O)

OLE saves the document along with some additional information into the LONG RAW column of a table via VBA macro

MS Query

enables native select from Oracle to Excel

(Get External Data from Excel submenu)

Excel Add-in SQL*XL

add-in for Excel that uses Oracle objects for OLE and sql*net to make connection from Oracle query data into an Excel sheet


saves Oracle table data directly to an Excel file

Oracle iFS

Excel can read/write the spreadsheet from/to the database naturally

Several of the most used "Excel2Oracle" load methods:


LOAD Methods (Excel 2Oracle)


loads text delimited file (csv format) into Oracle table


uses TEXT_IO package that enables Input/Output on local files provided with Developer 2000 Forms 4.5, or using native RDBMS packages for external I/O with 255 bytes/row limit

External Tables (Oracle 9i)

loads filtered and transformed Excel binary data using SQL into BLOB column data type

Oracle ODBC

loads data from Excel to MS Access and then to Oracle using ODBC and user program (for example VB script)

InterMedia Text

loads the Excel spreadsheet into the database using interMedia text (convert XLS spreadsheet into a big HTML table)

Java, C, Pro*C

User made programs for data load and manipulation

Oracle Objects For OLE

writes a program (VB script, or Oracle Forms) which is using OLE automation to interact with Excel, executing queries and managing the results

Excel Add-in SQL*XL

SQL*XL adds a new menu item to Excel so Oracle access appears to be native, you can use the data in e.g. Excel calculations and charts without problems, main method for retrieving data is through SQL

4TOPS Excel Import Assistant

integrated wizard for MS Access to convert Excel data to Oracle

Oracle iFS

Excel can read/write the spreadsheet from/to the database naturally

Any of these options may be used; however, the final choice depends on the customer's specific needs.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM