Solved

Client side file I/O with PL/SQL

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
grouping on time windows 6 51
How do I call MySQL Stored Procedure from oracle using HS link ? 5 39
SQL query question 8 73
Creation date for a PDB 5 18
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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

815 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

8 Experts available now in Live!

Get 1:1 Help Now