Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

FTP From PL/SQL - A description of two methods for triggering FTP jobs directly from PL/SQL.



Gaby said...

Just to say hellow!

Trevor Woolnough said...

Great code, very useful thanks. Slight buggette. You need to do a get_reply after doing a transfer to pickup the 226 Transfer complete message otherwise you can;t do subsequent get or puts as your reply processing trys to interpret the 226 as a port message when entering passive mode

Mike said...

Great example. My only issue was that because this was my first time using UTL_FILE, I didn't know that UTL_FILE only has access to directories listed in INIT.ORA (utl_file_dir = ). Otherwise, things worked great!

Tim... said...

Hi.

The UTL_FILE_DIR parameter does not need to be specified if a directory object is used. It is only relevant if you use an explicit path, which is not the recommended way to use UTL_FILE in 9i onwards.

Cheers

Tim...

Tim... said...

Trevor Woolnough:

I've amended the ftp.pkb code and credited you in the amendment history for spotting the issue.

Cheers

Tim...

Dario Romare said...

After a long and ineffective search through DBMS_PIPE, UTL_TCP, DBMS_JOB, UTL_INADDR etc., it was great to find this very useful, ready-made code. Thanks !

Sujay said...

Tim,
Wonderful code. Just one issue with this. It cannot put a zero byte file. It throws an exception. I have modified the code slightly in get_local_ascii_data and get_local_binary_data to fix this and it works fine.

Barry Chase said...

I would like to share my work with anyone who wants it. I have done a lot of work with this code in combination with several other sources researched. We are currently using my code in our production environment. It is on its 3rd revision. Free for the taking and free for those who wish to make it even better.

http://www.myoracleportal.com

Minimum requirement is Oracle 9iR2

FTP_INTERFACE v.3.0.0 with new features ::

* LS/DIR commands to provide directory listing retrieval
* Oracle Applications integration

And still supporting ::

* Binary and Ascii support
* Session Timeout control
* Server Connection valid support
* Mainframe support
* PUT, GET, REMOVE, and RENAME functionality
* ...and more.

Tim... said...

Cool! :)

Cheers

Tim...

Barry Chase said...

Thanks. Couldn't have done it without all the work others put into it. I just keeping building on to it, adding more features. Just hope it helps others out and saves them a ton of work by not having to create a fully functional setup on their own.

~Barry

Andy said...

Useful ..Thanks.

Pawel said...

Tim,
Can we use code from your site in production DB inside our organization?
How about if it will become part of commercial software ?
Is there any rules or licence available on oracle-base.com?

Tim... said...

Then you have to pay me one billion dollars (laughs like DR Evil!)

Just kidding. There's no license, so you can use it in production at no cost, so long as you don't expect me to support it.

Cheers

Tim...

Steve said...

With the ftp package installed in the database, the program sends a blank line with /r/n on the ftp.login line and then does a reset. Do you know where the problem is?

Thanks

Kevin Phillips said...

Brilliant bit of code, really easy to follow.

But I have spent a day fathoming out why I kept getting ORA-29260 after the PASV call. Others on various web forums seem to be getting this.

In send_command I changed
l_result := UTL_TCP.write_line(p_conn, p_command);
to
l_result := UTL_TCP.write_text(p_conn, p_command||utl_tcp.crlf, length(p_command||utl_tcp.crlf))

And it works. Maybe a problem with utl_tcp not doing what the code comment for write_line says it will (appending of the CR/LF you specified when the connection was opened)?

I had it working with original code on some FTP servers and not with others. Could it be a mismatch between sending from a Windows DB machine to a Unix FTP server?

I found that Windows->Windows worked OK with original code, buy only my changes get my file to a Unix FTP Server.

Tim... said...

Kevin Phillips: I've included your suggested fix in the code, but it's commented out. I've credited you in the amendment history for the suggestion.

Cheers

Tim...

Julian said...

Hi there,

first i'd like to say thanks for this neat package. Saved me a lot of time implementing such by myself ;)

