Home » SQL & PL/SQL » SQL & PL/SQL » FTP package by Tim Hall (pl/sql)
FTP package by Tim Hall [message #434332] Tue, 08 December 2009 13:23 Go to next message
tootsie
Messages: 4
Registered: December 2009
Location: Denver
Junior Member
I am trying to use the ftp package developed by Tim Hall.

I am trying to remote ftp a fiel from the db server to a remote server.

I created a Directory within the oracle db to identify the directory path for the file on the local server.

When I run the procedure, I get ORA-20000 The system cannot find the path specified.

The path for the file on the remote server is not at the root dir. Oracle is having trouble recognizing the path on the remote server even though I used the ftp package to create the dir on the remote server.

Has anyone a solution to this problem? Any help would be appreaciated.

Thanks,

Esther
Re: FTP package by Tim Hall [message #434336 is a reply to message #434332] Tue, 08 December 2009 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Has anyone a solution to this problem?
We don't know what you have.
We don't know exactly what you did.
We don't know OS name or version for either local or remote system
We don't know Oracle version.

We do know you got custom error &
therefore you either did not do something you should have done
or you did something wrong.

We don't have any details to make a semi-educated guess how to make error not occur.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: FTP package by Tim Hall [message #434337 is a reply to message #434332] Tue, 08 December 2009 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you mail Tim Hall to ask him direclty?

But obviously "The system cannot find the path specified" means that the system cannot find the specified path.

Regards
%Michel
Re: FTP package by Tim Hall [message #434339 is a reply to message #434332] Tue, 08 December 2009 13:41 Go to previous messageGo to next message
tootsie
Messages: 4
Registered: December 2009
Location: Denver
Junior Member
I don't know how to get in touch with Tim Hall so I am using this forun.

I a running on a unix server with Oracle 9i: My program looks like:

create or replace PROCEDURE testftp IS


user_connection UTL_TCP.connection;
dir varchar2(100) := '';
ftp_user varchar2(25) := '';
plandir VARCHAR2(40) := '';

ftp_remote_path varchar2(255):='';




BEGIN


dir := 'JDS_FILES';

ftp_remote_path := '011256556.340/' ||'222M604/';

ftp_user := 'anonymous';

user_connection := ftp.login('qtdenvd06.dev.qintra.com', '21', ftp_user, ' ');

ftp.ascii(p_conn => user_connection);

ftp.put(p_conn => user_connection,
p_from_dir => dir,
p_from_file => 'COMMON_FUNDING_REPORT222M604',
p_to_file => ftp_remote_path || 'COMMON_FUNDING_REPORT222M604' );


ftp.logout(user_connection);

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line ( 'A Critical Error occured while FTPing the files to JDS server for the job number ' ||
' - SQL CODE : ' || sqlcode || 'err msg : ' || sqlerrm );

END;
/
Re: FTP package by Tim Hall [message #434340 is a reply to message #434339] Tue, 08 December 2009 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First remove EXCEPTION WHEN OTHERS, it is useless and just hide from where the error comes.

Regards
Michel

[Updated on: Tue, 08 December 2009 13:56]

Report message to a moderator

Re: FTP package by Tim Hall [message #434341 is a reply to message #434339] Tue, 08 December 2009 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
OK, you are trying to use ftp to push a file from local system to a remote system.

I don't understand why you don't simply use command line ftp.
In any case it appears you are using relative pathname for destination; which can be problematic.

What is OS name & version of remote system?
Re: FTP package by Tim Hall [message #434342 is a reply to message #434332] Tue, 08 December 2009 14:00 Go to previous messageGo to next message
tootsie
Messages: 4
Registered: December 2009
Location: Denver
Junior Member
The problem is oracle is not recognizing the path for the file on the remote server. How do I get oracle to do that? creating a directory for the remote path did not do any good. I still had the problem. creating a directory in oracle for the local path for the local file did help when I got the same error for the from or local file.
Re: FTP package by Tim Hall [message #434344 is a reply to message #434341] Tue, 08 December 2009 14:09 Go to previous messageGo to next message
tootsie
Messages: 4
Registered: December 2009
Location: Denver
Junior Member
because we were directed to get rid of the cron and go with packaging everything into pl/sql and the database
Re: FTP package by Tim Hall [message #434345 is a reply to message #434344] Tue, 08 December 2009 14:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
tootsie wrote on Tue, 08 December 2009 12:09
because we were directed to get rid of the cron and go with packaging everything into pl/sql and the database


Fair enough.
Please consider the following:
First make it work, then make it fancy.

If you can't ftp file from command line, you'll never make it work from inside PL/SQL.

I know I would have more/better visibility of reality via command line ftp; but that would be because I know how to drive ftp at command line.

What is remote OS name & version?
Re: FTP package by Tim Hall [message #434348 is a reply to message #434345] Tue, 08 December 2009 15:00 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The

Quote:

The system cannot find the path specified


Error message is nowhere in Tom Halls package. There is however an

RAISE_APPLICATION_ERROR(-20000, g_reply(g_reply.last));


in there that prints the error returned by the FTP server. so it's not Oracle that doesn't recognize the file on the remote server, the remote server itself is sending you that message.

Now if you get rid of the useless "when others", then you might find out if that line is indeed the line that is raising the error.
Previous Topic: How to filter multiple records
Next Topic: Other Possibilities - SQL Query
Goto Forum:
  


Current Time: Thu Dec 08 02:38:48 CST 2016

Total time taken to generate the page: 0.11731 seconds