Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

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 5, 2008 at 4:45 am

2 Responses

Subscribe to comments with RSS.

  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…


    June 5, 2008 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:


    June 23, 2008 at 2:13 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: