Solved

Client side file I/O with PL/SQL

Posted on 2007-11-21
5
2,446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 74

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 74

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 74

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

717 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