This content has been marked as final. Show 11 replies
Where are you friends?
Master wrote:Weekend? :D
Where are you friends?
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.
I hope it was happy weekend :)
but what can i do now to transfer the file?
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
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
Did you lookup in the documentation about dbms_file_transfer ?
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?
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? )
text_io is for writing files on the application server with forms:1 person found this helpful
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)
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.
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.1 person found this helpful
Hope this helps,
If someone's response is helpful or correct, please mark it accordingly.
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