HomeOracle

Excel Document Management with Oracle iFS (Internet File System)

Marin Komadina
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:

Method

UNLOAD Methods
(Oracle2Excel)

SQL

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.

PLSQL

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)

InterMedia

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

TOAD

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:

Method

LOAD Methods (Excel
2Oracle)

SQLoader

loads
text delimited file (csv format) into Oracle table

PLSQL

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.

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.
Previous article
Automating ETL using Oracle warehouse Builder – Part 2: OWB architecture
Next article
Enhanced SQL Server Security Auditing

Related Articles

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

Advertisers

Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.

Advertise with Us

Menu

Our Brands

Property of TechnologyAdvice.
© 2024 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.