But, I found an failure: To fully support multiple transfers per connection, you should add a call to get_reply() at the end of the function list(), because the ftp-server responses a '226 Transfer Complete' and the next time, get_passive() is invoked, the last line of g_reply is just that 226. The substr() doesn't find any port-information and the result should be clear to everybody ;) And while your on the code, you could also add an call to UTL_TCP.close_connection(l_conn) after the get_reply() in the same function, or else it keeps open (if the passive connection doesn't resets the socket, I haven't tested it).

It's the same for put_remote_*_data() where the call to get_reply() isn't implemented either. Important at this point is, that the call to get_reply() has to be made after the call of UTL_TCP.close_connection(l_conn), because otherwise the ftp-server doesn't send a response (because he's waiting for more input from the passive connection) and the program hangs until a timeout raises an exception.

Anyway, good work there :)

Julian, Bavaria, Germany (so please excuse if this comment isn't a well english at all)

Tim... said...

Hi.

Your suggestion for the "list" function works and I've amended the code.

Adding the get_reply() call to put_remote_*_data() routines, whether it's before or after the connection close, causes the whole process to hang for me, so either I've misunderstood what you said, or you've not actually tried this.

Cheers

Tim...

Julian said...

Hi Tim,

sure I've tested it. And it worked fine for me. I'm working under ora10g v10.2.0.1. Also, I've tested it with various FTP-servers (i.e. Windows FTP-Service, FileZilla Server, pure-ftpd etc.).

If you want to have a look at my code, you can get it here: http://files.sirskunkalot.de/KL_FTP.rar

I also made a new function 'list_files' which retrieves only the filenames of a given directory's content. And some other, minor changes. They are described in the headers of the files.

Greets,

Julian Wagener

Wendy said...

I need to realize a FTP to a Server that this formed in mode Active

Thanks

dam said...

Hi Tim, Gread code ? I've started to test.It works very good :)
Do you have an idea to get only the name of the files in the directory ( not all , like rights, names of groups, ) with the fonction list ???

email: zopinette93@gmail.com
Dam.

Andrew Hateley said...

Hi Tim, I have been using your code successfully for a couple of years and have just got the latest version. Thanks so much for making this available.

An issue which still exists in the 03-Mar-2008 Update is an exception is thrown when trying to send a 0-byte file.

A workaround for this is to modify the get_local_ascii_data and get_local_binary data functions in the package body as such:

-- --------------------------------------------------------------------------
FUNCTION get_local_ascii_data (p_dir IN VARCHAR2,
p_file IN VARCHAR2)
RETURN CLOB IS
-- --------------------------------------------------------------------------
l_bfile BFILE;
l_data CLOB;
BEGIN
DBMS_LOB.createtemporary (lob_loc => l_data,
cache => TRUE,
dur => DBMS_LOB.call);

l_bfile := BFILENAME(p_dir, p_file);
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
-- AJH BugFix for 0 byte files
if (DBMS_LOB.getlength(l_bfile) > 0) then
DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
end if;
DBMS_LOB.fileclose(l_bfile);

RETURN l_data;
END;
-- --------------------------------------------------------------------------



-- --------------------------------------------------------------------------
FUNCTION get_local_binary_data (p_dir IN VARCHAR2,
p_file IN VARCHAR2)
RETURN BLOB IS
-- --------------------------------------------------------------------------
l_bfile BFILE;
l_data BLOB;
BEGIN
DBMS_LOB.createtemporary (lob_loc => l_data,
cache => TRUE,
dur => DBMS_LOB.call);

l_bfile := BFILENAME(p_dir, p_file);
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
-- DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
-- AJH BugFix for 0 byte files
if (DBMS_LOB.getlength(l_bfile) > 0) then
DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
end if;
DBMS_LOB.fileclose(l_bfile);

RETURN l_data;
END;
-- --------------------------------------------------------------------------

This successfully resolved the issue for us, thanks again for this extremely valuable public domain code!

Regards,
Andrew Hateley.

Rajeev said...

Tim
Does your code support ftp of file containing multibyte characters. I tried it against a file containing english and japanese characters. In binary mode, file gets transfered well but it is size is bigger than original file and format is mesy. In ascii mode, it produces a garbage file on ftp location.
I even tried put_direct, it behaves similar to put

Any suggestions??

regards
Rajeev

Mark said...

Im getting this error "ORA-29261: bad argument". I uncommented the 29260 line as suggested but that did not help.

Mark said...

I made the following changes to the code to fix the fact that when a file is not found the get_reply raise ora-20000 but the pasv connection is not closed. Notice the exception handler in the bottom of get_remote_binary_data and get_remote_ascii_data.

-- --------------------------------------------------------------------------
FUNCTION get_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection,
p_file IN VARCHAR2)
RETURN CLOB IS
-- --------------------------------------------------------------------------
l_conn UTL_TCP.connection;
l_amount PLS_INTEGER;
l_buffer VARCHAR2(32767);
l_data CLOB;
BEGIN
DBMS_LOB.createtemporary (lob_loc => l_data,
cache => TRUE,
dur => DBMS_LOB.call);

