9 Replies Latest reply: Feb 11, 2013 6:02 AM by Billy~Verreynne RSS

    File transfer from UNIX server to Windows server path

    34MCA2K2
      Hello Experts, (Gud Even Gud Aft & Gud Mor)

      Is there a way to transfer file from UNIX server(Oracle database), to Windows server? All I know if Windows path. I am able to read the file as it is on server, however it does not recognize Windows directory at all.

      If you can share some documents around this to study, I will be grateful.

      Regards,
        • 1. Re: File transfer from UNIX server to Windows server path
          JustinCave
          That depends. What API is available to transfer files to the Windows server?

          Can you mount a drive on the Windows server from the Unix server?
          Can you FTP the file to the Windows server? Can you SFTP it?

          Justin
          • 2. Re: File transfer from UNIX server to Windows server path
            Billy~Verreynne
            34MCA2K2 wrote:

            Is there a way to transfer file from UNIX server(Oracle database), to Windows server? All I know if Windows path. I am able to read the file as it is on server, however it does not recognize Windows directory at all.
            Yes it is possible. Samba on Unix sharing the directory containing the file. NFS on the Unix server sharing the directory containing the file. FTP server on Unix allowing the file to be read and copied. OpenSSH on the Unix allowing the file to be read and copied via scp (secure copy).

            But seeing as this is an Oracle database forum (not an operating system forum), and the subject matter is the database server languages SQL and PL/SQL, here is a PL/SQL solution.

            Create a table using the BFILE data type for referencing the files on the Unix o/s. Provide a web enabled procedure for downloading the files via HTTP using a web browser. This procedure will look something as follows:
            --// URL example: http://my-server.my-domain.com/MyDAD/MySchemaName.StreamFile?fileID=1234
            create or replace procedure StreamFile( fileID number ) AUTHID DEFINER is
                    mimeType        varchar2(48);
                    fileName        varchar2(400);
                    lobContent      BLOB;
            begin
                    --// read the LOB from a table (this example uses the
                    --// Apex file uploads table - change it to your own files table)
                    select
                            f.filename,
                            f.mime_type,
                            f.blob_content
                                    into
                            fileName,
                            mimeType,
                            lobContent
                    from    FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ f
                    where   f.id = fileID;
             
                    --// format a basic HTTP header that describes the file stream send
                    OWA_UTIL.mime_header( mimeType, FALSE );        -- e.g. text/csv text/plain text/html image/gif
                    HTP.p( 'Content-Disposition: attachment; filename='||fileName );
                    HTP.p( 'Content-Length: ' || DBMS_LOB.GetLength(lobContent) );
                    OWA_UTIL.http_header_close;
             
                    --// now write the BLOB as a mime stream using the Web Procedural Gateway's
                    --// doc load API
                    WPG_DOCLOAD.download_file( lobContent );
             
            exception when OTHERS then
                    --// Decide what HTML to generate (if at all) if there is a failure
                    --// (usually not a good idea to show database errors to the
                    --// web browser client as that can provide technical details
                    --// that could be useful for exploiting the database)
                    HTP.prn( 'StreamFile() failed with '||SQLERRM(SQLCODE) );
            end;
            / 
            On Windows, IE can be used interactively to copy the file across. For automation (via PowerShell or console jobs), use a command like tool like wget* to download the file.

            If you do not want to use SQL or PL/SQL as the file copy mechanism, then please close this thread (mark as answered) and take your question to an appropriate forum.

             
            <i>* - see http://gnuwin32.sourceforge.net/packages/wget.htm</i>
            • 3. Re: File transfer from UNIX server to Windows server path
              34MCA2K2
              Billy  Verreynne  wrote:

              But seeing as this is an Oracle database forum (not an operating system forum), and the subject matter is the database server languages SQL and PL/SQL, here is a PL/SQL solution.

              Create a table using the BFILE data type for referencing the files on the Unix o/s. Provide a web enabled procedure for downloading the files via HTTP using a web browser. This procedure will look something as follows:
              Glad to see two Oracle Aces answering my question. Thanks a ton to both of you!

              Actually I dont want a Web browser. I want to achieve this using PLSQL. By running a procedure or function, the file should be copied from Unix path to Windows path. I have the utility available which transfers the files within Unix server using UTL_TCP & DBMS_LOB.

              Is it possible to copy file from Unix Server to Windows shared path?

              Regards,
              • 4. Re: File transfer from UNIX server to Windows server path
                Billy~Verreynne
                File sharing is an o/s issue - and something that needs to be raised in an o/s forum.

                E.g. Samba is an Open Source implementation of SMB/CIFS, which enables you to map and share drives with Window servers and clients.

                With Samba you can map the Windows server's shared directory to a Linux directory/mount point. And then use UTL_FILE from PL/SQL to write to that mapped drive.

                Similarly, you can run a FTP sever in Windows and use UTL_TCP to transfer a LOB (or file) from the Oracle server, using PL/SQL.

                So you first need to decide what protocol/interface/method you are going to use for "file transfer" between the Unix and Windows servers - and once that is done, forum members can suggest on how to best use this "file transfer" mechanism from PL/SQL.

                If you are asking about what "file transfer" mechanisms there are, these range from Samba, NFS, FTP, SCP, SFTP, HTTP, and so on. And this is not really the right forum to discuss file transfer/integration between Unix and Windows.
                • 5. Re: File transfer from UNIX server to Windows server path
                  34MCA2K2
                  Oh! Sorry for giving limited info! :-( I want to use FTP protocol. Thanks again for spending your time on a incomplete thread Billy!!
                  • 6. Re: File transfer from UNIX server to Windows server path
                    Billy~Verreynne
                    You can download this PL/SQL package for doing FTP in PL/SQL. It is old, but should still do the job. Keep in mind that in 11g, an ACL needs to be created to allow the code access to the network.

                    An alternative is to script a Unix ftp script and execute that (as an external process) using DBMS_SCHEDULER.
                    • 7. Re: File transfer from UNIX server to Windows server path
                      34MCA2K2
                      I am trying to use the appropriate prog units which suit my req. I think I should be able to do that. I will keep you posted! Thanks again Billy! Really appreciate you spending time on this

                      PS: Db is 10g Enterprise Edition Release 10.2.0.3.0

                      Regards,
                      • 8. Re: File transfer from UNIX server to Windows server path
                        34MCA2K2
                        Forgive my slackness in marking this answer correct :-(
                        • 9. Re: File transfer from UNIX server to Windows server path
                          Billy~Verreynne
                          Slack is good. Pink is bad. "+"Give me liberty, or give me Slack+". ;-) *

                           
                          As long as you mark a thread as answered to indicate to others that you have find some kind of resolution and are not monitoring the thread any more. This saves others from the effort of responding with more details that you will not see or read.

                          It is, and never should be, about forum points.


                           
                          * <i>From the Church of the SubGenius - it's what Scientology would've been if Elron had had any sense of humor.</i>