[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

FTP using PL SQL

Hi,
I tried to transfer through FTP using PL SQL.
by ftp packet.

DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := Ftp.login('xxx.xxx.xxx.xxx', '21', 'C160046', 'C160046');
  Ftp.ASCII(p_conn => l_conn);
  Ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MYCSV',
          p_from_file => 'DEL_ALLOC-166146.CSV',
          p_to_file   => '/JOBS/test_put.txt');
  Ftp.logout(l_conn);
END;

but i am getting this error. can anyone help regarding this.

ORA-29260: network error: TNS:operation timed out
ORA-06512: at "SYS.UTL_TCP", line 28
ORA-06512: at "SYS.UTL_TCP", line 257
ORA-06512: at "HTHTEST.FTP", line 80
ORA-06512: at "HTHTEST.FTP", line 578
ORA-06512: at line 6
0
ashok p
Asked:
ashok p
  • 8
  • 7
3 Solutions
 
sdstuberCommented:
we don't have your ftp package so we don't know what is happening at line 578 or 80.

but based on the error it looks your remote server isn't responding to your put request.
0
 
ashok pAuthor Commented:
Hi, pls find my ftp packet in attachment . and guide me for this error.
ftp.zip
0
 
sdstuberCommented:
the remote server might not support the PASV command (that would be unusual though.)

you can check by looking at the reply value  returned at line 72

  send_command(p_conn, 'PASV');
  l_reply := g_reply(g_reply.last);   ---  look at this value.

If it's supported, then it should be returning a string containing an ip address and port

if it's not, it should have some kind of error code

A more likely possibility is the PASV command is working but your firewall is blocking communication on the secondary connection.  If the reply has an address and port, then it that's likely the problem
1
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
ashok pAuthor Commented:
Hi,
I tried to get the reply value. but while executing again, i got the the below mentoned error.

too many connections.

Where i am getting problem. wheather in my system or in the remote server.
Kindly help. thank you.

ORA-30678: too many open connections
ORA-06512: at "SYS.UTL_TCP", line 28
ORA-06512: at "SYS.UTL_TCP", line 257
ORA-06512: at "HTHTEST.FTP", line 51
ORA-06512: at line 4
0
 
ashok pAuthor Commented:
Hi,
Now i am getting this error with file not found while executing.

ORA-20000: 550 File not found
ORA-06512: at "HTHTEST.FTP", line 129
ORA-06512: at "HTHTEST.FTP", line 109
ORA-06512: at "HTHTEST.FTP", line 204
ORA-06512: at "HTHTEST.FTP", line 409
ORA-06512: at line 6


DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := Ftp.login('<<ip>>',21, username,pswrd);
  Ftp.ASCII(p_conn => l_conn);
  Ftp.get(p_conn      => l_conn,
          p_from_file => 'D:\TESTING\abc-166146_TESTING.CSV',
          p_to_dir    => '<<ftp_folder_name>>',
          p_to_file   => 'abc-166146_TESTING.CSV');
  Ftp.logout(l_conn);
END;

Kindly guide me to resolve.
0
 
sdstuberCommented:
the errors are pretty self-explanatory

you (and possibly others) had too many connections to the remote server, so it rejected new ones.

then, after those connections ended or timed out  you tried to transfer a file that doesn't exist or can't be read by the database.
1
 
ashok pAuthor Commented:
For my last comment. anything i did wrong?
because i getting up this error
0
 
sdstuberCommented:
does 'D:\TESTING\abc-166146_TESTING.CSV' exist on the remote server?  If not, you can't retrieve it

It seems unlikely to me that your remote server would have a drive letter in its ftp path.

try doing your ftp manually, find the file you're looking for and get it.  If it that works, then you should be able to automate it by specifying the same path.

if you can't do it manually, then you can't have the program do it for you.
0
 
ashok pAuthor Commented:
Hi,
I tried to FTP through CMD. its transfered successfully.
But when i tried with the execution. i getting the below error.

ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "HTHTEST.FTP", line 156
ORA-06512: at "HTHTEST.FTP", line 430
ORA-06512: at line 6



DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := Ftp.login('<<ip>>', '21', 'username', 'pswrd');
  Ftp.ASCII(p_conn => l_conn);
  Ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MYCSV', --LOCAL PATH WHERE I M TRYING TO GET THE FILE
          p_from_file => '166271.CSV', --LOCAL FILE NAME
          p_to_file   => '166271.CSV'); --MY REMOTE SERVER DIRECTORY WITH FILE NEM
  Ftp.logout(l_conn);
END;

Kindly help me to resolve this.
0
 
sdstuberCommented:
do you have a directory object called MYCSV?

not a directory in your server's file system, a directory in your database
1
 
ashok pAuthor Commented:
Yes. i am having the directory object with the name MYCSV in DB.
0
 
sdstuberCommented:
is your file in the path that directory points to?

remember it must be on the database server, not on your pc (unless your pc is the database server)
0
 
sdstuberCommented:
I see you've endorsed some of the posts (thank you), but you haven't closed the question.  Did you fix the problem with your file?
1
 
ashok pAuthor Commented:
i tried . but i didn't get. finally i got result by ftp through cmd..
thank you.
0
 
sdstuberCommented:
what went wrong?  Are you trying to ftp a file from your pc?

If you used the dos command line ftp from your pc, then I'll bet that's the problem.

If so, the database server can't do that, because it can't read files from your pc - unless your pc is the database server.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now