Posted on 2002-06-25
Last Modified: 2007-12-19
hai everybody,

Can anybody provide me an idea of how to go about with a FTP proj in D2K. Basically i wanna tranfer my files from the local directory to the remote FTP server. Is there any code or any site which help me in assiting.

Thanxs in advance
Question by:delphipal
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 48

Expert Comment

ID: 7106832
You could use the 'text_io' package in forms to create an ftp script and then use the 'host' command
to execute ftp, giving it the name of the script that created.

Another known way is to install Oracle ifs(internet file system), which allow ftp,snmp,...

The following procedures have been established for Oracle Worldwide Support
customers to upload or download files to/from our external ftp site.

Customers should use and be familiar with an FTP tool.

     Logon Info Required Syntax
     FTP IP address:
     USERID: anonymous
     PASSWORD: customer's email id. *NOTE: this must be a valid email id.*

1. Change to the directory for your competency/incoming:(apps, server)
for example:
ftp> cd server/incoming
2. Make a directory named with your current tar number:
ftp> mkdir 12345678.600
3. Change into that new directory:
ftp> cd 12345678.6
4. Set the transfer type to binary
ftp> binary or bin
5. Upload the files
ftp> put file_name or mput *

Uploading a file in this fashion does not notify the analyst or update the tar. You
must call back on the tar after you have uploaded the file(s) so that the analyst assigned to your tar can retrieve the files.

1. Change to the directory for your competency/outgoing:(apps, server)
for example:
ftp> cd server/outgoing
2. Change to the directory that your Support Analyst has given you.
For example:
ftp> cd tar10498374.600
3. If you are downloading current patchsets for server, you can query on your
own for the platform, oracle version, and patchset version. Begin in the
server/patchsets directory.
For example:

Most NT patches will be under the server/patchsets/wgt_tech/server/windowsNT/
directoy.  Most Netware patches will be under /server/wgt_tech/server/netware/.

4. Set the transfer type to binary
ftp> binary or bin
5. Download the files
ftp> get file_name or mget *

init<SID>.ora parameters, affecting on memory

1. If you don't use JAVA virtual machine type java_pool_size=0;
2. db_block_buffers=N*db_block_size;
3. shared_pool_size;
4. sort_area_size;
5. sort_area_retained_size;
6. log_buffer;
LVL 48

Expert Comment

ID: 7106842
There are times where it would be useful to be able to move files across systems from within a PL/SQL program. The usual tool for doing this is the ftp command. There are at least 4 ways to realize this.

The first one is to interface the O/S ftp command to the database so it can be used from within PL/SQL. An implementation of this alternative will likely use an external procedure to spawn the program. Actually, such a solution already exists, see Note:111780.1. Another implementation will be found in a future article.

The second one is to grab a library offering an ftp client API callable from C. A quick search on the Web brought back several hits, among which FCE4C "a library of functions providing direct and simple control of the FTP protocol". This is a shareware available from

The third way is to use the functions in PL/SQL packages utl_tcp and utl_http, either to realize ad hoc file retrieve/put functionality or to (re-)implement the ftp protocol. This can be a fun project but requires some insight of its innards as described in the RFC 959 document, far too more than an application programmer cares to know about.

The fourth one is to publish the java class FtpClient from the package, contained in the JDKs rt.jar library. This solution is similar to the second one but for java. While the first is more universal because it is based on the external procedure mechanism existing since Oracle v8, the fourth one is cleaner because it relies on the built-in JVM that exists since Oracle v8i, and is therefore an internal solution. There is one catch though: as it uses the Sun's package, which is not part of the JDK and is not supported by Sun, there is no warranty nor official documentation. However, it is easy to use and seems to work fine so far  minus one small glitch, see below  and there are free alternative implementations of it in source form on the Internet. See for a FTPClient implementation, and for its documentation.

In this 3-part article, I have chosen the fourth alternative. I am presenting its implementation here and will provide the code listing in Note.159061.1 and Note.159062.1.

Interfacing to FtpClient
This java class belongs to the Suns package It conveniently exports the following main methods: openServer() and closeServer(), login() (log in onto a given host), get() (retrieve a list of remote files), put() (store a list of files to the remote host), list() (get the current directorys list of entries), binary() (set file transfer to binary), and ascii() (set file transfer to ASCII), and cd() (change to directory). As no mget() is exported by the class, this function is not available, though it could relatively easily be implemented using list() and get().

As usual with java in the database, the FtpClient class will be invoked through static public methods published as stored procedures (JSP). Because of this, a stateless java interface has to be written, which one can take profit of to make it more synthetic and comfortable. Here are the retained public methods:

   static public String FTPGet(String ServerName,
                               String UserName,
                               String Password,
                               String SourceFileList,
                               String FileTypeIsBinaryList,
                               String DestFileList,
                               String ListSeparator);

   static public String FTPGet(String ServerName,
                               String UserName,
                               String Password,
                               String[] SourceFiles,
                               boolean[] FileTypeIsBinary,
                               String[] DestFiles);

   static public String FTPPut(String ServerName,
                               String UserName,
                               String Password,
                               String SourceFileList,
                               String FileTypeIsBinaryList,
                               String DestFileList,
                               String ListSeparator);

   static public String FTPPut(String ServerName,
                               String UserName,
                               String Password,
                               String[] SourceFiles,
                               boolean[] FileTypeIsBinary,
                               String[] DestFiles);

   static public String FTPDir(String ServerName,
                               String UserName,
                               String Password,
                               String RemoteDir);

