Read/Write data from/to a file in PL/SQL without using UTL_FILE

Question

Is it possible to read/write data from/to a file in a PL/SQL block without using the UTL_FILE package in Oracle 10g?

I currently have a file containing a set of primary keys (approx. 28000) that I need query a table for additional data that needs to be written out to a file. The schema I am using is very restricted and do not have any create privileges. Also, there are no directories set up with the necessary read/write privileges for the procedures in UTL_FILE to work properly. Asking a DBA to provide additional privileges will take too much time.

Is there a way to work around this problem?

Thanks in advance.

[Edit] I am unable to load the data into a table. Don't have a table to load into or the privileges to create one. I think this is the most difficult part of the problem. How to query the database if the criteria for the query can't conveniently be part of the query itself (i.e. in a separate file)? If number of primary keys is small, I can generate a number of select statements to extract the data in sqlplus (using spool), but 28000 seem to be way too large.

Answer 1

If you cannot get directories set up, I would say you are out of luck. You are asking to read and create files on the database server. This is something your DBA should know about.

Are you sure you need the files on server? Can you load a file from the client into a table (using the usual client-side tools), and read/write data from there?

Update:

I am unable to load the data into a table. Don't have a table to load into or the privileges to create one. If number of primary keys is small, I can generate a number of select statements to extract the data in sqlplus (using spool), but 28000 seem to be way too large.

28000 is not that much. Loop on the client-side.

Answer 2

1. You may be able to use the UTL_TPC package to write to a FTP server using TCP packets.
2. It would be better to load the primary keys to a table before running your package to allow the pl/sql to read/write to the primary keys table.
3. Write a giant pl/sql program with the 20000 keys as insert statements to build an in memory table and use that to query / etc. Then use sqlplus to spool the results. or the mail package to e-mail the results to yourself.