11 Replies Latest reply: Jun 21, 2012 3:22 PM by Michael Ferrante-Oracle

# How to copy a file from client to DB machine using WebUtil.

We are converting our 10g R1 forms to Forms 11g (Forms [64 Bit] Version 11.1.2.0.0 ).

In or 10g R1 forms we use a bean called oracle.forms.demos.uploadclient.FileUploader to upload files from our client machines to the DB machine and sometimes the app server. We have to select the file (using file open dialog box which is also part of FileUploader) and then give the destination directory (of DB machine) using IP address.

Problem is that this File Upload Bean does not work in 11g Forms. So, we have to use WebUtil.

Problem is, WebUtil has only a utility to copy files from client machine to App. Server machine (Client_To_AS). Documentation says "the upload target directory
must be listed in the transfer.appsrv.write list". Can we put any directory name starting with an IP address in this and use this Client_to_AS function to copy files from client to DB machine???

Or if not how do we transfer files from client machine to any machine in the network in Forms 11g?

Edited by: user12240205 on Jun 8, 2012 3:58 AM
• ###### 1. Re: How to copy a file from client to DB machine using WebUtil.
Two things...

First, your own bean probably still works. It just needs to be recompiled against the newer Forms version. Look at Note 559213.1

Second, WebUtil offers up and download to and from the middle tier and db. Keep in mind that "to the database" means "into" the db and not to the file system.

Refer to the WebUtil reference documentation included in the Forms Builder online help. You can also refer to the WebUtil demo found here:

http://www.oracle.com/technetwork/developer-tools/forms/downloads/index.html

Edited by: Michael Ferrante on Jun 8, 2012 12:15 PM
• ###### 2. Re: How to copy a file from client to DB machine using WebUtil.
Michael Ferrante wrote:
Two things...

First, your own bean probably still works. It just needs to be recompiled against the newer Forms version. Look at Note 559213.1

Second, WebUtil offers up and download to and from the middle tier and db. Keep in mind that "to the database" means "into" the db and not to the file system.

Refer to the WebUtil reference documentation included in the Forms Builder online help. You can also refer to the WebUtil demo found here:

http://www.oracle.com/technetwork/developer-tools/forms/downloads/index.html

Edited by: Michael Ferrante on Jun 8, 2012 12:15 PM
Michael, your reply did not help that much. I understand you are working @ Oracle and is in the Forms team. So I think you are the perfect person for this.

I downloaded the WebUtil demo for 11g and went through the documentation. You can transfer files only like this:
(1.) File Upload - Client file to DB
(2.) File Upload - Client file to AS.
(3.) File Download - DB to client.
(4.) File Download - AS to client.

So using WebUtil there is no function to transfer a file from client machine to DB machine.

We cannot use old FileUploader Bean since our mgt. has decided not to use it, but to go with WebUtil.

So, how can we achieve this? I think this could be a common problem for lots of Forms developers. i.e. How to transfer a file from client machine to DB machine???
• ###### 3. Re: How to copy a file from client to DB machine using WebUtil.
As I mentioned, WebUtil does offer the ability to move files "into" the db but not directly onto the database filesystem. If you want to do this you will need to either create your own bean, create a mapped drive (network share), or use some scripting method to move the files from one place to another. Moving the db tier is not something that we often get as a request. However, we do sometimes get requests which are more general. In other words, how to I move a file from the client machine to any other machine? Well, the answer is the same as what I mentioned.

If you really want to move files from the client to the db tier you have many options. Here are just a few (some already mentioned):

1. Create a mapped drive on the client so the files can be written directly from the client to the db tier. I don't recommend this for a variety of security and performance issues, but it can be done.

2. Create a mapped drive on the middle tier and use WebUtil's CLIENT TO AS option to temporarily move the file to the mid tier then move it from AS to DB through the mapped drive. I don't recommend this for a variety of security and performance issues, but it can be done.

3. Use WebUtil's CLIENT TO AS option to temporarily move the file to the mid tier then move it from AS to DB using FTP

4. FTP directly from the client to the db tier.

5. Write your own custom bean. See the following example:

http://forms.pjc.bean.over-blog.com/article-23800297.html

