Newbie question: Read text file from PL/SQL or SQLPlus

From: Baz <Baz_at_no-spam.com>
Date: Fri, 10 Oct 2003 16:18:06 GMT
Message-ID: <2ZAhb.7432$qK1.6149673@news2.news.adelphia.net>

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" Received on Fri Oct 10 2003 - 11:18:06 CDT