This discussion is archived
11 Replies Latest reply: Apr 5, 2011 1:47 AM by 841424 RSS

Transfere file from database server to client

841424 Newbie
Currently Being Moderated
Dears

I am generating file in csv format on database server (using custom application)

The file is generated in database server but when trying to copy it to the client PC i receive not transferred where i were doing the same action on the test server (but it was including the database and Apps on the same sever) and it working fine

I am using code like that:


IF NOT webutil_file_transfer.as_to_client ( 'c:\TEMP\'|| 'p_name'|| '.csv' , 'c:\TEMP\'|| 'p_name'|| '.csv')
THEN
MESSAGE ('Not Transferred');
...
...
...
...

The first parameter is the database localhost and the second parameter is the server host (while the application is running using application server on different server)

I need to copy the file from the datable server directly or to copy on Apps server first and then to the Client PC

Can anybody help?

Thanks In Advance
  • 1. Re: Transfere file from database server to client
    841424 Newbie
    Currently Being Moderated
    Where are you friends?
  • 2. Re: Transfere file from database server to client
    Christian Erlinger Guru
    Currently Being Moderated
    Master wrote:
    Where are you friends?
    Weekend? :D

    as_to_client means "Application Server to Client". You generated the file on the database server; as_to_client requires a file on the application server. It worked in the first place as Application Server and Database Server was one and the same machine.

    cheers
  • 3. Re: Transfere file from database server to client
    841424 Newbie
    Currently Being Moderated
    I hope it was happy weekend :)

    but what can i do now to transfer the file?
  • 4. Re: Transfere file from database server to client
    Christian Erlinger Guru
    Currently Being Moderated
    There is db_to_client which transfer the contents of a blob column to the client; however, I don't think this is a suitable way. As it is a simple csv file you could generate it on the middle tier with text_io and then you can use as_to_client

    cheers
  • 5. Re: Transfere file from database server to client
    841424 Newbie
    Currently Being Moderated
    I were trying ( DBMS_FILE_TRANSFER ) package but i got two issues

    First: this package is only executed separately from the package but if i tried to execute within my package i receive ( DBMS_FILE_TRANSFER ) must be declared.

    Second: Although it worked separately, when i tried to copy the file to the AS server it returned the following errors:

    ORA-19504: failed to create file "\\172.16.*.**\d$\tests1234\copy_test.txt"
    ORA-27040: file create error, unable to create file
    OSD-04002: unable to open file
    O/S-Error: (OS 5) Access is denied.
    ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
    ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
    ORA-06512: at line 2

    So.....?
  • 6. Re: Transfere file from database server to client
    Christian Erlinger Guru
    Currently Being Moderated
    Did you lookup in the documentation about dbms_file_transfer ?

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_ftran.htm
    http://www.oracle-base.com/articles/10g/FileTransfer10g.php

    First of all you'd need directory objects as source and target directories, not UNC paths. Then most certainly you started your database server service with the local system account so it cannot access network shares (let alone the administrative d$ share).
    And last but not least:
    The DBMS_FILE_TRANSFER package provides procedures to copy a binary file within a database or to transfer a binary file between databases.
    and
    Files to be copied must be multiples of 512 bytes in size.
    so dbms_file_transfer is definitely the wrong way to copy files, as it most certainly is for copying datafiles between database servers.

    why not keep it simple and generate your file in forms?

    cheers
  • 7. Re: Transfere file from database server to client
    841424 Newbie
    Currently Being Moderated
    Thanks christian so much for the followup and help

    Actually previous developers were creating a custom database package to generate the files using parameters passed from the report form and it created successfully on database server, but please what do you mean with ( generate your file in forms? )

    Regards
  • 8. Re: Transfere file from database server to client
    Christian Erlinger Guru
    Currently Being Moderated
    text_io is for writing files on the application server with forms:
    http://www.oracle.com/webapps/online-help/forms/10g/state?navSetId=_&navId=3&vtTopicFile=f1_help/oraini/c_text_io.html&vtTopicId=

    So you'd simply create your csv file with text_io in a forms procedure, and issue a as_to_client call afterwards.

    If you still want to generate the file on the database server this is a little bit more work to do:

    1.) create a network share for the directory you are creating your file on the database server
    2.) start your application server service with an account which is allowed to access the above network share
    3.) transfer the file from this share with as_to_client (from +\\dbserver1\share1+ to c:\temp or something)

    cheers
  • 9. Re: Transfere file from database server to client
    Aver Explorer
    Currently Being Moderated
    You can try additional solution by using ftp commands.
    Create the file in DB server and run bat file (from forms) that contain ftp commands to transfer file to your client machine.
  • 10. Re: Transfere file from database server to client
    CraigB Guru
    Currently Being Moderated
    Another option would be to create a shared drive on your App Server that points to the directory where the file is created on the Database server. This is how we worked around this issue. This allowed us to produce files through the database and still transfer the file to a client using WebUtil.AS_TO_CLIENT(). Check with your DB and AS Admins to see if this is allowed by your company standards.

    Hope this helps,
    Craig B-)

    If someone's response is helpful or correct, please mark it accordingly.
  • 11. Re: Transfere file from database server to client
    841424 Newbie
    Currently Being Moderated
    Thanks all for help

    A colleague in company suggest to use webutil function called ( webutil_file.copy_file ), actually it working fine and now i can copy file from anywhere to anywhere.

    Although i were receiving error message in Java console like (wut 121 file not transferred by administrator) while using webutil_file.as_to_client, no errors using webutil_file.copy_file and file copied successfully to Client and no need to copy first to AS (while I can do).


    Here is the syntax:

    webutil_file.copy_file (' From_path ' || p_name|| '.CSV',' To_path ' || p_name|| '.CSV' )

    Hope this help others

    Thanks

Legend

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