l_conn := get_passive(p_conn);
send_command(p_conn, 'RETR ' || p_file, TRUE);
logout(l_conn, FALSE);

BEGIN
LOOP
l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767);
DBMS_LOB.writeappend(l_data, l_amount, l_buffer);
END LOOP;
EXCEPTION
WHEN UTL_TCP.END_OF_INPUT THEN
NULL;
WHEN OTHERS THEN
NULL;
END;

get_reply(p_conn);
UTL_TCP.close_connection(l_conn);

RETURN l_data;

-- Mark Reichman 26-Jun-2008
-- added exception handler to close connection
exception
when OTHERS then
UTL_TCP.close_connection(l_conn);
raise;
END;
-- --------------------------------------------------------------------------



-- --------------------------------------------------------------------------
FUNCTION get_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection,
p_file IN VARCHAR2)
RETURN BLOB IS
-- --------------------------------------------------------------------------
l_conn UTL_TCP.connection;
l_amount PLS_INTEGER;
l_buffer RAW(32767);
l_data BLOB;
BEGIN
DBMS_LOB.createtemporary (lob_loc => l_data,
cache => TRUE,
dur => DBMS_LOB.call);

l_conn := get_passive(p_conn);
send_command(p_conn, 'RETR ' || p_file, TRUE);

BEGIN
LOOP
l_amount := UTL_TCP.read_raw (l_conn, l_buffer, 32767);
DBMS_LOB.writeappend(l_data, l_amount, l_buffer);
END LOOP;
EXCEPTION
WHEN UTL_TCP.END_OF_INPUT THEN
NULL;
WHEN OTHERS THEN
NULL;
END;

get_reply(p_conn);
UTL_TCP.close_connection(l_conn);

RETURN l_data;

-- Mark Reichman 26-Jun-2008
-- added exception handler to close connection
exception when OTHERS then
UTL_TCP.close_connection(l_conn);
raise;
END;
-- --------------------------------------------------------------------------

Tim... said...

Hi.

Thanks for the suggestion. I've added it to the code and credited you.

Cheers

Tim...

Juliana said...

when I run this pck, return error

PLS-00801: internal error(ph2exp:case)

What can I do?

Birthe said...

Hi Tim,
thanks for thegreat ftp pck. Mabe you could help. I a don't get any data back, if I want to list the directory. After a while a disonnect takes place.
Have you any idea ?

Thanks.
Birthe

Roman said...

Hello!

I try to run example:

DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login('localhost', '21', 'anonymous', 'ftppwd');
ftp.binary(p_conn => l_conn);
ftp.get(p_conn => l_conn,
p_from_file => 'test.jpg',
p_to_dir => 'FOLDER',
p_to_file => 'test.jpg');
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;

But it hangs. I debug it and see that it hangs there:
LOOP
l_amount := UTL_TCP.read_raw (l_conn, l_buffer, 32767); -- in this string
DBMS_LOB.writeappend(l_data, l_amount, l_buffer);
END LOOP;

Although FTP server writes in log
[3036] 150 Opening BINARY mode data connection for file transfer.
[3036] 226 Transfer complete

Where is wrong?

pooja said...

Hi Tim,

Great piece of code!

