Solved

FTP using PL SQL

Posted on 2016-09-08
17
33 Views
Last Modified: 2016-10-03
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
Comment
Question by:ashok p
  • 8
  • 7
17 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 41789457
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
 

Author Comment

by:ashok p
ID: 41789484
Hi, pls find my ftp packet in attachment . and guide me for this error.
ftp.zip
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41789516
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
 

Author Comment

by:ashok p
ID: 41790708
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
 

Author Comment

by:ashok p
ID: 41790809
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points (awarded by participants)
ID: 41791093
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
 

Author Comment

by:ashok p
ID: 41791098
For my last comment. anything i did wrong?
because i getting up this error
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points (awarded by participants)
ID: 41791114
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
 

Author Comment

by:ashok p
ID: 41791187
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 41791210
do you have a directory object called MYCSV?

not a directory in your server's file system, a directory in your database
1
 

Author Comment

by:ashok p
ID: 41791212
Yes. i am having the directory object with the name MYCSV in DB.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points (awarded by participants)
ID: 41791214
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 41797722
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
 

Author Comment

by:ashok p
ID: 41797736
i tried . but i didn't get. finally i got result by ftp through cmd..
thank you.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41797772
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now