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

Baz wrote:
> 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.
> -------------

If the list is already comma separated in the test file, then you can probably do something as simple as this in SQL*Plus ...

get id.txt
append )
0 select * from my_table where id in (
/

> 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?

The correct answer is that the file is opened from the database server so the file must be accessible from there. This still doesn't imply that the file must be physically on that server (NFS for example).

> 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.

To process the file in PL/SQL using SQL*Plus as the interface you could send the contents as a parameter in a procedure call using the same technique I showed above.

--
Richard Kuhler