I have a small problem though....
Our implementation has an application server and a separate database server.
I'm able to ftp files from remote server to database server using your code but not able ftp files from the remote server to the application server. It throws invalid directory error.

Any idea why this would happen?

Thanks
Pooja

Julian said...

Hi Tim,

i saw you implemented my suggestion with the get_reply() in the put_remote_*-functions. The reason, that it hangs for you is probably, that I put the call after closing the connection, because some FTP-servers require to close the passive connection first, before answering any call to the control-connection. Here's my code:

-- --------------------------------------------------------------------------
PROCEDURE put_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection,
p_file IN VARCHAR2,
p_data IN CLOB) IS
-- --------------------------------------------------------------------------
l_conn UTL_TCP.connection;
l_result PLS_INTEGER;
l_buffer VARCHAR2(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_clob_len INTEGER;
BEGIN
l_conn := get_passive(p_conn);
send_command(p_conn, 'STOR ' || p_file);

l_clob_len := DBMS_LOB.getlength(p_data);

WHILE l_pos <= l_clob_len LOOP
DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
l_result := UTL_TCP.write_text(l_conn, l_buffer, LENGTH(l_buffer));
UTL_TCP.flush(l_conn);
l_pos := l_pos + l_amount;
END LOOP;
UTL_TCP.close_connection(l_conn);
get_reply(p_conn);
EXCEPTION
WHEN OTHERS THEN
UTL_TCP.close_connection(l_conn);
RAISE;
END;
-- --------------------------------------------------------------------------

Further I wrote (merely copy+pasted :)) some code to get a list of the filenames only, instead of getting all information like owner etc. It's very useful for retrieval of an unknown amount of files/a whole dir where the filenames are unknown/change.

-- --------------------------------------------------------------------------
PROCEDURE list_files (p_conn IN OUT NOCOPY UTL_TCP.connection,
p_dir IN VARCHAR2,
p_list OUT t_string_table) AS
-- --------------------------------------------------------------------------
l_conn UTL_TCP.connection;
l_list t_string_table := t_string_table();
l_reply_code VARCHAR2(3) := NULL;
BEGIN
l_conn := get_passive(p_conn);
send_command(p_conn, 'NLST ' || p_dir);

BEGIN
LOOP
l_list.extend;
l_list(l_list.last) := UTL_TCP.get_line(l_conn, TRUE);
debug(l_list(l_list.last));
IF l_reply_code IS NULL THEN
l_reply_code := SUBSTR(l_list(l_list.last), 1, 3);
END IF;
IF SUBSTR(l_reply_code, 1, 1) = '5' THEN
RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last));
ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND
SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN UTL_TCP.END_OF_INPUT THEN
NULL;
END;
UTL_TCP.close_connection(l_conn);
get_reply(p_conn);

l_list.delete(l_list.last);
p_list := l_list;
EXCEPTION
WHEN OTHERS THEN
UTL_TCP.close_connection(l_conn);
RAISE;
END list_files;
-- --------------------------------------------------------------------------

hth,

Julian

Lionel said...

I have been using to code to ftp file from unix to unix servers but failed to ftp files to mainframe. only the first line of the file got transferred. Any idea?

Nantha said...

Hi Sujay, Could please you tell me what change you made to send the zero byte files?

Daniel said...

Exception in get_loacl_binary_data:

When I have a local file with size=0 then I get an Exception. So I wrote this patch:

- DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
+ if DBMS_LOB.getlength(l_bfile) > 0
+ then
+ DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
+ end if;

Apart from that, thanks for the work...

Bireley said...

Hi Tim and Everyone,
Thanks so much for this code. Had to add ACL script for 11g. Because of new security restrictions on UTL_TCP you will get ORA-24247.
Sources used for this were
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#insertedID0

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-security.html
Access Control Lists for UTL_TCP/HTTP/SMTP (about two thirds down the page)

http://www.dba-oracle.com/t_ora_24247_network_access_denied_by_access_control_list_tips.htm

http://johanlouwers.blogspot.com/2009/01/ora-24247-network-access-denied-by.html