The functions return a string which is the eventual error message, or an empty string if no error occurred.

FTPGet() and FTPPut() functions are overloaded because PL/SQL index-by tables cannot currently be passed to java (an error "PLS-00999: implementation restriction (may be temporary) INDEX TABLE parameters are disallowed" is issued). Therefore, lists of files to put or get are passed as strings containing file names separated by a given separator string. On the java side however, it is more natural to resort to array of file names as collection, so a higher-level profile is also given.

The functions profile is quite intuitive and does not need any explanation except parameter FileTypeIsBinaryList. This is a string of ListSeparator-separated "T" or "F" characters which respectively stand for TRUE and FALSE. This is needed because PL/SQL BOOLEAN type is not mappable to java boolean.

To be used as JSP, the above static methods need to be interfaced to PL/SQL through PL/SQL functions, which do not need to be public. Here is the profile of the publicly accessible functions:

                   UserName STRING,
                   Password STRING,
                   SourceFiles STRING_TABLE,
                   FileTypeIsBinary BOOLEAN_TABLE,
                   DestFiles STRING_TABLE) RETURN STRING;

                   UserName STRING,
                   Password STRING,
                   SourceFiles STRING_TABLE,
                   FileTypeIsBinary BOOLEAN_TABLE,
                   DestFiles STRING_TABLE) RETURN STRING;

                   UserName IN STRING,
                   Password IN STRING,
                   RemoteDir IN STRING,
                   DirList OUT STRING_TABLE) RETURN STRING;

                   UserName IN STRING,
                   Password IN STRING,
                   RemoteDir IN STRING) RETURN STRING;

The profiles are closely related to their java counterparts.

Function FTPGet() connects to the remote machine whose host name is in ServerName using user account UserName/Password. If successful, it attempts to get the files whose names are in the PL/SQL index-by table of strings SourceFiles, and whose type (binary or ASCII) is contained in the PL/SQL index-by table of booleans FileTypeIsBinary (entry at index i is TRUE iff SourceFiles(i) is binary). The retrieved files are stored in the PL/SQL index-by table of strings DestFiles.

Index-by tables were chosen as collections here because they support the PL/SQL BOOLEAN data type and despite they dont have constructors (each item in the table must be individually assigned, this cannot be done globally in one shot). Alternatively, higher-level nested tables of user-defined types could be used here but such types must be declared and defined in the database outside of PL/SQL packages, and they do not support the BOOLEAN data type. They have a constructor though which makes it nicer to initializes nested table literals. The reader is free to choose the interface s?he bests sees fit.

FTPPut() works similarly, excepted that it copies the files passed in SourceFiles onto the remote host as files whose names are in DestFiles.

SourceFiles and DestFiles must obey the file name syntax of their respective host platforms. E.g. when getting files from a Unix host into a PC host running NT, SourceFiles must be a / delimited list of sub-path names whereas DestFiles must be a \ delimited list of sub-path names with an optional starting drive letter: sequence.

As PL/SQL index-by tables can be sparse, there is no guarantee that their first element is at index 0 or even 1. Therefore, instead of coercing the user to some arbitrary starting index or to dense tables only, I leave up to (him)|(her) this choice. Internally, functions Table.FIRST, Table.LAST, Table.EXISTS(index) are used to accommodate that freedom, which only slightly complicates the code.

FTPDir() exists in 2 flavors. The first version gets a directory listing of the files in the directory RemoteDir and returns it in raw format, i.e. the same as the one returned by the ftp "dir" command. Each line contains a file name preceded by its permissions, owner name, group name, size, and last access date. All the linefeed-separated lines are concatenated together into one string. The caller is responsible for parsing it and extracting the information of interest. The second flavor partially parses the raw output and returns it into a PL/SQL index-by table, one file entry per table element.

The functions return an error message if an error occurs, or an empty string if all the files could be processed correctly. If an error occurs, the current file plus the rest of the files are discarded and the functions return immediately. Programmatically, this was the easiest thing to do and justifies itself by the fact that a transfer error usually means a permission issue, a disk full or a network problem, which jeopardizes all subsequent file transfer as well so that an abort is the most sensible thing to do here.

Permission considerations
As resources such as TCP/IP sockets and files are being accessed by java functions, special permissions are required. Firstly, if the remote host is accessed through its hostname, a DNS must be queried to get the hosts IP address. This requires the resolve permission. Secondly, sockets operations such as accept(), listen(), and connect() also demand adequate permissions. Thirdly, files accessed remotely to be copied locally require the write permission and files accessed locally to be copied remotely require the read permission. All those permissions must be granted to the calling user through the function dbms_java.grant_permission().

