Solved

Client side file I/O with PL/SQL

Posted on 2007-11-21
5
2,257 Views
Last Modified: 2013-12-07
I would like to be able to read and write files on a Windows workstation from a PL/SQL application.  It appears that UTL_FILE runs on the server side and does file I/O to the server's files, not the client.  Is there a way to write PL/SQL that will run on the Windows workstation and read/write files on that system?
0
Comment
Question by:dkreines
  • 3
5 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 20328960
not unless your client has a file share that the db server can also write to.   A Samba share or NFS
0
 
LVL 6

Expert Comment

by:SJT2003A
ID: 20329195
If it is not longer the 1MB, may be you like chose to write using DBMS_OUTPUT package as we use for debugging.
DBMS_OUTPUT.PUT_LINE would direct the output to redirected also. Make sure the SET SERVEROUTPUT ON.

Check it out. Good luck :)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20329472
if it's 10gR2 you don't have an upper limit on how much data you can spool out and the per-line limit has been raised to 32K as well.


You can also try utl_tcp with a listener program on the client that reads from the tcp port and writes the data to a file.

A client app listening on a dbms_pipe would also works.

you could also write the file out on the server with utl_file then ftp to your windows machine, provided you have an ftp server


0
 

Author Comment

by:dkreines
ID: 20329726
I guess I should clarify my problem a bit more.  It is probably more important that I be able to read from files on the client.  There is an existing client application (which I cannot access or change) that downloads files to be processed to a directory on the workstation.  My program must:

1. Open a file on the workstation (downloaded file)
2. Process the contents into the database
3. Close the file and see if there is another - if so, then go back to 1

Another program will:

1. Process rows from the database
2. Create an output file on the workstation and write to it
3. Go back to 1 for the next set of rows.

When that is finished, the packaged app runs to send the output files on.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 20330022
yes, that's fine.  What I've suggested would be the "other program", which, depending on the option you pick, may involve a 3rd program, either a tcp port listener, and ftp server, or dbms_pipe listener.

Or, if you have a shared file system (samba/nfs) you can write directly from the server to the share and read from the share with your client
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now