Here's my script on its first iteration.
-- Creating an Access Control List (ACL) for 11G using the ftp api
-- Run logged in as SYS as SYSDBA.
begin
dbms_network_acl_admin.create_acl (
acl => 'utltcppkg.xml',
description => 'Connect to external servers using UTL_TCP',
principal => 'CONNECT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
commit;
dbms_network_acl_admin.add_privilege (
acl => 'utltcppkg.xml',
principal => 'some_user',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
commit;
dbms_network_acl_admin.assign_acl (
acl => 'utltcppkg.xml',
host => 'ftphost',
lower_port => 21,
upper_port => 21);
commit;

-- The following is needed for the ftp passive data connection in get_passive
dbms_network_acl_admin.assign_acl (
acl => 'utltcppkg.xml',
host => 'xxx.xxx.xxx.xx', -- IP of ftp host
lower_port => xx, -- lower random port range from server and/or firewall
upper_port => xx); -- upper random port range from server and/or firewall
commit;

end;

grant execute on utl_tcp to some_user;

grant execute on utl_smtp to some_user;

grant execute on utl_file to some_user;

revoke execute on utl_tcp from public;

revoke execute on utl_smtp from public;

revoke execute on utl_file from public;

-- Recompile sys schema objects if necessary after revoke from public
@@'$ORACLE_HOME/rdbms/admin/utlrp.sql'

Amit said...

Do you have any solution for mget or mdelete in non-interavtive mode?

Thanks.

Tim... said...

No I don;t have a solution for this.

Sorry.

Tim...

Jay said...

Hi Tim,

Whenever i do below as APPS user:
l_conn := ftp.login
(p_host => 'FTPTEST',
p_port => '21',
p_user => 'abcd',
p_pass => 'abcd');

I get the following error:
ORA-29260: network error: TNS:operation timed out

I am able to open the FTP site without any issue from 3rd party s/w.

John Duncan said...

Great code here, I am using this for a project right now. Thanks for making this public!

I enhanced one type of error check.

Everywhere that did
IF SUBSTR(l_reply_code, 1, 1) = '5'
I changed to
IF SUBSTR(l_reply_code, 1, 1) in ('4', '5')

400 level messages are the same severity as 500 level messages. I have not experienced any 400 level messages in my testing, but wanted to be prepared if any did occur in the future.

John Duncan said...

Amit asked about an mget or mdelete capability.
I am handling this in my calling program. But first I cloned the LIST procedure, creating an NLIST procedure (much like Julian did by creating a file_list procedure).

The command is NLST -1 -p. The -1 (one) puts each file on a separate line. The -p appends a slash to the end of the name, if it is a directory. This way I can avoid trying to GET a directory, by skipping names that end with a slash.

FOR i IN 1 .. l_list.count LOOP
l_file := l_list(i);
IF (substr(l_file,length(l_file),1) <> '/'
-- GET, DELETE, PUT, etc. here
END IF;
END LOOP;

You can use additional substr or 'like' to accomplish pattern matching on your file names.

John Duncan said...

p.s. NLST returns just the filenames, not the permissions, date, size, etc. Just a nice clean filename ready to pass to another call. :-)

Dod said...

The get_local_binary_data function was flawed, here's the working one (retrospectively less ugliness too). Errare humanum est, perseverare autem diabolicum...


-- --------------------------------------------------------------------------
FUNCTION get_local_binary_data (p_dir IN VARCHAR2,
p_file IN VARCHAR2)
RETURN BLOB IS
-- --------------------------------------------------------------------------
l_data BLOB;
l_fd UTL_FILE.FILE_TYPE;
l_buff RAW(32767);
BEGIN
DBMS_LOB.createtemporary (lob_loc => l_data,
cache => TRUE,
dur => DBMS_LOB.call);

l_fd := UTL_FILE.FOPEN (p_dir, p_file, 'RB', 32767);
LOOP
BEGIN
UTL_FILE.GET_RAW(l_fd, l_buff);
EXIT WHEN l_buff IS NULL;

DBMS_LOB.WRITEAPPEND(l_data, UTL_RAW.LENGTH(l_buff), l_buff);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE(l_fd);

RETURN l_data;
END;
-- --------------------------------------------------------------------------

Dod said...

And I see my previous post did not go thru...

Well, aside from saying how handy this pkg was, I was pointing out that put_local_*_data supported both directory objects and fully qualified directory paths, while get_local_*_data supported only directory paths, due to the (widely recommended) use of BFILENAME. I coded a workaround for get_local_*_data to handle both, with the downside of using the (deprecated) RAW datatype. My previous post contains get_local_binary_data, here goes get_local_ascii_data:

-- --------------------------------------------------------------------------
FUNCTION get_local_ascii_data (p_dir IN VARCHAR2,
p_file IN VARCHAR2)
RETURN CLOB IS
-- --------------------------------------------------------------------------
l_data CLOB;
l_fd UTL_FILE.FILE_TYPE;
l_buff RAW(32767);
BEGIN
DBMS_LOB.createtemporary (lob_loc => l_data,
cache => TRUE,
dur => DBMS_LOB.call);

l_fd := UTL_FILE.FOPEN (p_dir, p_file, 'R', 32767);
LOOP
BEGIN
UTL_FILE.GET_RAW(l_fd, l_buff);
EXIT WHEN l_buff IS NULL;

DBMS_LOB.APPEND(l_data, TO_CLOB(UTL_RAW.CAST_TO_VARCHAR2(l_buff)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE(l_fd);

RETURN l_data;
END;
-- --------------------------------------------------------------------------



Tim... said...

Hi Dod.

I have a question. You say the get_local_binary_data function is flawed, but you don't say in what way. As a result, I can't test whether your more verbose solution does actually fix anything. I've certainly not had a problem with the current implementation and it uses less code.

As for the modification to get_local_ascii_data, it is Oracle's recommendation that you never use explicit paths, but instead use directory objects. As a result, I won;t be adding this variation to the package, but it will remain in the comments here so people can use it if they choose. :)

Cheers

Tim...

PS. If you have time, please post the issue that the first suggestion is supposed to fix so I can test it. :)

Dod said...

Hey Tim,

Sorry about the confusion: when I said "the get_local_binary_data function is flawed", I was referring to my original post... which didn't go thru! I was actually correcting myself :p

I merely wanted to point out the difference between put_local_*_data and get_local_*_data functions on how they handle the directory param, and provide a workaround to make them behave in the same (permissive) way. My version would simply allow using both a fully qualified path or a directory object.

Thanks for the great work :)
Cheers!

Tim... said...

OK. I see. :)

Cheers

Tim...

Sendil said...

Wonderful ! This works without even changing permission on the file.

Begin
-- This is working fine as long as I set the file to 777 permission , since the original file was created by ftp user under ftp group as 770 permission
-- expecting 777
UTL_FILE.FRENAME('FTP_FROM_DIR', 'VENDOR.txt', 'FTP_TO_DIR', 'VENDOR1.txt', TRUE);
End;

Sendil said...

Thank you for the hard work Tim.

Jai said...

Hi

I like the conecpt, its great. But I need some help.

It tried:

CREATE OR REPLACE DIRECTORY my_docs AS 'U:\Data\Downloads\JG\';
-- Retrieve an ASCII file from a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login('A', '21', 'U', 'P');
ftp.ascii(p_conn => l_conn);
ftp.get(p_conn => l_conn,
p_from_file => '/UATAIMS/AIS/IN/test.txt',
p_to_dir => 'MYDOCS',
p_to_file => 'jg_test_get.txt');
ftp.logout(l_conn);
END;

Get the error:

ORA-29280: invalid directory path
ORA-06512: at "HIGHWAYS.FTP", line 337
ORA-06512: at "HIGHWAYS.FTP", line 468
ORA-06512: at line 6


Does it make a difference that I am running the code not directly on the database server?

Jank said...

Hi,

I have a format problem sending a file to an ftp (mainframe) 250-data was truncated. Using a shell script with the same commands (site ***) to the ftp works fine , any ideas ?

Tim... said...

Jai: As the error implies, one or more of the directory paths does not exist, or you do not have permission to read/write to it. Check the physical directories and permissions.

Jank: Not sure what you mean by 250-data.

Please ask questions in the Forum, NOT in the comments.

Cheers

Tim...

Tim... said...

Piyush: Deleting your comments. Please ask in forum, like the page says.

Rune said...

After sending this command "STOR AA.txt" to open a file to send text to the ftp server reports this error:

503 Bad sequence of commands.

I cant really see anything wrong, but it wont go any further..

I have tried using passive mode, but I suspect I might have some firewall issues as the connetion just hangs.

Pete M said...

I wanted to say thank you for your efforts maintaining this code. I've successfully used it in its unaltered state in a mission-critical implementation in our production environment.

Top marks, really impressive stuff, thanks Tim!

Pete

Mor said...

Hi,

This is an awesome package!

After testing it on 11.2.0.2, assigning the hostname to the ACL is not enough when calling ftp.list, since the call to get_passive opens a new connection based on the response from the PASV command, which returns the IP of the server, and the 2nd open connection failed on ORA-24247.

Conclusion: need to add IP and HOSTNAME to the ACL

Tim... said...

Hi.

Thanks for spotting that. I've added into the ACL definition at the bottom of the article.

Cheers

Tim...

Tim... said...

Vanjohn: I'm happy to help, but you must ask your question in the forum, not in the comments. The forum allows enough room to enter a formatted question and you get email responses. Ask on the forum and I'll help. I've deleted your multiple posts.

Cheers

Tim...

Vanjohn said...

Thanks Tim, I will post it under forum

Tom R. said...

Tim, was looking at put_remote_binary_data function in zip file and looks like file with 32768 bytes won't get last byte processed. This was fixed in Tim Hall ftp package update. l_pos <= l_blob_len

Mitch said...

Hello Tim,
This code saved us lots of time by performing some basic tasks for us!
However, I found a couple of issues:
- in get_passive, setting l_host to data parsed from the reply led to permissions issues; changed this to
l_host := p_conn.remote_host;
...

Mitch said...

(continued)
- within get_local_ascii_data, DBMS_LOB.loadfromfile was producing gibberish for text files. Switched to DBMS_LOB.LOADCLOBFROMFILE and it works.
- in put_remote_ascii_data, needed to set l_amount smaller (tried previous value/4) to avoid an overflow when transferring larger files (over 32k).

Overall, very nice package!
Thank you very much.
Mitch

Tim... said...

Hi.

Sounds like you have some multi-byte characterset issues going on over there. :)