Keep in mind that the basic concept of the "Forms" product is to create applications which act as electronic "forms" for data entry. It originally was not intended to be used as an ftp client, document editor, image editor, etc. Over the years, Oracle has added features to the Forms product (such as those in WebUtil) which help it to perform some of these tasks, but with the idea that this would not be the primary use of the application being created. In other words, we added file upload/download, but this doesn't mean you should write an application that acts primarily as an ftp client or one which is expected to move 4gig files from machine to machine. In this example, you would simply be using the wrong tool for the job. File transfer is what ftp is for.

.
.................................................................................................................................
The views expressed in this posting are my own and do not necessarily reflect the views of Oracle.
.................................................................................................................................
• ###### 4. Re: How to copy a file from client to DB machine using WebUtil.
Hi Michael, thanks for your lengthy explanation.

This morning I was working on this and came up with this solution.

(1.) I use WEBUTIL_FILE.FILE_SELECTION_DIALOG to get the filename from the client machine.
(2.) I use the file name and then use that as an input to WEBUTIL_FILE_TRANSFER.Client_To_DB to get it straight to the DB.
(3.) Then I use UTL_FILE and DBMS_LOB to write the BLOB as a text file in the directory of the DB machine.

Is this OK?? Are there any issues on this?? If this is OK then I can use this to create a procedure to mimik the FileUploader which we used upto now.

