How to FTP with Oracle PL/SQL: Oracle, FTP, PL/SQL

Sometimes you need to get data from a server into your database, or from your database into a remote server. There are many ways this can be done such as SOAP or REST web service calls, database links, pipes, or external tools. Another option, illustrated here, is to use the internet standard File Transfer Protocol (FTP).

In the attached code snippets I have included a PL/SQL package that implements most of the FTP protocol, including local/remote file transfer in ascii and binary formats, remote directory navigation and manipulation and some modified features for local actions. Directories in Oracle are independent objects and do not have a hierarchy, even though the underlying filesystem paths might. Also, data can be sent directly to and from the database without the need for a local file.

Sample Usage

Many readers won't care how any of the functionality is implemented; if it works, that's good enough. So, I'll begin by skipping straight to the meat and show how you might use this package to retrieve a remote file and display its contents. This use case is embedded in the source comments for reference.

DECLARE
    v_conn sdsftp.connection;
BEGIN
    sdsftp.clear_log;
    sdsftp.set_log_options(1);
    v_conn := sdsftp.open(:HOST, :username, :pwd);
    DBMS_OUTPUT.
     put_line(
        '----------------------------------------------------------------------------'
    );
    DBMS_OUTPUT.put_line(sdsftp.get_clob(v_conn, 'test.txt'));
    DBMS_OUTPUT.
     put_line(
        '----------------------------------------------------------------------------'
    );
    sdsftp.close(v_conn);
EXCEPTION
    WHEN OTHERS
    THEN
        sdsftp.close(v_conn);
        RAISE;
END;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

Select all Open in new window

Explanation of Example

In general the API works much like you would expect a set of file operations in most languages to behave. Declare a handle, open, read/write, close.

As with any pl/sql block we have to declare our variables first. The connection type is a wrapper for the Oracle UTL_TCP connection with some extra fields included.

DECLARE
    v_conn sdsftp.connection;

1:
2:

Select all Open in new window

Next we start the code and initialize the logging. Option 1 directs all logs to the DBMS_OUTPUT buffer.

BEGIN
    sdsftp.clear_log;
    sdsftp.set_log_options(1);

1:
2:
3:

Select all Open in new window

After setting up the logs, open a connection to the remote host, I have bound variables here to prompt for the required values. The connection will be to the default FTP port 21, but this can be changed with the optional p_port parameter. Other options include setting local and remote starting directories and transfer options (ASCII vs Binary)

    v_conn := sdsftp.open(:HOST, :username, :pwd);

1:

Select all Open in new window

Then, a one-line workhorse. Get and display a remote file called "test.txt". Note, the function is "get_clob". No file is created locally, not even temporarily in this process. The contents of test.txt are read directly into a temporary CLOB variable. That CLOB is then returned from the function. Thus turning the remote file into a local string variable; which is then displayed.

    DBMS_OUTPUT.put_line(sdsftp.get_clob(v_conn, 'test.txt'));

1:

Select all Open in new window

And, finally, having read the file we close the connection.

     sdsftp.close(v_conn);

1:

Select all Open in new window

If there were any errors above, cleanup our connection and then throw/raise the error.

EXCEPTION
    WHEN OTHERS
    THEN
        sdsftp.close(v_conn);
        RAISE;
END;

1:
2:
3:
4:
5:
6:

Select all Open in new window

Overview of the API and the FTP protocol

Next, I'd like to go into more detail about the procedures and functions exposed and how they relate to and implement the FTP protocol. FTP is a fairly simple protocol. Everything is a command-reply pair with numeric codes on each reply indicating meaning. The actual data transfers (in either direction) add a little more complexity since a secondary connection is opened with semi-independent communication on it from the main command port. That complexity is encapsulated within a family of get_xxx and put_xxx routines. I'll list the procedures and functions exposed in the package and explain how the underlying FTP commands are invoked.

Connectivity and communication

open

All FTP sessions will start with the OPEN procedure or function, they are equivalent, both forms are provided for programmatic convenience and preference. Under the hood, the function version simply calls the procedural version. The session begins with a telnet connect to the remote server and assuming a good connection will then issue the FTP commands USER and PASS, and as you might expect these log you in with your username and password. Some servers may request a secondary password or "account info". If this is requested, the open procedure will acknowledge with the appropriate ACCT command. Note, you must provide these credentials, including the account info (if needed) when invoking the open routine.

Simply disconnects from the FTP server with the QUIT command and cleans up the TCP telnet connection to the server.

send_ftp_command

This is the engine behind all of the other routines that communicate with the remote server. Despite being critical, its functionality is actually quite simple. First it writes the command to the remote TCP port along with any arguments. It reads whatever reply returned from the server and, if prompted for account information as described in the login process , will automatically send the ACCT command and appropriate information supplied with the OPEN.

read_reply

Each FTP command should expect a reply, so every invocation of send_ftp_command automatically calls read_reply to get the response. All FTP responses begin with a 3 digit code and then possibly some text including multi-line responses. Read_reply handles all of these and raises exceptions for codes in the 4xx or 5xx series as these indicate Transient and Permanent errors respectively from the remote server.

get_last_reply

Returns the last reply from the remote FTP server, including numeric code and any associated text. Multi-line replies are returned as a single value with embedded carriage-returns and line-feeds.

Logging

set_log_options and get_log_options

There are 4 logging options which may be used in combination as bit masks

c_log_off (0)

If logging is set to 0 then all logging is turned off

c_log_dbms_ouptut (1)

When logging bit-1 is set then all messages are written to the dbms_output buffer. When using this option remember to enable output and also remember the buffer is not displayed until processing is over. If you want a more real-time response, consider adding either or both of the next options.

c_log_rolling_buffer (2)

When logging bit-2 is set then all messages are written to a 32K buffer (varchar2 type). When the buffer fills, the oldest messages are wiped out from the beginning and new are appended to the end.

c_log_client_info (4)

When logging bit-3 is set then the first 64 bytes of each message is written to the user's session client_info column of v$session. This is a non-transactional update. So it's seen immediately regardless of any data manipulation or commits/rollbacks that may have occurred.

clear_log

The rolling buffer will be set to NULL and, if client_info logging is enabled, it will be set to NULL as well. The dbms_output buffer is not affected by this procedure.

get_log_text

Returns the contents of the rolling buffer. The type of the rolling buffer is VARCHAR2(32767) so it's a legal string for use in PL/SQL but may be too large to be used in a SQL statement. If so, it can be split into smaller, consumable chunks with SUBSTR

write_to_log

Just as the name implies, the procedure writes a message to any and all active logging options (see above.)

set_verbose and get_verbose

The verbose option doesn't correspond to an FTP command, it's internal to this package and when TRUE, increases the level of information written to the logs. If logging is disabled then verbose mode has no effect. Verbose is OFF by default.

Remote operations
The following commands call send_ftp_command to operate on files or directories of the remote FTP server. With the exception of a rename, each of these routines maps to exactly one FTP command. These commands have no affect on local files, directories or data. Paths given in these may be absolute or relative to the current working directory.

remote_rename

Rename a file on the remote server. The exposed API is a single command but inside the procedure, the FTP protocol requires 2 calls to send_ftp_command: RNFR (ReName FRom) and RNTO (ReName TO). The rename can include new paths causing the remote file to be moved to a new directory in addition to changing the file name.

remote_delete_file

Delete a file on the remote server using ftp command DELE

remote_create_directory

Create a new directory on the remote server with ftp command MKD.

remote_delete_directory

Delete a directory on the remote server with ftp command RMD.

remote_cd

Change your current working directory on the remote server with ftp command CWD

remote_cdup

Change your current working directory to the parent of the current directory with ftp command CDUP

remote_pwd

Rather than manipulating anything on the remote server, this function returns the absolute path of your current working directory.

Local operations
The following routines work a little differently than a typical FTP client because the Oracle database is our client and the database doesn't have normal file sytems. It has directory objects that sit on top of the underlying operating system file structure. As such, there are no paths absolute or relative. Each directory is its own entity, even if the underlying OS filesystem has a hierarchy. Since the directories are database objects which already have corresponding SQL commands for their DDL operations, this package doesn't duplicate that functionality. So, there are only two local operations compared to the greater remote operations. Directory privileges are not checked as part of these routines. Privileges to read from or write to a directory is only determined at the time of those operations.

local_cd

Change your current local directory

local_pwd

Return the name of your current local directory

File and Data Transfers
The data transfers for the pl/sql client are similar to those you might find in other clients: gets and puts with different transfer methods. Two signficant differences. One, there is no facility for multi-file transfers in either direction. Two, as illustrated in the use case above, a local file isn't necessary, the local data can come directly from or write directly to CLOB and BLOB objects within the database.

