Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!rip!news.webusenet.com!router1.news.adelphia.net!router2.news.adelphia.net!news2.news.adelphia.net.POSTED!not-for-mail
From: "Baz" <Baz@no-spam.com>
Newsgroups: comp.databases.oracle.misc
Subject: Newbie question: Read text file from PL/SQL or SQLPlus
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <2ZAhb.7432$qK1.6149673@news2.news.adelphia.net>
Date: Fri, 10 Oct 2003 16:18:06 GMT
NNTP-Posting-Host: 67.20.101.166
X-Complaints-To: abuse@adelphia.net
X-Trace: news2.news.adelphia.net 1065802686 67.20.101.166 (Fri, 10 Oct 2003 12:18:06 EDT)
NNTP-Posting-Date: Fri, 10 Oct 2003 12:18:06 EDT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:132194

Problem:
-----------
I would like to read a list of IDs from a text file on the client machine
and fetch records for those IDs from the database. The client is a win2k
machine with SQLPlus and TOAD. installed on it. The database is on a unix
server.
-------------

I tried using the UTL_FILE package for reading the file from the win2K OS in
a simple procedure that just opens the file, reads the file and writes out
the text read. I get errors when I execute the procedure (given at the end
of the message). I did some research on using UTL_FILE  and I am getting
conflicting information about the use of this package. Some say that it is
for use only on the server OS and some say that it can be used on the client
side OS files as well. Could somebody please tell me which is correct?

What would be other ways of reading this client-side text file in a PL/SQL
or SQL script? I understand that you can use Java or C code to do this but
just wondering if there are any other packages/utilities that can accomplish
this.

Any help would be appreciated!

CREATE OR REPLACE PROCEDURE test_read
IS
file_handle UTL_FILE.FILE_TYPE;
l_text varchar2(100);
BEGIN
file_handle : = UTL_FILE.FOPEN('c:\temp', 'temp.txt', 'R');
LOOP
  utl_file.get_line(file_handle, l_text);
  dbms_output.put_line(l_text);
END LOOP;
utl_file.fclose(file_handle);
end;

I get the following error:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE"