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

[Baz]

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"

[Sybrand Bakker]

On Fri, 10 Oct 2003 16:18:06 GMT, "Baz" <B...@no-spam.com> wrote:

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

utl_file is for the server only

As you mention no version an accurate answer can't be provided as to
the alternatives

--
Sybrand Bakker, Senior Oracle DBA

[Baz]

Oracle 9i Release 9.2.0.4.

"Sybrand Bakker" <gooid...@sybrandb.demon.nl> wrote in message
news:sktdovsopbvf690go...@4ax.com...

[Richard Kuhler]

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

[Baz]

Oracle 9i Release 9.2.0.4.

"Sybrand Bakker" <gooid...@sybrandb.demon.nl> wrote in message
news:sktdovsopbvf690go...@4ax.com...

[Baz]

Thanks for the tip. I tried using the append method & it works perfectly! :)
Is there a limit on the number of characters that can be read from the file
or appended?

"Richard Kuhler" <no...@nowhere.com> wrote in message
news:3cDhb.7565$Z86....@twister.socal.rr.com...

[Richard Kuhler]

Baz wrote:
> Thanks for the tip. I tried using the append method & it works perfectly! :)
> Is there a limit on the number of characters that can be read from the file
> or appended?

There shouldn't be any practical limit on the file size other than the
O/S and sql buffer memory limits (which are usually extremely high).
However, there is a more constraining limit of 1000 items in a single in
list that may affect you (you'd have to split it into multiple lists
with an OR condition between them).

Note: I'm assuming you're using this for an occasional query. If this
list changes a lot and you're going to run lots of these queries then
the sql area is going to be strained which may adversely affect the
performance of your entire system (the standard argument for using binds).

--
Richard Kuhler

[Baz]

As of now, I am not sure how often I will be using this. But I guess
clearing out the buffer might help with the performance issue. Please
correct me if I am wrong...

"Richard Kuhler" <no...@nowhere.com> wrote in message

news:A5Ghb.8056$Z86....@twister.socal.rr.com...

[Richard Kuhler]

Baz wrote:
>>There shouldn't be any practical limit on the file size other than the
>>O/S and sql buffer memory limits (which are usually extremely high).
>>However, there is a more constraining limit of 1000 items in a single in
>>list that may affect you (you'd have to split it into multiple lists
>>with an OR condition between them).
>>
>>Note: I'm assuming you're using this for an occasional query. If this
>>list changes a lot and you're going to run lots of these queries then
>>the sql area is going to be strained which may adversely affect the
>>performance of your entire system (the standard argument for using
binds).
>

> As of now, I am not sure how often I will be using this. But I guess
> clearing out the buffer might help with the performance issue. Please
> correct me if I am wrong...

Well, it depends on what you mean by 'buffer'. I'm referring to the sql
area so you'd have to flush the shared pool (alter system ...). That's
something that you really just don't want to be doing. If you're going
to do this very frequently (hundreds or thousands of times a day) or you
have a small shared pool then you need to engineer this to use bind
variables. That means you'll need to use something other than SQL*Plus
as well since there's no method for using it's version of binds without
abusing the sql area to some extent.

--
Richard Kuhler

[Ubiquitous]

In article <2ZAhb.7432$qK1.6...@news2.news.adelphia.net>, B...@no-spam.com
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.
>-------------
>

> [...]

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

Is there really a directory "C:\temp" on the server? Perhaps adding the
standard UTL_FILE EXCEPTION statements would help clarify things?

--
======================================================================
ISLAM: Winning the hearts and minds of the world, one bomb at a time.

[Frank]

Ubiquitous wrote:

Don't think so - it's a Unix box, according to the original thread.
--
Regards, Frank van Bortel