set_transfer_method

3 different transfer methods are available. ASCII, EBCDIC, and IMAGE. These may be chosen by name, abbreviation (A,E,I) or the keywords BINARY (same as IMAGE) or DEFAULT (constant currently set to ASCII). All input variations are converted to the standard strings and passed with the TYPE command. FTP options for NONPRINT, TELNET and CARRIAGE CONTROL may be passed as optional 3rd parameters but do not have any effect in the current verions of the code.

put_clob and put_blob

These two procedures copy a local LOB value to the remote system and write it as a file with the FTP command STOR. The CLOB version can be sent with the current transfer method or the optional 4th parameter can change the current method as part of the transfer. The BLOB procedure will, by default change to the IMAGE (binary) transfer method if it's not currently selected and then restore the current method afterward. The optional 4th parameter can override this switch and force the current transfer method to be used.

get_clob and get_blob

Each of the get options is exposed in both procedure and function versions, but perform the same with the FTP command RETR. The function verions simply invoke the corresponding procedure and return the OUT parameter as the function value. Since LOBs don't have names, unlike a file get, you only need to specify the remote file name, the LOB pointer determines the destination. Like the put functions above, the CLOB routines can change transfer methods and the BLOB will automatically switch to IMAGE (binary) transfer unless forced to use the current method.

put_file and get_file

These are each overloaded to 3 versions, depending whether you want to specify the remote file path, or simply read/write the local file with the same name to the current directory. And whether you want to specify a local directory other than the current directory. By default, the current transfer method will be used, but it can be changed as part of the transfer with the last parameter of each version (optional in all of them). As with the LOB verions, put actions are an FTP STOR, get actions are with FTP RETR.

get_file_list

This routine comes in procedure and function form but the functionality is equivalent. Given a path and including optional filename wildcards, these will return the list of files in a collection. By default all information about the files will be returned but if the p_names_only parameter is TRUE only the names will be returned. These options correspond to the FTP commands LIST (false) and NLST (true). Note the FTP RFC does not specify content or format of the data returned by these commands, so all information
is returned simply as a collection of strings. Once you know the format of the data for the particular server you are working with you can write a parsing routine to extract the pieces of information you need; but be sure to verify the format anytime you change servers.

Additional Example

This example shows how to use the get_file_list function and iterate through the resulting collection

DECLARE
    v_conn sdsftp.connection;
    v_list sdsftp.file_list;
BEGIN
    sdsftp.clear_log;
    sdsftp.set_log_options(0);
    v_conn := sdsftp.open(:HOST, :username, :pwd);
    DBMS_OUTPUT.put_line(
        '----------------------------------------------------------------------------'
    );
    v_list := sdsftp.get_file_list(v_conn, '/', false);

    FOR i IN v_list.FIRST .. v_list.LAST
    LOOP
        DBMS_OUTPUT.put_line(v_list(i));
    END LOOP;

    DBMS_OUTPUT.put_line(
        '----------------------------------------------------------------------------'
    );
    sdsftp.close(v_conn);
EXCEPTION
    WHEN OTHERS
    THEN
        sdsftp.close(v_conn);
        RAISE;
END;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Select all Open in new window

Reference and Source Code

