Excel Document Management with Oracle iFS (Internet File System)

Thursday Jul 31st 2003 by Marin Komadina
Share:

Marin Komadina explains one of the lesser-known methods of controlling information chaos using Oracle iFS. Learn how Excel documents can be securely stored and retrieved from the Oracle database using iFS.

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.

Oracle iFS Option

In version 8.1.7 of the database, Oracle has provided a special, extensible file system for managing and accessing content, called the Internet File System. With iFS, companies could use an existing Oracle database as a unified repository for storing different content (including audio, video, e-mail, formatted text). Over time, there were several iFS versions:

  • Oracle iFS Release 1.0.8.0
  • Oracle iFS Release 1.1.9
  • Oracle iFS Release 9.0.1
  • Oracle iFS Release 9.0.2

The latest 9iFS version supports the Windows File System, Network File System (Unix), Apple File Protocol, HTTP, FTP protocols and document formats like Plain Text, Word, Excel, HTML, PDF. Previously, the iFS were shipped separately. Eventually, the iFS became part of the Oracle 9iAS (Application Server) software under a 'Supplemental CD's' section. In the latest version, Oracle decided to change the name from iFS to the Oracle Content Management SDK, keeping iFS under the 9iAS software product.

The Oracle iFS instance is the name for the complete administrative iFS unit. The iFS instance consists of the several main components such as:

  • iFS Domain - administrative unit for all domain nodes. The iFS Domain consist of iFS Repository, iFS Nodes and iFS Domain Controler.

  • iFS Repository - set of Java classes and tables that manage the data in the 9iFS schema

  • iFS Domain Controller - iFS monitoring and controlling software for domain

  • iFS Node - iFS process running on one physical host

  • iFS Protocols - protocol servers handling the communication process between client and iFS repository (HTTP, SMB, FTP, NFS, AFP, SMTP, IMAP)

  • iFS Services - user session process running on the node (Agents, Parsers, Renderers, Overrides)

    Click for larger image
    The image on the left displays all of the elements of the iFS Instance.

    Once iFS is installed and the iFS elements are up and running, iFS client can start using the shared file system.

    Secure access to the iFS server running on Windows NT/2000 is controlled with the Windows security system. Secure access to the iFS server running on Windows NT/2000 is double-checked. It is first with the Windows security system and then, after a successful logon to the Windows domain or server, the login is checked against the iFS security. If we do not want to handle the double security system, we can simply use a Web Interface, where the user id is checked only once against the iFS security system. Likewise,
    with iFS running on a UNIX server; the user needs only a valid iFS account. 

    Often used praxis from the iFS client side is to map the shared iFS resource as an "O" drive. Main advantage of iFS is that access to iFS over standard FTP, SMB, HTTP, IMAP4, AFP and NFS clients is provided without need to install anything on the client side.

    The latest iFS version includes several new features such as:

    • versioning (in the database we will have several document versions saved, and new changes will create a new database entry rather to replace existing one)
    • check-in/check-out control which allows multiple users to share core documents
    • enhanced searching compatibilities and many others such as e-mail change notification
    • automatic expiration for old files
    • auto compress for documents and format changing on the fly
    • support for symbolic links
    • multilevel security with integrated anti-virus software
  • Oracle iFS Installation and Administration

    There is a no single document to help a DBA to easily install and manage the iFS infrastructure. Working with iFS showed that the best iFS configuration is similar to the following one:

    • Sun Solaris server with 8.1.7.4 Oracle Enterprise Edition database ( installed Java (Jserver/JVM) and UTF8 character set to enable full multi
    • language functionality ) holding iFS repository.
    • NT/2000 Server as iFS server
    • Windows client as iFS client

    Since we wanted to test the basic functionality of iFS as an Excel filesystem, we did not install any database extensions such as interMedia options. iFS working alone does not have any search capability. It is some kind of basic filesystem. To enhance iFS with a search capability, the interMedia option has to be installed in the database. InterMedia will bring context search capability to iFS.
    Testing with the different iFS versions, I have found that the installation procedure has several limitations:

    1. the database has to be at least version 8.1.7.2
    2. iFS 1.1.9 version has limited functionality where the HTTP server has to be run on database node
    3. iFS 9.0.1 needs the HTTP server to be installed in the same Oracle home
      directory, and not in the Oracle database directory.
    4. iFS 9.0.2 need the 9iAS to be installed first, since iFS is part of 9iAS and no longer a separate product.

    If the installation is successful, you can manage iFS services using following commands:

  • ifsconfig - Manually configuration or reconfiguration iFS
  • ifsstart - start iFS
  • ifsstop - stop iFS
  • ifssvrmgr - start and stop iFS
  • ifsjservctl - start and stop 9iFS Jserv process ( ifsjservctl -start )
  • ifslaunchdc - start the Domain Controller
  • ifslaunchnode - start node process
  • ifsstartdomain - opens the "Start Domain Controller" dialog and after activates node processes on each host machine.
  • ifsstopdomain -stops the domain

    The URL location for Web access or name of SMS shared resource has to be provided to the end users and they can start using it.

    Practical Use for Managers

    Once the 9iFS system is up and running, users can connect and upload their Excel files there. End users can use MS Internet Explorer or the Windows File Explorer to browse or manage content from the iFS Server. Here is an example of the Web and the Explorer view.

    The iFS start web login page is defined on iFS installation and it is usually located at URL:

    http://<hostname>/ifs/files/ifs.

    The Standard set of the users for connecting to the iFS repository is:

    • scott/tiger
    • guest/welcome
    • system/manager

    The system is very simple to use and handle. Managers just need to remember the starting URL for iFS access or the Windows SMB network name for mapping over the "Map Network Drive" from Windows Explorer. No additional software need be installed and there is no additional special configuration.

    Conclusion

    IFS promises to save lots of money, after data consolidation and system automatization, using the new file system type. The data can be moved from the separate systems to the Oracle iFS, using
    database technology for security, backup and recovery.
    Is it likely that companies will replace all of those fileserver sitting under people's desks over-night, or that they will convince end users and managers to use the new technology? Probably not.

    » See All Articles by Columnist Marin Komadina

  • Share:
    Home
    Mobile Site | Full Site
    Copyright 2017 © QuinStreet Inc. All Rights Reserved