Cheers

Tim...

Rajesh said...

Hi Tim,

Thanks for this useful code. I want to know, why have you added the below code:

@c:\ftp.pks
@c:\ftp.pkb

Will the above run the sql scripts from sql plus?

Basically, what I understand is you would need to created the ftp package in your schema. After that you can create your procs for ftp using that package. Is my understanding correct?

Thanks.

Tim... said...

Hi.

Yes. That is correct.

Cheers

Tim...

David de Vega said...

Hi

When sending file with the put procedure, if local file doesn't exists, application error code -2000 is raised but connection remains open.
I've modified the put procedure in order to handle the internal exception -22288 when local file not found or cannot be read.

David de Vega said...

The fix:

PROCEDURE put (p_conn IN OUT NOCOPY UTL_TCP.connection,
p_from_dir IN VARCHAR2,
p_from_file IN VARCHAR2,
p_to_file IN VARCHAR2) AS
ERR_FILE_NOT_FOUND EXCEPTION;
PRAGMA EXCEPTION_INIT (ERR_FILE_NOT_FOUND,-22288);
BEGIN
...
EXCEPTION
when ERR_FILE_NOT_FOUND then
logout(p_conn,FALSE);
RAISE;
END;

Regards
.

Tim... said...

Hi.

Sorry. I don't agree. What happens if this is only one of several files you want to move with one connection. Now you have closed the connection and broken the rest of the transfers.

You need to manage exceptions like this on a case by case basis, external to the PUT, then decide on the appropriate action.

Cheers

Tim...

DO NOT ask technical questions here, that's what my forum is for!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)