MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

How you can read an external directory list from SQL

with 2 comments

A post last week in the SQL & PL/SQL Forum caught my eye because it referenced an old post by Tom Kyte. That post shows you how to read an external file system directory using Java library wrapped by a PL/SQL program unit. The problem I have with the solution is that it writes the data to a table, and then it reads the file list from the table. This type of design requires cleaning up the table after running the function or procedure.

An improvement on Tom’s old solution would be to return the list as a SQL collection data type. A few searches on the Internet and of the Oracle documentation didn’t unearth an example. The referenced code and instructions show you how to implement the necessary pieces with a PL/SQL wrapper function.

Written by maclochlainn

June 5th, 2008 at 4:45 am

2 Responses to 'How you can read an external directory list from SQL'

Subscribe to comments with RSS or TrackBack to 'How you can read an external directory list from SQL'.

  1. How about just use a global temporary table with ON COMMIT DELETE (or whatever the syntax is)?

    I had to do something similar awhile back…

    chet

    5 Jun 08 at 6:34 pm

  2. While Chet is correct, why should we create a table and perform I/O when it can be avoided. Global temporary tables came along in Oracle 8i. There’s a good article at:
    http://www.oracle-base.com/articles/8i/TemporaryTables.php

    maclochlainn

    23 Jun 08 at 2:13 am

Leave a Reply