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
Solved

Client side file I/O with PL/SQL

Posted on 2007-11-21
5
2,361 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 87
UTL_FILE invalid file operation 5 24
Loading flat file data in tables 2 40
Migration from SQL server to oracle (XML input) 4 24
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

839 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