4 Replies Latest reply: Mar 29, 2011 11:03 AM by Billy~Verreynne RSS

    How to create a text file from PLSQL on client machine?

    KevinFitz
      Hello,

      I would like to write a text file to a directory on my client machine from within PL/SQL. As far as I am aware I cannot do this using UTL_FILE package as it will only write files to the server. Can anyone out there suggest a convenient way for me to achieve this aim from within PL/SQL code,

      thanks for reading,

      Kevin.
        • 1. Re: How to create a text file from PLSQL on client machine?
          smon
          You could...

          - map a drive between client & server, and utl_file to the mapped drive.
          - look at using a java proc, i believe there are examples out there (google) to do client file-handling.
          - run an ftp server on your client, then have the server ftp the file over to you.
          - have plsql email the file to you instead?
          • 2. Re: How to create a text file from PLSQL on client machine?
            BluShadow
            KevinFitz wrote:
            Hello,

            I would like to write a text file to a directory on my client machine from within PL/SQL. As far as I am aware I cannot do this using UTL_FILE package as it will only write files to the server. Can anyone out there suggest a convenient way for me to achieve this aim from within PL/SQL code,
            So, you want a database server, to hack across the network, break through security protocols onto a client machines hard drive and write a file there? Does that sound reasonable?

            You could map a drive/directory on the database server so that the client's directory appears to be a local one to the database server, but generally most businesses would consider this to be a breach of security protocols (and common sense). What would make more sense would be to make a directory shared/available on the database server that the client machine(s) can access and apply appropriate user/group security policies against that.

            You're trying to do it backwards to how is normally logical.
            • 3. Re: How to create a text file from PLSQL on client machine?
              327282
              KevinFitz wrote:
              I would like to write a text file to a directory on my client machine from within PL/SQL.
              I guess you want client(s) to be able to access local file(s) interactively, in which case you will need a UI. For e.g. there is a WEBUTIL package that can be used with oracle forms that allows files to be accessed from the client. You can install a simple web form that could be accessed by multiple clients using this package.
              For more info on WEBUTIL, please see: http://www.oracle.com/technetwork/developer-tools/forms/overview/index.html

              You can also consider the other options suggested by smon if you don't mind the file being sent to the client(s) asynchronously.

              Edited by: GG on Mar 29, 2011 10:34 AM
              • 4. Re: How to create a text file from PLSQL on client machine?
                Billy~Verreynne
                KevinFitz wrote:

                I would like to write a text file to a directory on my client machine from within PL/SQL. As far as I am aware I cannot do this using UTL_FILE package as it will only write files to the server. Can anyone out there suggest a convenient way for me to achieve this aim from within PL/SQL code,
                Why from PL/SQL code?

                There's a fundamental client-server concept at stake here. The boundary between client and server.

                What is the client responsibility? It interacts with the client itself, read input using mouse and keyboard. Receive output from the server and render it on the client display device. Etc.

                Where does writing (or reading) the client's local diskdrive fit into this? Is that the client's responsibility or the server's responsibility?

                The line seems to be very clear to me. Client resources, devices and peripherals are the domain of the client - not the server. It does not make any sense ito architecture, control, security and management to allow the server to directly deal with resources and devices in the client's domain.

                PL/SQL code is server code. It executes in the server.

                So does it make sense to use PL/SQL to write directly to the client's local harddrive?

                What does make sense is using PL/SQL to create the file content as a CLOB - and then for the client to read this CLOB and write it to local file. This can easily be done with web clients for example. A web enabled PL/SQL procedure can be called by the client and this procedure, using the WPG_DOCLOAD.download_file() API call, can stream the content to the web client to save as file.