For more information about FTP commands and reply codes, see "RFC 959 - File Transfer Protocol" ( http://www.faqs.org/rfcs/rfc959.html )

I hope you find the attached package code both useful and interesting. It was fun to write. If you need more complete support of the FTP protocol, you can try searching for other pl/sql implementations, or java stored procedures with standard FTP libraries may be a better bet.

CREATE OR REPLACE PACKAGE sdsftp
AS
    --                    .///.
    --                   (0 o)
    ---------------0000--(_)--0000---------------
    --
    --  Sean D. Stuber
    --  sean.stuber@gmail.com
    --
    --             oooO      Oooo
    --------------(   )-----(   )---------------
    --             \ (       ) /
    --              \_)     (_/

    -- Only Passive data transfers are supported
    -- Active connections aren't possible because UTL_TCP, as of 11gR2, doesn't support
    -- the local_host and local_port parameters and fields of the open procedure and record
    --
    --
    --   Sample usage
    --
    --DECLARE
    --    v_conn   sdsftp.connection;
    --BEGIN
    --    sdsftp.clear_log;
    --    sdsftp.set_log_options(1);
    --    v_conn   := sdsftp.open(:HOST, :username, :pwd);
    --    DBMS_OUTPUT.put_line(
    --        '----------------------------------------------------------------------------'
    --    );
    --    DBMS_OUTPUT.put_line(sdsftp.get_clob(v_conn, 'test.txt'));
    --    DBMS_OUTPUT.put_line(
    --        '----------------------------------------------------------------------------'
    --    );
    --    sdsftp.close(v_conn);
    --EXCEPTION
    --    WHEN OTHERS THEN
    --        sdsftp.close(v_conn);
    --        RAISE;
    --END;

    TYPE connection IS RECORD
    (
        tcp               UTL_TCP.connection,
        account_info      VARCHAR2(1000),
        transfer_method   VARCHAR2(1),
        transfer_option   VARCHAR2(1),
        local_directory   VARCHAR2(30),
        last_reply        VARCHAR2(32767)
    );

    TYPE file_list IS TABLE OF VARCHAR2(32767)
                          INDEX BY BINARY_INTEGER;

    c_default_ftp_control_port   CONSTANT INTEGER := 21;
    c_default_transfer_method    CONSTANT VARCHAR2(10) := 'ASCII';

    c_default_verbose            CONSTANT BOOLEAN := FALSE;

    c_log_off                    CONSTANT INTEGER := 0;
    c_log_dbms_output            CONSTANT INTEGER := 1;
    c_log_rolling_buffer         CONSTANT INTEGER := 2;
    c_log_client_info            CONSTANT INTEGER := 4;

    PROCEDURE open(p_server             IN     VARCHAR2,
                   p_username           IN     VARCHAR2,
                   p_password           IN     VARCHAR2,
                   p_connection            OUT connection,
                   p_local_directory    IN     VARCHAR2 DEFAULT NULL,
                   p_remote_directory   IN     VARCHAR2 DEFAULT NULL,
                   p_trans_method       IN     VARCHAR2 DEFAULT c_default_transfer_method,
                   p_timeout            IN     INTEGER DEFAULT NULL,
                   p_port               IN     INTEGER DEFAULT c_default_ftp_control_port,
                   p_account_info       IN     VARCHAR2 DEFAULT NULL
                  );

    FUNCTION open(p_server             IN VARCHAR2,
                  p_username           IN VARCHAR2,
                  p_password           IN VARCHAR2,
                  p_local_directory    IN VARCHAR2 DEFAULT NULL,
                  p_remote_directory   IN VARCHAR2 DEFAULT NULL,
                  p_trans_method       IN VARCHAR2 DEFAULT c_default_transfer_method,
                  p_timeout            IN INTEGER DEFAULT NULL,
                  p_port               IN INTEGER DEFAULT c_default_ftp_control_port,
                  p_account_info       IN VARCHAR2 DEFAULT NULL
                 )
        RETURN connection;

    PROCEDURE close(p_connection IN OUT NOCOPY connection);

    PROCEDURE set_verbose(p_verbose IN BOOLEAN);

    FUNCTION get_verbose
        RETURN BOOLEAN;

    PROCEDURE write_to_log(v_text IN VARCHAR2, p_verbose IN BOOLEAN DEFAULT FALSE);

    PROCEDURE set_log_options(p_log_options IN INTEGER);

    FUNCTION get_log_options
        RETURN INTEGER;

    PROCEDURE clear_log;

    FUNCTION get_log_text
        RETURN VARCHAR2;

    FUNCTION get_last_reply(p_connection IN OUT NOCOPY connection)
        RETURN VARCHAR2;

    PROCEDURE send_ftp_command(p_connection     IN OUT NOCOPY connection,
                               p_command        IN            VARCHAR2,
                               p_arguments      IN            VARCHAR2 DEFAULT NULL,
                               p_account_info   IN            VARCHAR2 DEFAULT NULL
                              );

    PROCEDURE read_reply(p_connection IN OUT NOCOPY connection);

    PROCEDURE remote_rename(p_connection   IN OUT NOCOPY connection,
                            p_old_name     IN            VARCHAR2,
                            p_new_name     IN            VARCHAR2
                           );

    PROCEDURE remote_delete_file(p_connection IN OUT NOCOPY connection, p_file IN VARCHAR2);

    PROCEDURE remote_create_directory(p_connection   IN OUT NOCOPY connection,
                                      p_directory    IN            VARCHAR2
                                     );

    PROCEDURE remote_delete_directory(p_connection   IN OUT NOCOPY connection,
                                      p_directory    IN            VARCHAR2
                                     );

    PROCEDURE set_transfer_method(p_connection        IN OUT NOCOPY connection,
                                  p_transfer_method   IN            VARCHAR2,
                                  p_option            IN            VARCHAR2 DEFAULT NULL
                                 );

    FUNCTION get_transfer_method(p_connection IN OUT NOCOPY connection)
        RETURN VARCHAR2;

    PROCEDURE local_cd(p_connection IN OUT NOCOPY connection, p_directory IN VARCHAR2);

    FUNCTION local_pwd(p_connection IN OUT NOCOPY connection)
        RETURN VARCHAR2;

    PROCEDURE remote_cd(p_connection IN OUT NOCOPY connection, p_directory IN VARCHAR2);

    PROCEDURE remote_cdup(p_connection IN OUT NOCOPY connection);

    FUNCTION remote_pwd(p_connection IN OUT NOCOPY connection)
        RETURN VARCHAR2;

    PROCEDURE put_clob(p_connection     IN OUT NOCOPY connection,
                       p_local_clob     IN OUT NOCOPY CLOB,
                       p_remote_file    IN            VARCHAR2,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      );

    PROCEDURE put_blob(p_connection     IN OUT NOCOPY connection,
                       p_local_blob     IN OUT NOCOPY BLOB,
                       p_remote_file    IN            VARCHAR2,
                       p_force_binary   IN            BOOLEAN DEFAULT TRUE
                      );

    PROCEDURE get_clob(p_connection     IN OUT NOCOPY connection,
                       p_remote_file    IN            VARCHAR2,
                       p_local_clob     IN OUT NOCOPY CLOB,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      );

    FUNCTION get_clob(p_connection     IN OUT NOCOPY connection,
                      p_remote_file    IN            VARCHAR2,
                      p_trans_method   IN            VARCHAR2 DEFAULT NULL
                     )
        RETURN CLOB;

    PROCEDURE get_blob(p_connection     IN OUT NOCOPY connection,
                       p_remote_file    IN            VARCHAR2,
                       p_local_blob     IN OUT NOCOPY BLOB,
                       p_force_binary   IN            BOOLEAN DEFAULT TRUE
                      );

    FUNCTION get_blob(p_connection     IN OUT NOCOPY connection,
                      p_remote_file    IN            VARCHAR2,
                      p_force_binary   IN            BOOLEAN DEFAULT TRUE
                     )
        RETURN BLOB;

    PROCEDURE put_file(p_connection        IN OUT NOCOPY connection,
                       p_local_directory   IN            VARCHAR2,
                       p_local_file        IN            VARCHAR2,
                       p_remote_file       IN            VARCHAR2,
                       p_trans_method      IN            VARCHAR2 DEFAULT NULL
                      );

    PROCEDURE put_file(p_connection     IN OUT NOCOPY connection,
                       p_local_file     IN            UTL_FILE.file_type,
                       p_remote_file    IN            VARCHAR2,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      );

    PROCEDURE get_file(p_connection        IN OUT NOCOPY connection,
                       p_remote_file       IN            VARCHAR2,
                       p_local_directory   IN            VARCHAR2,
                       p_local_file        IN            VARCHAR2,
                       p_trans_method      IN            VARCHAR2 DEFAULT NULL
                      );

    PROCEDURE get_file(p_connection     IN OUT NOCOPY connection,
                       p_remote_file    IN            VARCHAR2,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      );

    PROCEDURE get_file(p_connection     IN OUT NOCOPY connection,
                       p_remote_file    IN            VARCHAR2,
                       p_local_file     IN OUT NOCOPY UTL_FILE.file_type,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      );

    PROCEDURE get_file_list(p_connection     IN OUT NOCOPY connection,
                            p_remote_path    IN            VARCHAR2,
                            p_list              OUT        file_list,
                            p_names_only     IN            BOOLEAN DEFAULT FALSE,
                            p_trans_method   IN            VARCHAR2 DEFAULT 'ASCII'
                           );

    FUNCTION get_file_list(p_connection     IN OUT NOCOPY connection,
                           p_remote_path    IN            VARCHAR2,
                           p_names_only     IN            BOOLEAN DEFAULT FALSE,
                           p_trans_method   IN            VARCHAR2 DEFAULT 'ASCII'
                          )
        RETURN file_list;
END sdsftp;
/

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:

Select all Open in new window

CREATE OR REPLACE PACKAGE BODY sdsftp
AS
    --                    .///.
    --                   (0 o)
    ---------------0000--(_)--0000---------------
    --
    --  Sean D. Stuber
    --  sean.stuber@gmail.com
    --
    --             oooO      Oooo
    --------------(   )-----(   )---------------
    --             \ (       ) /
    --              \_)     (_/

    -- 2010-09-16 Bug Fix:     Buffer length in binary transfers set to null, meaning all.
    --            Bug Fix:     Files are closed in routines that generate their own file handle.
    --                         Routines that receive a file handle are left open.
    --            Cosmetic:    Minor formatting and variable name changes.
    -- 2011-01-18 Enhancement: Added support for listing of remote directories 
    -- 


    c_crlf                    CONSTANT CHAR(2) := CHR(13) || CHR(10);

    -- Currently unused, would be used with the PORT command to do transfers
    -- without passive mode.
    c_default_ftp_data_port   CONSTANT INTEGER := c_default_ftp_control_port - 1;

    -- Per RFC 959, if account info (ACCT) is requested then a 332 code
    -- should be returned from the PASS command instead of a Positive Completion
    c_ftp_request_acct        CONSTANT INTEGER := 332;

    g_log_options                      INTEGER := c_log_dbms_output;
    g_log_text                         VARCHAR2(32767) := NULL;

    g_verbose                          BOOLEAN := c_default_verbose;

    -- Parse an FTP reply string, extract the numeric code
    FUNCTION reply_code(p_reply_text IN VARCHAR2)
        RETURN INTEGER
    IS
    BEGIN
        RETURN TO_NUMBER(SUBSTR(p_reply_text, 1, 3));
    END reply_code;

    -- When verbose mode is TRUE then additional information will be written to the logs.
    -- If logging is turned off then verbose mode won't add anything.
    PROCEDURE set_verbose(p_verbose IN BOOLEAN)
    IS
    BEGIN
        g_verbose  := NVL(p_verbose, c_default_verbose);

        IF p_verbose
        THEN
            write_to_log('Switching logging to verbose mode', TRUE);
        END IF;
    END set_verbose;

    -- Returns the current setting of verbose mode (true/false)
    FUNCTION get_verbose
        RETURN BOOLEAN
    IS
    BEGIN
        RETURN g_verbose;
    END get_verbose;

    -- Turn off logging (options=0) or turn on different options (see c_log_xxxxx constants)
    PROCEDURE set_log_options(p_log_options IN INTEGER)
    IS
    BEGIN
        -- options big mask must be between 0 and sum of all possible log options.
        IF p_log_options < 0
           OR p_log_options > c_log_dbms_output + c_log_rolling_buffer + c_log_client_info
        THEN
            raise_application_error(
                -20001,
                'Invalid log options, must be between 0 and '
                || TO_CHAR(c_log_dbms_output + c_log_rolling_buffer + c_log_client_info),
                TRUE);
        END IF;

        g_log_options  := p_log_options;
    END set_log_options;

    -- Return current logging options
    FUNCTION get_log_options
        RETURN INTEGER
    IS
    BEGIN
        RETURN g_log_options;
    END get_log_options;

    -- Clears (null) the logging buffers
    PROCEDURE clear_log
    IS
    BEGIN
        -- Clear the log buffer
        g_log_text  := NULL;

        -- if logging to session client then clear that too
        IF BITAND(g_log_options, c_log_client_info) > 0
        THEN
            DBMS_APPLICATION_INFO.set_client_info(NULL);
        END IF;
    END clear_log;

    -- Return the current contents of the rolling log buffer
    FUNCTION get_log_text
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN g_log_text;
    END get_log_text;

    -- write the text to each logging option that is currently enabled
    -- verbose comments will not be logged if verbose mode is off.
    PROCEDURE write_to_log(v_text IN VARCHAR2, p_verbose IN BOOLEAN DEFAULT FALSE)
    IS
    BEGIN
        -- If global verbose setting is ON (meaning log everything)
        -- or if this text is not verbose then log it.
        IF g_verbose OR NOT p_verbose
        THEN
            IF BITAND(g_log_options, c_log_dbms_output) > 0
            THEN
                DBMS_OUTPUT.put_line(v_text);
            END IF;

            IF BITAND(g_log_options, c_log_rolling_buffer) > 0
            THEN
                IF LENGTH(g_log_text) + LENGTH(c_crlf) + LENGTH(v_text) > 32767
                THEN
                    g_log_text      :=
                        SUBSTR(g_log_text, INSTR(g_log_text, c_crlf, LENGTH(v_text) + 2));
                END IF;

                g_log_text  := g_log_text || v_text || c_crlf;
            END IF;

            IF BITAND(g_log_options, c_log_client_info) > 0
            THEN
                DBMS_APPLICATION_INFO.set_client_info(v_text);
            END IF;
        END IF;
    END write_to_log;

    -- Log in to the given server and return a connection object
    PROCEDURE open(p_server             IN     VARCHAR2,
                   p_username           IN     VARCHAR2,
                   p_password           IN     VARCHAR2,
                   p_connection            OUT connection,
                   p_local_directory    IN     VARCHAR2 DEFAULT NULL,
                   p_remote_directory   IN     VARCHAR2 DEFAULT NULL,
                   p_trans_method       IN     VARCHAR2 DEFAULT c_default_transfer_method,
                   p_timeout            IN     INTEGER DEFAULT NULL,
                   p_port               IN     INTEGER DEFAULT c_default_ftp_control_port,
                   p_account_info       IN     VARCHAR2 DEFAULT NULL
                  )
    IS
        v_connection   connection;
    BEGIN
        write_to_log('opening connection to: ' || p_server || ':' || p_port, TRUE);
        v_connection.tcp      :=
            UTL_TCP.open_connection(remote_host  => p_server,
                                    remote_port  => p_port,
                                    tx_timeout   => p_timeout
                                   );

        read_reply(v_connection);

        send_ftp_command(v_connection, 'USER', p_username, p_account_info);
        send_ftp_command(v_connection, 'PASS', p_password, p_account_info);

        -- If we haven't already been prompted for ACCT info
        -- then send it now if we have it
        IF p_account_info IS NOT NULL AND v_connection.account_info IS NULL
        THEN
            send_ftp_command(v_connection, 'ACCT', p_account_info);
            v_connection.account_info  := p_account_info;
        END IF;

        IF p_local_directory IS NOT NULL
        THEN
            local_cd(v_connection, p_local_directory);
        END IF;

        IF p_remote_directory IS NOT NULL
        THEN
            remote_cd(v_connection, p_remote_directory);
        END IF;

        set_transfer_method(v_connection, p_trans_method);

        p_connection  := v_connection;
    END open;

    -- Log in to the given server and return a connection object
    FUNCTION open(p_server             IN VARCHAR2,
                  p_username           IN VARCHAR2,
                  p_password           IN VARCHAR2,
                  p_local_directory    IN VARCHAR2 DEFAULT NULL,
                  p_remote_directory   IN VARCHAR2 DEFAULT NULL,
                  p_trans_method       IN VARCHAR2 DEFAULT c_default_transfer_method,
                  p_timeout            IN INTEGER DEFAULT NULL,
                  p_port               IN INTEGER DEFAULT c_default_ftp_control_port,
                  p_account_info       IN VARCHAR2 DEFAULT NULL
                 )
        RETURN connection
    IS
        v_connection   connection;
    BEGIN
        sdsftp.open(p_server            => p_server,
                    p_username          => p_username,
                    p_password          => p_password,
                    p_connection        => v_connection,
                    p_local_directory   => p_local_directory,
                    p_remote_directory  => p_remote_directory,
                    p_trans_method      => p_trans_method,
                    p_timeout           => p_timeout,
                    p_port              => p_port,
                    p_account_info      => p_account_info
                   );
        RETURN v_connection;
    END open;

    -- Close the FTP, severs the TCP connection and clears the internal values
    PROCEDURE close(p_connection IN OUT NOCOPY connection)
    IS
    BEGIN
        write_to_log(
               'closing connection: '
            || p_connection.tcp.remote_host
            || ':'
            || p_connection.tcp.remote_port,
            TRUE);
        send_ftp_command(p_connection, 'QUIT');
        UTL_TCP.close_connection(p_connection.tcp);
        p_connection.tcp              := NULL;
        p_connection.account_info     := NULL;
        p_connection.transfer_method  := NULL;
        p_connection.transfer_option  := NULL;
        p_connection.local_directory  := NULL;
        p_connection.last_reply       := NULL;

        write_to_log('connection closed', TRUE);
    END close;

    -- Request a separate connection on a different port for data transfer
    -- PASV is the preferred transfer method opposed to PORT
    -- per RFC 1123 (4.1.2.6) all servers must implement PASV.
    FUNCTION get_passive_connection(p_connection IN OUT NOCOPY connection)
        RETURN UTL_TCP.connection
    IS
        v_temp              VARCHAR2(25);
        v_host              VARCHAR2(25);
        v_port              INTEGER;
        v_pasv_connection   UTL_TCP.connection;
    BEGIN
        write_to_log('requesting passive connection', TRUE);
        send_ftp_command(p_connection, 'PASV');

        -- PASV should respond with something of the form 227 zzzzzzzzzzzzzz (hhh,hhh,hhh,hhh,ppp,ppp)
        -- where  hhh,hhh,hhh,hhh  is the host ip address, simply change ','  to '.' and it's ready to go
        -- construct the port by taking the first part as the high byte of a 2-byte number  (multiply by 256)
        -- and the second part as the low byte of the 2-byte number  (add it to the high byte)

        -- per RFC1123 the host/port digits might not be enclosed in parentheses(),
        -- therefore the parsing should be based on a scan of the digits themselves

        v_temp             := REGEXP_SUBSTR(p_connection.last_reply, '(\d{1,3},){5,5}\d{1,3}');

        --
        -- 9i and lower don't have regular expressions
        -- so, we have to use other methods to strip out the host/port digits
        --        v_temp :=
        --            RTRIM(
        --                LTRIM(
        --                    TRANSLATE(
        --                        SUBSTR(p_connection.last_reply, 5),
        --                        '0123456789,'
        --                        || TRANSLATE(SUBSTR(p_connection.last_reply, 5), CHR(0) || '0123456789,', CHR(0)),
        --                        '0123456789,'
        --                    ),
        --                    ','
        --                ),
        --                ','
        --            );

        --  v_temp should now look like this: 'hhh,hhh,hhh,hhh,ppp,ppp'  (minus the quotes)

        v_host             := REPLACE(SUBSTR(v_temp, 1, INSTR(v_temp, ',', 1, 4) - 1), ',', '.');

        v_temp             := SUBSTR(v_temp, INSTR(v_temp, ',', 1, 4) + 1);

        v_port             :=
            TO_NUMBER(SUBSTR(v_temp, 1, INSTR(v_temp, ',') - 1)) * 256
            + TO_NUMBER(SUBSTR(v_temp, INSTR(v_temp, ',') + 1));

        write_to_log('opening passive connection', TRUE);
        v_pasv_connection  := UTL_TCP.open_connection(remote_host => v_host, remote_port => v_port);

        RETURN v_pasv_connection;
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in get_passive_connection');
            write_to_log('    reply:' || p_connection.last_reply);
            write_to_log('    host:' || v_host);
            write_to_log('    port:' || v_port);
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
            RAISE;
    END get_passive_connection;

    -- Return the last reply from the server (multi-line replies will be returned as a single string)
    FUNCTION get_last_reply(p_connection IN OUT NOCOPY connection)
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN p_connection.last_reply;
    END;

    -- Read a reply from the server, including multi-line replies, concatenating them into a single reply string
    PROCEDURE read_reply(p_connection IN OUT NOCOPY connection)
    IS
        --    FTP Replies (per rfc959)
        --        replies begin with 3 digit codes xyz
        --        the from can be either single line or multi-line
        --        if single line, then xyz text
        --        if multi-line, then xyz-text, followed by any number of lines, followed by xyz text
        --        thus a reply will always end with the xyz code, a space and optionally some text.
        --
        --        xyz single line reply
        --
        --        xyz-start of multi-line reply
        --            some text
        --            some more text
        --        xyz end of multi-line reply
        --
        --        1yz   Positive Preliminary reply
        --        2yz   Positive Completion reply
        --        3yz   Positive Intermediate reply
        --        4yz   Transient Negative Completion reply
        --        5yz   Permanent Negative Completion reply
        --
        --        x0z   Syntax
        --        x1z   Information
        --        x2z   Connections
        --        x3z   Authentication and accounting
        --        x4z   Unspecified as yet.
        --        x5z   File system
        --
        --        The third digit gives a finer gradation of meaning in each
        --        of the function categories, specified by the second digit.
        v_reply        VARCHAR2(32767) := NULL;
        v_start_code   VARCHAR2(3);
        v_temp         VARCHAR2(32767);
    BEGIN
        v_temp                   := UTL_TCP.get_line(p_connection.tcp, TRUE);

        IF SUBSTR(v_temp, 4, 1) = ' '
        THEN
            -- 3 digits and a space is a normal, one line response
            v_reply  := v_temp;
        ELSIF SUBSTR(v_temp, 4, 1) = '-'
        THEN
            v_start_code  := SUBSTR(v_temp, 1, 3);

            LOOP
                v_temp  := UTL_TCP.get_line(p_connection.tcp, TRUE);

                IF v_reply IS NULL
                THEN
                    v_reply  := v_temp;
                ELSE
                    v_reply  := v_reply || UTL_TCP.crlf || v_temp;
                END IF;

                EXIT WHEN v_temp LIKE v_start_code || ' %';
            END LOOP;
        ELSE
            raise_application_error(-20001, 'Invalid FTP Protocol reply: ' || v_temp, TRUE);
        END IF;

        p_connection.last_reply  := v_reply;

        IF g_log_options > 0
        THEN
            write_to_log(v_reply);
        END IF;

        IF SUBSTR(v_reply, 1, 1) = '4'
        THEN
            raise_application_error(-20001, 'Transient error from FTP server: ' || v_reply, TRUE);
        ELSIF SUBSTR(v_reply, 1, 1) = '5'
        THEN
            raise_application_error(-20001, 'Permanent error from FTP server: ' || v_reply, TRUE);
        END IF;
    EXCEPTION
        WHEN UTL_TCP.end_of_input
        THEN
            NULL;
    END read_reply;

    -- Send raw FTP protocol command (such as USER, PASS, STOR, RETR, etc)
    PROCEDURE send_ftp_command(p_connection     IN OUT NOCOPY connection,
                               p_command        IN            VARCHAR2,
                               p_arguments      IN            VARCHAR2 DEFAULT NULL,
                               p_account_info   IN            VARCHAR2 DEFAULT NULL
                              )
    IS
        v_code   INTEGER;
    BEGIN
        IF p_arguments IS NULL
        THEN
            v_code  := UTL_TCP.write_line(p_connection.tcp, p_command);
        ELSE
            v_code  := UTL_TCP.write_line(p_connection.tcp, p_command || ' ' || p_arguments);
        END IF;

        read_reply(p_connection);

        IF reply_code(p_connection.last_reply) = c_ftp_request_acct
        THEN
            send_ftp_command(p_connection, 'ACCT', NVL(p_connection.account_info, p_account_info));
            p_connection.account_info  := p_account_info;
        END IF;
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in send_ftp_command(' || p_command || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
            RAISE;
    END send_ftp_command;

    -- Rename a file or directory on the FTP server.
    -- Note, this operation can be used to move files
    PROCEDURE remote_rename(p_connection   IN OUT NOCOPY connection,
                            p_old_name     IN            VARCHAR2,
                            p_new_name     IN            VARCHAR2
                           )
    IS
    BEGIN
        write_to_log('renaming remote file: ' || p_old_name || ' to ' || p_new_name, TRUE);
        send_ftp_command(p_connection, 'RNFR', p_old_name);
        send_ftp_command(p_connection, 'RNTO', p_new_name);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in remote_rename(' || p_old_name || ',' || p_new_name || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            RAISE;
    END remote_rename;

    -- Delete a file on the FTP server.
    PROCEDURE remote_delete_file(p_connection IN OUT NOCOPY connection, p_file IN VARCHAR2)
    IS
    BEGIN
        write_to_log('deleting remote file: ' || p_file, TRUE);
        send_ftp_command(p_connection, 'DELE', p_file);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in remote_delete_file(' || p_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            RAISE;
    END remote_delete_file;

    -- Create a directory on the FTP server.
    PROCEDURE remote_create_directory(p_connection   IN OUT NOCOPY connection,
                                      p_directory    IN            VARCHAR2
                                     )
    IS
    BEGIN
        write_to_log('creating remote directory: ' || p_directory, TRUE);
        send_ftp_command(p_connection, 'MKD', p_directory);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in remote_create_directory(' || p_directory || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            RAISE;
    END remote_create_directory;

    -- Delete a directory on the FTP server.
    PROCEDURE remote_delete_directory(p_connection   IN OUT NOCOPY connection,
                                      p_directory    IN            VARCHAR2
                                     )
    IS
    BEGIN
        write_to_log('deleting remote directory: ' || p_directory, TRUE);
        send_ftp_command(p_connection, 'RMD', p_directory);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in remote_delete_directory(' || p_directory || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            RAISE;
    END remote_delete_directory;

    -- Sets the transfer method, it will accept FTP protocol values of A, I, E
    -- as well as the key words ASCII, IMAGE, EBCIDIC, BINARY, DEFAULT
    -- The first three map to the protocol characters, BINARY is equivalent to I
    -- DEFAULT will set the method equal to the default method defined in this package's
    -- specification.  If NULL, then no change will be made.
    -- The A, E and I methods can also be parameterized if necessary (usually won't be)
    -- The FTP protocol representation L is NOT supported at this time.
    PROCEDURE set_transfer_method(p_connection        IN OUT NOCOPY connection,
                                  p_transfer_method   IN            VARCHAR2,
                                  p_option            IN            VARCHAR2 DEFAULT NULL
                                 )
    IS
        v_method       VARCHAR2(10) := UPPER(SUBSTR(p_transfer_method, 1, 10));
        v_option       VARCHAR2(1) := UPPER(SUBSTR(p_option, 1, 1));

        v_new_method   VARCHAR2(3);
    BEGIN
        CASE
            WHEN v_method IS NULL
            THEN
                -- Do nothing, if NULL is the new method, then just keep the current one
                NULL;
            WHEN v_method = 'DEFAULT'
            THEN
                v_new_method  := SUBSTR(c_default_transfer_method, 1, 1);
            WHEN v_method = 'BINARY'
            THEN
                v_new_method  := 'I';
            WHEN v_method IN ('A', 'ASCII', 'I', 'IMAGE', 'E', 'EBCDIC')
            THEN
                v_new_method  := SUBSTR(v_method, 1, 1);
            ELSE
                raise_application_error(
                    -20001,
                    'SDSFTP.set_transfer_method INVALID method: ' || p_transfer_method,
                    TRUE);
        END CASE;

        IF v_new_method != NVL(p_connection.transfer_method, '---')
        THEN
            write_to_log('changing transfer method', TRUE);

            IF v_option IN ('N', 'T', 'C')
            THEN
                send_ftp_command(p_connection, 'TYPE', v_new_method || ' ' || v_option);
            ELSIF v_option IS NULL
            THEN
                send_ftp_command(p_connection, 'TYPE', v_new_method);
            ELSE
                raise_application_error(-20001,
                                        'SDSFTP.set_transfer_method INVALID option: ' || p_option,
                                        TRUE
                                       );
            END IF;

            p_connection.transfer_method  := v_new_method;
            p_connection.transfer_option  := v_option;
            write_to_log('Transfer method changed', TRUE);
        END IF;
    END set_transfer_method;

    -- Return the full name of the current transfer method for the given connection
    -- Note, the FTP protocol representation L is NOT supported at this time.
    FUNCTION get_transfer_method(p_connection IN OUT NOCOPY connection)
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN CASE
                   WHEN p_connection.transfer_method = 'A' THEN 'ASCII'
                   WHEN p_connection.transfer_method = 'E' THEN 'EBCDIC'
                   WHEN p_connection.transfer_method = 'I' THEN 'IMAGE'
               END;
    END get_transfer_method;

    -- Change the directory used locally for sending files from or retrieving files into
    -- a directory can be an explicit path supported by utl_file_dir parameter
    -- or it can be a directory object.
    PROCEDURE local_cd(p_connection IN OUT NOCOPY connection, p_directory IN VARCHAR2)
    IS
    BEGIN
        p_connection.local_directory  := p_directory;
    END local_cd;

    -- Returns the current directory used locally
    -- Unlike a server which will always have a current directory
    -- the local client might be NULL if a local_cd has not occurred.
    FUNCTION local_pwd(p_connection IN OUT NOCOPY connection)
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN p_connection.local_directory;
    END local_pwd;

    -- Returns the current directory for the remote host
    FUNCTION remote_pwd(p_connection IN OUT NOCOPY connection)
        RETURN VARCHAR2
    IS
        v_temp   VARCHAR2(32767);
    BEGIN
        write_to_log('Requesting remote directory', TRUE);
        send_ftp_command(p_connection, 'PWD');

        -- reply should be of the from 257 "directoryname"
        -- strip the return code prefix and leading double quote
        v_temp  := SUBSTR(p_connection.last_reply, 6);

        -- strip off trailing double-qoutes
        v_temp  := RTRIM(v_temp, '"');

        -- double-qoutes within the name will be escaped with double-qoutes  i.e.  ""
        -- un-escape any embedded double-quotes
        v_temp  := REPLACE(v_temp, '""', '"');

        write_to_log('Remote directory is:' || v_temp, TRUE);
        RETURN v_temp;
    END remote_pwd;

    -- Change to the given directory on the remote host
    PROCEDURE remote_cd(p_connection IN OUT NOCOPY connection, p_directory IN VARCHAR2)
    IS
    BEGIN
        write_to_log('Changing remote directory to: ' || p_directory, TRUE);
        send_ftp_command(p_connection, 'CWD', p_directory);
        write_to_log('Remote directory changed', TRUE);
    END remote_cd;

    -- Move up to the parent directory on the remote host
    PROCEDURE remote_cdup(p_connection IN OUT NOCOPY connection)
    IS
    BEGIN
        write_to_log('Changing to remote parent directory', TRUE);
        send_ftp_command(p_connection, 'CDUP');
        write_to_log('Changed to remote parent directory', TRUE);
    END;

    -- Given a local clob, send it to the remote host to be saved as a file
    -- by default the current transfer method can be used or it can be
    -- changed as part of this procedure
    PROCEDURE put_clob(p_connection     IN OUT NOCOPY connection,
                       p_local_clob     IN OUT NOCOPY CLOB,
                       p_remote_file    IN            VARCHAR2,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      )
    IS
        v_read_cnt     INTEGER;
        v_write_cnt    INTEGER;
        v_lob_length   INTEGER;
        v_index        INTEGER;
        v_chunk        VARCHAR2(32767);

        v_data         UTL_TCP.connection;
    BEGIN
        set_transfer_method(p_connection, p_trans_method);
        v_data        := get_passive_connection(p_connection);

        send_ftp_command(p_connection, 'STOR', p_remote_file);

        write_to_log('storing file on: ' || v_data.remote_host || ':' || v_data.remote_port, TRUE);

        v_lob_length  := DBMS_LOB.getlength(p_local_clob);
        v_index       := 1;

        WHILE v_index <= v_lob_length
        LOOP
            v_read_cnt   := 32767;
            DBMS_LOB.read(p_local_clob, v_read_cnt, v_index, v_chunk);
            v_write_cnt  := UTL_TCP.write_text(v_data, v_chunk, NULL);
            UTL_TCP.flush(v_data);
            v_index      := v_index + v_write_cnt;
        END LOOP;

        write_to_log(
            'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
            TRUE);
        UTL_TCP.close_connection(v_data);
        read_reply(p_connection);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in put_clob(' || p_remote_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            write_to_log(
                'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
                TRUE);
            UTL_TCP.close_connection(v_data);

            RAISE;
    END put_clob;

    -- Given a local clob, send it to the remote host to be saved as a file
    -- by default the current transfer method will be ignored
    -- and the procedure will temporarily switch to Image/Binary transfer
    -- if force_binary is FALSE then the current method will be used.
    PROCEDURE put_blob(p_connection     IN OUT NOCOPY connection,
                       p_local_blob     IN OUT NOCOPY BLOB,
                       p_remote_file    IN            VARCHAR2,
                       p_force_binary   IN            BOOLEAN DEFAULT TRUE
                      )
    IS
        v_save_method   VARCHAR2(1);
        v_read_cnt      INTEGER;
        v_write_cnt     INTEGER;
        v_lob_length    INTEGER;
        v_index         INTEGER;
        v_chunk         RAW(32767);

        v_data          UTL_TCP.connection;
    BEGIN
        IF p_force_binary AND p_connection.transfer_method != 'I'
        THEN
            v_save_method  := p_connection.transfer_method;
            write_to_log('Saving current transfer method: ' || v_save_method, TRUE);
            write_to_log('Setting transfer method for BLOB binary transfer', TRUE);
            set_transfer_method(p_connection, 'IMAGE');
        END IF;

        v_data        := get_passive_connection(p_connection);

        send_ftp_command(p_connection, 'STOR', p_remote_file);

        write_to_log('storing file on: ' || v_data.remote_host || ':' || v_data.remote_port, TRUE);

        v_lob_length  := DBMS_LOB.getlength(p_local_blob);
        v_index       := 1;

        WHILE v_index <= v_lob_length
        LOOP
            v_read_cnt   := 32767;
            DBMS_LOB.read(p_local_blob, v_read_cnt, v_index, v_chunk);
            v_write_cnt  := UTL_TCP.write_raw(v_data, v_chunk, NULL);
            UTL_TCP.flush(v_data);
            v_index      := v_index + v_write_cnt;
        END LOOP;

        write_to_log(
            'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
            TRUE);
        UTL_TCP.close_connection(v_data);
        read_reply(p_connection);

        IF p_force_binary AND v_save_method != 'I'
        THEN
            write_to_log('Restoring previous transfer method:' || v_save_method, TRUE);
            set_transfer_method(p_connection, v_save_method, SUBSTR(v_save_method, 3, 1));
        END IF;
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in put_blob(' || p_remote_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            write_to_log(
                'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
                TRUE);
            UTL_TCP.close_connection(v_data);

            RAISE;
    END put_blob;

    -- Read a remote file directly into a local clob
    -- there will be no OS file created locally by this
    -- by default the current transfer method will be used
    -- but it can be changed as part of this procedure call
    PROCEDURE get_clob(p_connection     IN OUT NOCOPY connection,
                       p_remote_file    IN            VARCHAR2,
                       p_local_clob     IN OUT NOCOPY CLOB,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      )
    IS
        v_char_cnt   INTEGER;
        v_chunk      VARCHAR2(32767);
        v_error      VARCHAR(32767);
        v_data       UTL_TCP.connection;
    BEGIN
        v_data  := get_passive_connection(p_connection);

        set_transfer_method(p_connection, p_trans_method);

        send_ftp_command(p_connection, 'RETR', p_remote_file);

        write_to_log('retrieving file from: ' || v_data.remote_host || ':' || v_data.remote_port,
                     TRUE
                    );

        LOOP
            BEGIN
                v_char_cnt  := UTL_TCP.read_text(v_data, v_chunk, 32767);
                DBMS_LOB.writeappend(p_local_clob, v_char_cnt, v_chunk);
            EXCEPTION
                WHEN UTL_TCP.end_of_input
                THEN
                    EXIT;
                WHEN OTHERS
                THEN
                    RAISE;
            END;
        END LOOP;

        write_to_log(
            'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
            TRUE);
        UTL_TCP.close_connection(v_data);
        read_reply(p_connection);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in get_clob(' || p_remote_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            write_to_log(
                'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
                TRUE);
            UTL_TCP.close_connection(v_data);

            RAISE;
    END get_clob;

    -- Read a remote file directly into a local clob
    -- there will be no OS file created locally by this
    -- by default the current transfer method will be used
    -- but it can be changed as part of this procedure call
    FUNCTION get_clob(p_connection     IN OUT NOCOPY connection,
                      p_remote_file    IN            VARCHAR2,
                      p_trans_method   IN            VARCHAR2 DEFAULT NULL
                     )
        RETURN CLOB
    IS
        v_clob   CLOB;
    BEGIN
        DBMS_LOB.createtemporary(v_clob, TRUE);

        get_clob(p_connection, p_remote_file, v_clob, p_trans_method);

        RETURN v_clob;
    EXCEPTION
        WHEN OTHERS
        THEN
            IF DBMS_LOB.istemporary(v_clob) = 1
            THEN
                IF DBMS_LOB.ISOPEN(v_clob) = 1
                THEN
                    DBMS_LOB.close(v_clob);
                END IF;

                DBMS_LOB.freetemporary(v_clob);
            END IF;

            RAISE;
    END get_clob;

    -- Read a remote file directly into a local blob
    -- there will be no OS file created locally by this
    -- by default the current transfer method will be ignored
    -- and the procedure will temporarily switch to Image/Binary transfer
    -- if force_binary is FALSE then the current method will be used.
    PROCEDURE get_blob(p_connection     IN OUT NOCOPY connection,
                       p_remote_file    IN            VARCHAR2,
                       p_local_blob     IN OUT NOCOPY BLOB,
                       p_force_binary   IN            BOOLEAN DEFAULT TRUE
                      )
    IS
        v_save_method   VARCHAR2(1);
        v_byte_cnt      INTEGER;
        v_chunk         RAW(32767);
        v_error         VARCHAR(32767);
        v_data          UTL_TCP.connection;
    BEGIN
        IF p_force_binary AND p_connection.transfer_method != 'I'
        THEN
            v_save_method  := p_connection.transfer_method;
            write_to_log('Saving current transfer method: ' || v_save_method, TRUE);
            write_to_log('Setting transfer method for BLOB binary transfer', TRUE);
            set_transfer_method(p_connection, 'IMAGE');
        END IF;

        v_data  := get_passive_connection(p_connection);

        send_ftp_command(p_connection, 'RETR', p_remote_file);

        write_to_log('retrieving file from: ' || v_data.remote_host || ':' || v_data.remote_port,
                     TRUE
                    );

        LOOP
            BEGIN
                v_byte_cnt  := UTL_TCP.read_raw(v_data, v_chunk, 32767);
                DBMS_LOB.writeappend(p_local_blob, v_byte_cnt, v_chunk);
            EXCEPTION
                WHEN UTL_TCP.end_of_input
                THEN
                    EXIT;
                WHEN OTHERS
                THEN
                    RAISE;
            END;
        END LOOP;

        write_to_log(
            'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
            TRUE);
        UTL_TCP.close_connection(v_data);
        read_reply(p_connection);

        IF p_force_binary AND v_save_method != 'I'
        THEN
            write_to_log('Restoring previous transfer method:' || v_save_method, TRUE);
            set_transfer_method(p_connection, v_save_method, SUBSTR(v_save_method, 3, 1));
        END IF;
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in get_blob(' || p_remote_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            write_to_log(
                'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
                TRUE);
            UTL_TCP.close_connection(v_data);

            RAISE;
    END get_blob;

    -- Read a remote file directly into a local blob
    -- there will be no OS file created locally by this
    -- by default the current transfer method will be ignored
    -- and the procedure will temporarily switch to Image/Binary transfer
    -- if force_binary is FALSE then the current method will be used.
    FUNCTION get_blob(p_connection     IN OUT NOCOPY connection,
                      p_remote_file    IN            VARCHAR2,
                      p_force_binary   IN            BOOLEAN DEFAULT TRUE
                     )
        RETURN BLOB
    IS
        v_blob   BLOB;
    BEGIN
        DBMS_LOB.createtemporary(v_blob, TRUE);

        sdsftp.get_blob(p_connection, p_remote_file, v_blob, p_force_binary);

        RETURN v_blob;
    EXCEPTION
        WHEN OTHERS
        THEN
            IF DBMS_LOB.istemporary(v_blob) = 1
            THEN
                IF DBMS_LOB.ISOPEN(v_blob) = 1
                THEN
                    DBMS_LOB.close(v_blob);
                END IF;

                DBMS_LOB.freetemporary(v_blob);
            END IF;

            RAISE;
    END get_blob;

    -- Given a local file, send it to the remote host to be saved as a file
    -- by default the current transfer method will be used but it can be 
    -- overridden.  The procedure is overloaded 3 ways based on available values.
    -- This version reads from a local utl_file pointer and sends that file
    -- to the remote server.
    -- with directory and local file, with utl_file pointer, 
    -- or just file names with current directory
    PROCEDURE put_file(p_connection     IN OUT NOCOPY connection,
                       p_local_file     IN            UTL_FILE.file_type,
                       p_remote_file    IN            VARCHAR2,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      )
    IS
        v_byte_cnt    INTEGER;
        v_textchunk   VARCHAR2(32767);
        v_binchunk    RAW(32767);
        v_data        UTL_TCP.connection;
    BEGIN
        set_transfer_method(p_connection, p_trans_method);

        v_data  := get_passive_connection(p_connection);

        send_ftp_command(p_connection, 'STOR', p_remote_file);

        write_to_log('writing file to: ' || v_data.remote_host || ':' || v_data.remote_port, TRUE);

        LOOP
            BEGIN
                IF p_connection.transfer_method = 'I'
                THEN
                    UTL_FILE.get_raw(file => p_local_file, buffer => v_binchunk, len => 32767);
                    v_byte_cnt  := UTL_TCP.write_raw(v_data, v_binchunk, NULL);
                ELSE
                    UTL_FILE.get_line(file => p_local_file, buffer => v_textchunk, len => 32767);
                    v_byte_cnt  := UTL_TCP.write_text(v_data, v_textchunk, NULL);
                END IF;
            EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                    EXIT;
                WHEN OTHERS
                THEN
                    RAISE;
            END;
        END LOOP;

        write_to_log(
            'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
            TRUE);
        UTL_TCP.close_connection(v_data);
        read_reply(p_connection);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in put_file(' || p_remote_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            write_to_log(
                'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
                TRUE);
            UTL_TCP.close_connection(v_data);

            RAISE;
    END put_file;

    -- Given a local file, send it to the remote host to be saved as a file
    -- by default the current transfer method will be used but it can be 
    -- overridden.  The procedure is overloaded 3 ways based on available values.
    -- This version reads from a file from a local directory and sends that file
    -- to the remote server.
    PROCEDURE put_file(p_connection        IN OUT NOCOPY connection,
                       p_local_directory   IN            VARCHAR2,
                       p_local_file        IN            VARCHAR2,
                       p_remote_file       IN            VARCHAR2,
                       p_trans_method      IN            VARCHAR2 DEFAULT NULL
                      )
    IS
        v_file   UTL_FILE.file_type;
    BEGIN
        IF p_local_directory IS NULL
        THEN
            write_to_log(
                   'Local directory not set prior to get_file('
                || p_local_directory
                || ','
                || p_local_file
                || ')');
            raise_application_error(
                -20001,
                'SDSFTP.PUT_FILE - Local directory must be specified to GET files',
                TRUE);
        END IF;

        IF p_remote_file IS NULL
        THEN
            write_to_log(
                   'NULL file name used for put_file('
                || p_local_directory
                || ','
                || p_local_file
                || ')');
            raise_application_error(-20001,
                                    'SDSFTP.GET_FILE - Can not PUT a file into a NULL file name',
                                    TRUE
                                   );
        END IF;

        IF NVL(UPPER(p_trans_method), p_connection.transfer_method) IN ('BINARY', 'IMAGE', 'I')
        THEN
            v_file  := UTL_FILE.fopen(p_local_directory, p_local_file, 'rb', 32767);
        ELSE
            v_file  := UTL_FILE.fopen(p_local_directory, p_local_file, 'r', 32767);
        END IF;

        sdsftp.put_file(p_connection    => p_connection,
                        p_local_file    => v_file,
                        p_remote_file   => p_remote_file,
                        p_trans_method  => p_trans_method
                       );
        UTL_FILE.fclose(v_file);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in put_file(' || p_remote_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            IF UTL_FILE.is_open(v_file)
            THEN
                UTL_FILE.fclose(v_file);
            END IF;

            RAISE;
    END put_file;

    -- Given a local file, send it to the remote host to be saved as a file
    -- by default the current transfer method will be used but it can be 
    -- overridden.  The procedure is overloaded 3 ways based on available values.
    -- This version reads from a file from the current local directory.
    PROCEDURE put_file(p_connection     IN OUT NOCOPY connection,
                       p_local_file     IN            VARCHAR2,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      )
    IS
    BEGIN
        IF p_connection.local_directory IS NULL
        THEN
            write_to_log('Local directory not set prior to put_file(' || p_local_file || ')');
            raise_application_error(
                -20001,
                'SDSFTP.PUT_FILE - Local directory must be specified to PUT files',
                TRUE);
        END IF;

        sdsftp.put_file(p_connection       => p_connection,
                        p_local_directory  => p_connection.local_directory,
                        p_local_file       => p_local_file,
                        p_remote_file      => p_local_file,
                        p_trans_method     => p_trans_method
                       );
    END put_file;

    -- Read remote file to local UTL_FILE file handle
    -- by default the current transfer mode will be used but it can be changed
    -- This is the driver function that all other "get_file" procedures invoke
    PROCEDURE get_file(p_connection     IN OUT NOCOPY connection,
                       p_remote_file    IN            VARCHAR2,
                       p_local_file     IN OUT NOCOPY UTL_FILE.file_type,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      )
    IS
        v_transfer_cnt   INTEGER;                              -- bytes for raw, characters for Text
        v_textchunk      VARCHAR2(32767);
        v_binchunk       RAW(32767);
        v_data           UTL_TCP.connection;
    BEGIN
        set_transfer_method(p_connection, p_trans_method);

        v_data  := get_passive_connection(p_connection);

        send_ftp_command(p_connection, 'RETR', p_remote_file);

        write_to_log('retrieving file from: ' || v_data.remote_host || ':' || v_data.remote_port,
                     TRUE
                    );

        LOOP
            BEGIN
                IF p_connection.transfer_method = 'I'
                THEN
                    v_transfer_cnt  := UTL_TCP.read_raw(v_data, v_binchunk, 32767);
                    UTL_FILE.put_raw(file => p_local_file, buffer => v_binchunk, autoflush => TRUE);
                ELSE
                    v_transfer_cnt  := UTL_TCP.read_text(v_data, v_textchunk, 32767);
                    UTL_FILE.put(file => p_local_file, buffer => v_textchunk);
                END IF;

                UTL_FILE.fflush(p_local_file);
            EXCEPTION
                WHEN UTL_TCP.end_of_input
                THEN
                    EXIT;
                WHEN OTHERS
                THEN
                    RAISE;
            END;
        END LOOP;

        write_to_log(
            'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
            TRUE);
        UTL_TCP.close_connection(v_data);
        read_reply(p_connection);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in get_file(' || p_remote_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            write_to_log(
                'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
                TRUE);
            UTL_TCP.close_connection(v_data);

            RAISE;
    END get_file;

    -- Read remote file to selected local directory and new local name
    -- by default the current transfer mode will be used but it can be changed
    PROCEDURE get_file(p_connection        IN OUT NOCOPY connection,
                       p_remote_file       IN            VARCHAR2,
                       p_local_directory   IN            VARCHAR2,
                       p_local_file        IN            VARCHAR2,
                       p_trans_method      IN            VARCHAR2 DEFAULT NULL
                      )
    IS
        v_file   UTL_FILE.file_type;
    BEGIN
        IF p_local_directory IS NULL
        THEN
            write_to_log('Local directory not set prior to get_file(' || p_remote_file || ')');
            raise_application_error(
                -20001,
                'SDSFTP.GET_FILE - Local directory must be specified to GET files',
                TRUE);
        END IF;

        IF p_local_file IS NULL
        THEN
            write_to_log('NULL file name used for get_file(' || p_remote_file || ')');
            raise_application_error(-20001,
                                    'SDSFTP.GET_FILE - Can not GET a file into a NULL file name',
                                    TRUE
                                   );
        END IF;

        IF NVL(UPPER(p_trans_method), p_connection.transfer_method) IN ('BINARY', 'IMAGE', 'I')
        THEN
            v_file  := UTL_FILE.fopen(p_local_directory, p_local_file, 'wb', 32767);
        ELSE
            v_file  := UTL_FILE.fopen(p_local_directory, p_local_file, 'w', 32767);
        END IF;

        sdsftp.get_file(p_connection    => p_connection,
                        p_remote_file   => p_remote_file,
                        p_local_file    => v_file,
                        p_trans_method  => p_trans_method
                       );
        UTL_FILE.fclose(v_file);
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in get_file(' || p_remote_file || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            IF UTL_FILE.is_open(v_file)
            THEN
                UTL_FILE.fclose(v_file);
            END IF;

            RAISE;
    END get_file;

    -- Read remote file to current local directory with the same name
    -- by default the current transfer mode will be used but it can be changed
    PROCEDURE get_file(p_connection     IN OUT NOCOPY connection,
                       p_remote_file    IN            VARCHAR2,
                       p_trans_method   IN            VARCHAR2 DEFAULT NULL
                      )
    IS
    BEGIN
        IF p_connection.local_directory IS NULL
        THEN
            write_to_log('Local directory not set prior to get_file(' || p_remote_file || ')');
            raise_application_error(
                -20001,
                'SDSFTP.GET_FILE - Local directory must be specified to GET files',
                TRUE);
        END IF;

        sdsftp.get_file(p_connection       => p_connection,
                        p_remote_file      => p_remote_file,
                        p_local_directory  => p_connection.local_directory,
                        p_local_file       => p_remote_file,
                        p_trans_method     => p_trans_method
                       );
    END get_file;

    -- Given a path, return the list of files, but default it return the full 
    -- file information but that may be changed to just the file names themselves.
    -- Supported transfer methods are ASCII and EBCDIC only.
    -- The current transfer method will be restored after listing if it was
    -- changed by this routine.
    -- Note the FTP RFC does not specify what the format will be so all information
    -- is returned simply as a collection of strings.
    PROCEDURE get_file_list(p_connection     IN OUT NOCOPY connection,
                            p_remote_path    IN            VARCHAR2,
                            p_list              OUT        file_list,
                            p_names_only     IN            BOOLEAN DEFAULT FALSE,
                            p_trans_method   IN            VARCHAR2 DEFAULT 'ASCII'
                           )
    IS
        v_transfer_cnt   INTEGER;                              -- bytes for raw, characters for Text
        v_textchunk      VARCHAR2(32767);
        v_binchunk       RAW(32767);
        v_data           UTL_TCP.connection;
        v_save_method    VARCHAR2(1) := NULL;
        v_index          INTEGER := 1;
    BEGIN
        IF p_trans_method NOT IN ('A', 'ASCII', 'E', 'EBCDIC')
        THEN
            raise_application_error(-20001,
                                    'SDSFTP.get_file_list INVALID method: ' || p_trans_method,
                                    TRUE
                                   );
        END IF;

        IF p_trans_method != get_transfer_method(p_connection)
        THEN
            v_save_method  := p_connection.transfer_method;
            write_to_log('Saving current transfer method: ' || v_save_method, TRUE);
            write_to_log('Setting ' || p_trans_method || ' method for remote file listing', TRUE);
            set_transfer_method(p_connection, p_trans_method);
        END IF;

        v_data  := get_passive_connection(p_connection);

        IF p_names_only
        THEN
            send_ftp_command(p_connection, 'NLST', p_remote_path);
        ELSE
            send_ftp_command(p_connection, 'LIST', p_remote_path);
        END IF;

        write_to_log(
            'retrieving file list from: ' || v_data.remote_host || ':' || v_data.remote_port,
            TRUE);

        p_list.delete;

        LOOP
            BEGIN
                v_transfer_cnt   := UTL_TCP.read_line(v_data, v_textchunk, TRUE, FALSE);
                p_list(v_index)  := v_textchunk;
                v_index          := v_index + 1;
            EXCEPTION
                WHEN UTL_TCP.end_of_input
                THEN
                    EXIT;
                WHEN OTHERS
                THEN
                    RAISE;
            END;
        END LOOP;

        write_to_log(
            'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
            TRUE);
        UTL_TCP.close_connection(v_data);
        read_reply(p_connection);

        IF v_save_method IS NOT NULL
        THEN
            set_transfer_method(p_connection, v_save_method);
        END IF;
    EXCEPTION
        WHEN OTHERS
        THEN
            write_to_log('ERROR in get_file_list(' || p_remote_path || ')');
            write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);

            write_to_log(
                'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
                TRUE);
            UTL_TCP.close_connection(v_data);

            IF v_save_method IS NOT NULL
            THEN
                set_transfer_method(p_connection, v_save_method);
            END IF;

            RAISE;
    END get_file_list;


    -- Given a path, return the list of files, but default it return the full 
    -- file information but that may be changed to just the file names themselves.
    -- Supported transfer methods are ASCII and EBCDIC only.
    -- The current transfer method will be restored after listing if it was
    -- changed by this routine.
    -- Note the FTP RFC does not specify what the format will be so all information
    -- is returned simply as a collection of strings.
    FUNCTION get_file_list(p_connection     IN OUT NOCOPY connection,
                           p_remote_path    IN            VARCHAR2,
                           p_names_only     IN            BOOLEAN DEFAULT FALSE,
                           p_trans_method   IN            VARCHAR2 DEFAULT 'ASCII'
                          )
        RETURN file_list
    IS
        p_list   file_list;
    BEGIN
        get_file_list(p_connection, p_remote_path, p_list, p_names_only, p_trans_method);
        RETURN p_list;
    END;
END sdsftp;
/

Select all Open in new window

How to FTP with Oracle PL/SQL

Sample Usage

Explanation of Example

Overview of the API and the FTP protocol

Additional Example

Reference and Source Code

Oracle

FTP

PL/SQL

Oracle Database

About The Author

More Articles From sdstuber

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Oracle Database Experts