This discussion is archived
9 Replies Latest reply: Feb 11, 2013 4:02 AM by BillyVerreynne RSS

File transfer from UNIX server to Windows server path

34MCA2K2 Journeyer
Currently Being Moderated
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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Forgive my slackness in marking this answer correct :-(
  • 9. Re: File transfer from UNIX server to Windows server path
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points