If we use FTP and drive mapping we might have to do a major code change to every form.
• ###### 5. Re: How to copy a file from client to DB machine using WebUtil.
Sounds like a reasonable plan. The only suggestion I would make is that if you plan to call any db packages/procedures you should make those calls from within your own custom db package so that the form does not call them directly. In other words, try to avoid making calls directly to the db standard packages from the form's code. Calling directly can be done, although not technically supported. This will ensure the highest level of compatibility especially in cases where the db may be upgrade or patched along the way.
• ###### 6. Re: How to copy a file from client to DB machine using WebUtil.
Michael, I finally got a-z working code. I tested this in my Oracle 11g DB (in my Windows machine) in my PC and works fine. Now, I deployed the code in the production DB which is a Linux box. I ran it, the file is created in the Linux box directory, BUT the file is empty. Data is not in the file.
Here is my code to write to the HDD:
PROCEDURE temp_blob_write_to_file
(
primary_key  IN temp_blob_tab_for_fileupload.rownumber%TYPE,
ora_db_obj IN VARCHAR2,
p_file_name  IN VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_file      UTL_FILE.FILE_TYPE;
l_buffer    RAW(32767);
l_amount    BINARY_INTEGER := 32767;
l_pos       INTEGER := 1;
l_blob      BLOB;
l_blob_len  INTEGER;
BEGIN
-- Get LOB locator
SELECT the_file  INTO   l_blob
FROM   temp_blob_tab_for_fileupload  WHERE  rownumber = primary_key;

l_blob_len := DBMS_LOB.getlength(l_blob);

-- Open the destination file.
l_file := UTL_FILE.fopen(ora_db_obj, p_file_name,'w', 32767);

-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;

-- Close the file.
UTL_FILE.fclose(l_file);

-- Delete row
-- DELETE FROM temp_blob_tab_for_fileupload  WHERE  rownumber = primary_key;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
Problem is, since it is working in one environment (Windows), I am finding it difficult to find out why this does not work in Linux. Even the file is being created without DATA. There is no error thrown also.

What could be wrong here?
• ###### 7. Re: How to copy a file from client to DB machine using WebUtil.
Likely, when the upload occurs from the form, it is important that a "commit" be sucessfully executed. This was actually a problem we saw with the original webutil demo too, which has now been corrected. We were not performing a commit and therefore the data was only available within the session as a result of the post, but not available outside of the current Forms session.
• ###### 8. Re: How to copy a file from client to DB machine using WebUtil.
Michael Ferrante wrote:
Likely, when the upload occurs from the form, it is important that a "commit" be sucessfully executed. This was actually a problem we saw with the original webutil demo too, which has now been corrected. We were not performing a commit and therefore the data was only available within the session as a result of the post, but not available outside of the current Forms session.
Thanks michael. You are right. Commit after the WebUtil call did the trick.

But that method where we put to the DB and from there to the DB HDD takes lot of time and overhead.

So instead we came up with the correct method. I.e. We use client_to_as and "trick" webutil to actually transfer to the DB HDD. This is what we did.
(1.) Configure a Samba share in the DB machine, so that the share can be accessed as a Windows folder.
(2.) Add the following to the webutil.cfg file found in
<drive>\Oracle\Middleware\asinst_1\config\FormsComponent\forms\server

transfer.database.enabled=TRUE
transfer.appsrv.enabled=TRUE
transfer.appsrv.workAreaRoot=c:\temp
transfer.appsrv.accessControl=TRUE
#List transfer.appsrv.read.<n> directories
transfer.appsrv.read.1=\\192.168.10.218\FileLoadDir
#List transfer.appsrv.write.<n> directories
transfer.appsrv.write.1=\\192.168.10.218\FileLoadDir

Note: above UNC is maped to /u01/FileLoadDir in Linux DB server machine.
For testing purpose full read + write permissions are given.

(3.) In the form you can use this code:
DECLARE
v_success                                           BOOLEAN DEFAULT FALSE;
BEGIN
v_success := WEBUTIL_FILE_TRANSFER.Client_to_AS
(
'C:\TEMP\abc.txt',
'\\192.168.10.218\FileLoadDir\customer\DATA_FILES\def.txt', /* you can change the file name also */
FALSE,
NULL
) ;
END;

-- So now when you transfer from Client to AS (client_to_as) it actually goes to to the DB HDD.
You can use this method to transfer a file in the client machine to ANY MACHINE hard disk directory in the network.
• ###### 9. Re: How to copy a file from client to DB machine using WebUtil.
Glad to hear you were able to get it working. However, just be aware that generally Oracle Support (for Forms) may not be able to support such a configuration. Access to mapped or shared resources is not recommended or supported in most cases. You should consider using FTP. You can execute ftp from the client and move the file directly from the client to the db server. All you need to do is enable an ftp server on the destination machine.

Refer to MyOracleSupport note 430222.1 for an example. In the example it uses the HOST built-in, however in your case you would use WebUtil's CLIENT_HOST.
• ###### 10. Re: How to copy a file from client to DB machine using WebUtil.
Michael Ferrante wrote:
However, just be aware that generally Oracle Support (for Forms) may not be able to support such a configuration. Access to mapped or shared resources is not recommended or supported in most cases. Y...
Michael, could you elaborate as to why it is not recommenced?? Is it due to security implications?

In mapped method:
(1.) DB has file share.
(2.) We have to give full access to the share. If we create uid/pwd for share then we cannot connect internally in the form. Since we have not figured out how to. This is drawback I agree.

In the FTP method:
(1.) Ftp server has home directory and has uid/pwd created.
(2.) We have to execute the FTP script in the client machine. BUT, here also we have to give the FTP server uid/pwd in the script file??? Since there is no mechanism to pass parameters to the script file (I think), we have to create the file dynamically. This is technically possible.
(3.) I don't think we have to give permission to the directory (in DB) to outsiders.

So, is it due to fact the we have to give full permission on the directory in the mapped method??

Why exactly is this not recommended??
• ###### 11. Re: How to copy a file from client to DB machine using WebUtil.
There are a variety of reasons why using shares is not recommended and in some cases not supported. I will not go into detail, but here are just a few reasons:

1. Some areas of the Forms product are not designed (not supported) to handle accessing remote systems. In most cases, mapped drives (e.g. Z:\) will work, but UNC naming (\\machine\share...) will fail.

2. There are known performance issues associated with accessing remote drives. This has nothing to do with Oracle, but is the nature of accessing remote resources

3. There are known security vulnerabilities associated with file sharing

4. There really is no good way to test if a share is available. It can be done, but because of #2 the application can appear stalled while testing for the availability of a drive

There are others...

One idea came to mind, although only a theory at this point. If you create a database procedure in java, you may be able to call that proc from forms. The db proc can be responsible for pulling the file from the mid tier to the db server. Here is one example. Google will offer many more:

http://www.roseindia.net/java/example/java/io/file-url-download.shtml

Since there is a web listener running on the mid tier, if the db calls the proper URL the file can be pulled and saved on the db server. Again, I have not tested this, but it should be possible. Of course you may need to create a virtual path on the mid tier which points to the file location. Also, it is possible to restrict access to the directory so that only the db machine's IP will gain access.

https://httpd.apache.org/docs/2.2/howto/access.html