Examples of use
In this example, all the permission granting stuff is done automatically since the running user name is grabbed from the sessions environment and the file names to be granted permission on are obviously known at run-time.
As error messages and the directory listing must be printed by the SQL*PLUS environment (see below why), host string variables are used.

CONNECT scott/tiger

exec dbms_java.set_output(5000);

VAR DirFiles VARCHAR2(4000);
VAR ErrorMessage VARCHAR2(4000);

    DummyKey NUMBER;
   -- grant required socket permissions;

   -- get list of files;
   :ErrorMessage := FTP.FTPDir(ServerName => '',
                               UserName => 'osupport',
                               Password => 'password',
                               RemoteDir => '/usr/users/osupport',
                               DirList => :DirFiles);

   :ErrorMessage := FTP.FTPDir(ServerName  => '',
                               UserName    => 'osupport',
                               Password => 'password',
                               RemoteDir   => '/usr/users/osupport/ccervini',
                               DirList     => DirList);
   FOR I IN 1 .. DirList.COUNT LOOP

   -- get files;
   S(1) := '/export/home/osupport/ccervini/1-65.pdf';
   B(1) := true;
   D(1) := '/u02/home/usupport/ccervini/1.pdf';

   S(2) := '/export/home/osupport/ccervini/KO11.xls';
   B(2) := true;
   D(2) := '/u02/home/usupport/ccervini/3.xls';

   S(3) := '/export/home/osupport/ccervini/Anatomy_of_an_Asp.pdf';
   B(3) := true;
   D(3) := '/u02/home/usupport/ccervini/2.pdf';

   -- test sparse table here;
   S(10) := '/export/home/osupport/ccervini/ODCI/';
   B(10) := false;
   D(10) := '/u02/home/usupport/ccervini/';

                                    ' size=+1>                                    D(I),
      END IF;
   :ErrorMessage := FTP.FTPGet(ServerName => '',
                               UserName => 'osupport',
                               Password => 'password',
                               SourceFiles => S,
                               FileTypeIsBinary => B,
                               DestFiles => D);

   -- put files;
   S(1) := '/u02/home/usupport/ccervini/RegularExpressions.c';
   B(1) := false;
   D(1) := '/usr/users/osupport/100.c';

   S(2) := '/u02/home/usupport/ccervini/analyze.sql';
   B(2) := false;
   D(2) := '/usr/users/osupport/101.sql';

   S(-10) := '/u02/home/usupport/core';
   B(-10) := true;
   D(-10) := '/usr/users/osupport/ccervini/102';

                                    ' size=+1>                                    S(I),
      END IF;
   :ErrorMessage := FTP.FTPPut(ServerName => '',
                               UserName => 'osupport',
                               Password => 'password',
                               SourceFiles => S,
                               FileTypeIsBinary => B,
                               DestFiles => D);

print DirFiles
print ErrorMessage

In the above demonstration block, the error message is returned into a SQL*PLUS host variable. This is because it was not always possible to use the dbms_output.put_line() function as the string often was too large (larger than 255 characters). Thus, this output task is left to the SQL*PLUS client. There is a java implementation of put_line() that works around those limits. Please see Note:136486.1.

The reader is invited to play with different combinations of host O/S, account and file status (existing/non existing, binary/ASCII, existing/non existing user, correct/wrong password) to see which error messages are returned.

A small glitch in Sun's class FtpClient
As is, method FtpClient.get() does not handle well multiple file cases, and fails with a java.lang.NullPointerException error after the first file was successfully retrieved.

The bug is on the last line of FtpClient.get(), i.e. instead of
   return new FtpInputStream(this,
it should read:
   return new TelnetInputStream(socket.getInputStream(),

Once this change is made and the whole class recompiled and reloaded, the method works as expected.

The original code can be gotten from the following URL: size=+1>References
For the full code of the java and PL/SQL interfaces, please check Note.159061.1 and Note.159062.1.
For an alternative implementation of FTPClient, see source code and documentation here:

Author Comment

ID: 7106981
i want this in Developer 2000.u seems to be using JAVA alot. can u help me out in Developer 2000
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

LVL 35

Expert Comment

by:Mark Geerlings
ID: 7107388
This Java-based approach looks like a good way to go.  I haven't tried it yet, but I hope to soon.  If you want to do this without Java though, just test all of the commands you need, then put them into a batch file and run the batch file via a "host" command from a Forms PL\SQL trigger or program unit.  If you want some flexibility, you may be able to do this with some parameters that you pass to the batch file, otherwise you will have to use file_io to first write the batch file, then use a "host" call to run it.
LVL 48

Accepted Solution

schwertner earned 100 total points
ID: 7107697
As mentioned the easiest way to di this in Forms is
to use the 'text_io' package in Forms to create an ftp script and then to use the 'host' command to execute ftp, giving it the name of the script that created.

The package TEXT_IO is the standart package of Forms and Reports for  manipulating O/S text files. HOST is a command of Forms to execute commands of the O/S.

Expert Comment

ID: 9193028
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accpet schwertner's comment as answer
Please leave any comments here within the next seven days.
EE Cleanup Volunteer

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question