TCPConnection does not exist!
Michael Madvig, November 29, 2001 - 11:37 am UTC
please see below for details on error message - tried from several systems. same result.
1 DECLARE
2 c utl_tcp.connection; -- TCP/IP connection to the
3 n number;
4 BEGIN
5 c := utl_tcp.open_connection('www.madvig.dk',80); -- open connection
6 n := utl_tcp.write_line(c, 'GET / HTTP/1.0');
7 n := utl_tcp.write_line(c);
8 BEGIN
9 LOOP
10 dbms_output.put_line(utl_tcp.get_line(c, TRUE)); -- read result
11 END LOOP;
12 EXCEPTION
13 WHEN utl_tcp.end_of_input THEN
14 NULL; -- end of input
15 END;
16 utl_tcp.close_connection(c);
17* END;
18 /
DECLARE
*
ERROR at line 1:
ORA-29540: class oracle/plsql/net/TCPConnection does not exist
ORA-06512: at "SYS.UTL_TCP", line 559
ORA-06512: at "SYS.UTL_TCP", line 552
ORA-06512: at line 5
Any ideas?
November 29, 2001 - 12:53 pm UTC
and a search for:
class oracle/plsql/net/TCPConnection does not exist
on this site will reveal the solution. Just plug it into the search.
Not working
ashraf, December 02, 2001 - 7:20 am UTC
i try like
SQL> DECLARE
2 c utl_tcp.connection; -- TCP/IP connection to the Web serve
3 n number;
4 BEGIN
5 c := utl_tcp.open_connection('aria.us.oracle.com',
6 80); -- open connection
7 n := utl_tcp.write_line(c, 'GET / HTTP/1.0');
8 n := utl_tcp.write_line(c);
9 BEGIN
10 LOOP
11 dbms_output.put_line(
12 utl_tcp.get_line(c, TRUE)); -- read result
13 END LOOP;
14 EXCEPTION
15 WHEN utl_tcp.end_of_input THEN
16 NULL; -- end of input
17 END;
18 utl_tcp.close_connection(c);
19 END;
20 /
DECLARE
*
ERROR at line 1:
ORA-20002: aria.us.oracle.com
ORA-06512: at "SYS.UTL_TCP", line 202
ORA-06512: at line 5
December 02, 2001 - 9:47 am UTC
Umm, well since aria.us.oracle.com is my machine behind Oracle's firwall -- you cannot connect to it. Try a machine in your network that runs a webserver on port 80 instead...
Getting response from the server
vaibhav, September 09, 2003 - 6:44 am UTC
Dear TOM,
my code writes to the server rightly but getting a response sends the application in a never ending loop. The code i have used is given below :-
DECLARE
c utl_tcp.connection; -- TCP/IP connection to the Web server
v integer ;
d varchar2(1000);
e varchar2(1000);
BEGIN
c := utl_tcp.open_connection('10.0.23.137',6000); -- open connection
v := utl_tcp.write_text(c,'CON^`!XML^`!BankClient#END#',null);
dbms_output.put_line('len -- '||v); -- read result
BEGIN
LOOP
dbms_output.put_line(utl_tcp.get_line(c,TRUE)); -- read result
END LOOP ;
EXCEPTION
WHEN utl_tcp.end_of_input THEN
NULL; -- end of input
END;
utl_tcp.close_connection(c);
END;
When i comment the utl_tcp.get_line part of code, the application runs properly but to get a response from the server where i have written the message is not possible. i even tried removing the loop and end loop assuming that maybe utl_tcp does not throw a utl_tcp.end_of_input exception, but in vain.
Please advise...
September 09, 2003 - 11:46 am UTC
umm, this just means you are never getting a response from your "server" or your server is not closing the connection after it does.
you have a protocol issue here, you are not speaking the right "protocol" to your server -- i've no idea what the proper protocol is (it is your server) but....
use telnet
telnet 10.0.23.137 6000
and do it manually -- see what happens there. if telnet doesn't exit right after you type in that line of text, this process won't either.
utl_tcp response
vaibhav, September 19, 2003 - 12:52 am UTC
Dear All,
It is happening exactly as you stated. the server is not closing the connection after it receives my message.
i had a discussion with the team handling the server side application and they told me that keeping the connection open was the practice they followed.
opening and closing the connections immediately would lead ot a lot of overhead which they want to avoid.
our application basically writes SMS messages to this server which then actually sends it to the mobile phones. the protocol they follow is :-
1. open a connection
2. keep sending messages
3. send a dummy message to keep the connection open, if no messages are to be sent presently.
4. close the connection (if a long pause is reqd)
In this case, how do i use utl_tcp to achieve my goal? I am able to write to the port, the server also sends the response, but my application hangs waiting indefinitely for a response.
Please help...
Regards,
Vaibhav.
September 20, 2003 - 4:57 pm UTC
you just read from the server until you get an "end of response" character -- whatever that may be (probably, a newline).
then you close the connection.
standard protocol stuff -- you have to be able to recognize "end of message". if there is no way to recognize end of message, you are hosed (out of luck). but I betcha, an end of line is "end of message" (or something like that)
ORA-29260: network error: TNS:lost contact
Gabriel, March 17, 2004 - 10:13 am UTC
Hello Tom,
I am using an FTP package implemented by Tim Hall, probably you are fimiliar with it if not:
</code>
http://www.oracle-base.com/Articles/9i/FTPFromPLSQL9i.php <code>
I am getting the ORA-29260: network error: TNS:lost contact error only when trying to connect to certain servers. I am using the following script to test it:
declare
l_conn UTL_TCP.connection;
begin
l_conn := ftp.login('<server_name>','<server_port>','<user_name>','<user_password>');
ftp.binary(p_conn => l_conn);
ftp.put(p_conn => l_conn,
p_from_dir => '<source_directory>',
p_from_file => '<source_file_name>',
p_to_file => '<destination_>'||'test.txt');
ftp.logout(l_conn);
utl_tcp.close_all_connections;
end;
The manual ftp command works thou which is a bit puzzeling.
The sys admin is not very cooperative giving me the typical 'if the os command is working than the error is on the oracle side; you fix it'. But the package was working perfectly fine on other servers! Are you familiar with this package? I are probably familiar with the utl_tcp package but I don't know exactly from which of the two the error is comming from? Did you see this problem before?
I understand that you might not be able to test it and reproduce as it seems to be dependent on the server. Can you at least tell me where to start looking, and debugging?
March 17, 2004 - 11:07 am UTC
you'll have to debug it a bit and see if it always fails on the same line of code (reproducible) or moves around
you have all of the source code -- you can instrument it a bit to see where and why it is failing and if it reproduces, that'll be "a good thing"
database versions and all (and os's) would be "somewhat relevant" when asking as well -- but put in some debug and figure out if it always fails at the same place.
Reproductible
Gabriel, March 17, 2004 - 2:30 pm UTC
Hello Tom,
It always fail as follows
ORA-29260: network error: TNS:lost contact
ORA-06512: at "SYS.UTL_TCP", line 410
ORA-06512: at "SYS.UTL_TCP", line 316
ORA-06512: at "SYS.UTL_TCP", line 380
ORA-06512: at "UTILITY.FTP", line 95
ORA-06512: at "UTILITY.FTP", line 81
ORA-06512: at "UTILITY.FTP", line 25
ORA-06512: at line 6
line 25: send_command(l_conn, 'USER ' || p_user);
line 81: get_reply(l_conn);
line 95: g_reply(g_reply.last) := UTL_TCP.get_line(l_conn, TRUE);
I'm running on HP-UX 11.11 sending to windows nt and a friend of mine had the same problem sending to a SUN machine. Again the proc works fine when sending to another HP-UX 11.11 Database version on the hp_ux 9.2.0.4
Do you think we need the oracle sofware installed on the receiving machine? On the machines that it was working we had that but I don't know about the machines on which it was failing.
March 17, 2004 - 3:30 pm UTC
no, no oracle software on the recieving machines, they are just ftp clients.
any firewalls here?
We have a firewall
Gabriel, March 18, 2004 - 10:34 am UTC
Hello Tom,
Yes, there is a firewall, but the sysadmin said it is open for the needed ports. I tried an manual OS ftp and it worked.
I added:
EXCEPTION
WHEN UTL_TCP.END_OF_INPUT THEN
NULL;
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(substr(SQLERRM, 1, 80));
and the procedures now ends with no error, sign that it is actually UTL_TCP.END_OF_INPUT the error that I'm getting.
But what to do to fixit?
Thank you very much,
March 18, 2004 - 11:01 am UTC
i asked because I see an issue logged that is exactly the same -- a truss of the process shows that in fact their is a sigpipe being sent (eg: the connection is being messed about with) and the rather simple tcp layer you have in plsql is reporting it as an error whereas ftp (a slight more sophisticated application) must be retrying or handling it differently.
Basically the pipe is in fact being closed on the remote end -- this is identical the issue I see logged (the truss verified the remote end in fact did close the connection). it only happened through the firewall.
the only thing I can suggest is to try more "sophisticated" ftp applications (eg: load up a java class) or if repeated tries work - catch the exception and retry.
my bad
gab, March 18, 2004 - 12:06 pm UTC
I think I made a mistake. I forgot to set server output on thats is why I didn't get the error. With that one set I get
ORA-29260: network error: TNS:lost contact tried;
Does this make a difference?
Thank you,
additional info
gab, March 18, 2004 - 12:11 pm UTC
With the serverout set on I also see the foloowing:
220 toroondc898 Microsoft FTP Service (Version 5.0).
before
ORA-29260: network error: TNS:lost contact tried;
together they look like:
220 toroondc898 Microsoft FTP Service (Version 5.0).
ORA-29260: network error: TNS:lost contact tried; continuing2
can it be that utl_tcp can't handle this return message?
Thank you
gab, March 18, 2004 - 1:37 pm UTC
Hello I'm getting the same error when trying to ftp to a sun server although it works towards other sun servers. here is the error:
220 qe70pdb1 FTP server (SunOS 5.6) ready
ORA-29260: network error: TNS:lost contac
PL/SQL procedure successfully completed.
any ideas?
utl_tcp port?
gab, March 22, 2004 - 10:00 am UTC
From what I understand the utl_tcp doesn't use the os ftp to communicate. In this case which port does the destination machine receives the communication on and which port should be opened in a firewall if the communication goes through a firewall?
Thank you very much,
March 22, 2004 - 11:50 am UTC
you'd have to read up on the ftp protocol itself (there is an RFC out there somewhere)
"TNS:lost contact" while doing FTP
Mike, November 03, 2004 - 4:36 pm UTC
Hey, we found a workaround to the "TNS:lost contact" problem while doing FTP!
Through much testing and working with firewall experts, we determined that the packets that are received at the destination firewall were being considered "FTP attacks". This only happened on packets from Oracle, not from microsoft FTP for example on the same server (and we never had a problem from our Oracle8 DB, but only from our Oracle9). Some firewalls have a setting that allows turning off "FTP Bounce Attack Prevention", and some do not. One firewall we had this problem with is a SonicWall Pro 330, which does not normally have this option, but we found a hidden (non-published) config page in the firewall, diag.html, that allows us to uncheck this option, and suddenly our FTP's were working.
I hope this helps someone out there!
-Mike
ORA-29260: network error: TNS:lost contact
Amie Bunchman, November 04, 2004 - 5:35 pm UTC
I too find this to be a 9.2.0.4 problem - it works from 8.1.7. Have created a TAR. I've pinpointed the problem to secondary calls to the UTL_TCP.GET_LINE function. Have also notified Network Manager to see if he can config the firewall to maintain the connection.
utl_tcp.get_line is hanging
Sudarsana, August 24, 2006 - 1:03 pm UTC
I have the following piece of pl/sql code. When I comment out the utl_tcp.get_line it does not hang. But with this command the procedure is hanging. Please advice
declare
l_conn utl_tcp.connection;
l_string varchar2(3000);
begin
l_conn := UTL_TCP.open_connection('dcoraap3', '1521');
if (l_conn.remote_host is null)
then
dbms_output.put_line('No connection');
else
dbms_output.put_line('connection is '||l_conn.remote_host);
l_conn.tx_timeout := 5;
l_string := UTL_TCP.get_line(l_conn);
end if;
end;
August 27, 2006 - 8:04 pm UTC
well, it would look like you are trying to connect to a tns listner....
but, you don't know the sqlnet protocol.
so, you have no idea how to talk to the listener.
and we are waiting for YOU to talk to US first.
whats the goal here?
follow up - utl_tcp.get_line is hanging
Sudarsana, August 29, 2006 - 1:32 pm UTC
Thats correct I found out that the test script I was writing was accesing the wrong port.
My goal is to ftp a file from one location to a given server. So I modified my code as follows :
declare
l_conn utl_tcp.connection;
l_string varchar2(3000);
begin
l_conn := ftp.login('ursula', '21', 'anonymous', 'schoudhury@virsa.com');
--l_conn := UTL_TCP.open_connection('ursula', '21');
if (l_conn.remote_host is null)
then
dbms_output.put_line('No connection');
else
dbms_output.put_line('connection is '||l_conn.remote_host);
l_conn.tx_timeout := 5;
--l_string := UTL_TCP.get_line(l_conn);
end if;
end;
the connection is being set up correctly now.
Now I am trying to use the package for ftp provided at
</code>
http://www.oracle-base.com/articles/9i/FTPFromPLSQL9i.php <code>
The connection is setup right , but calling the ftp.put gives an error :
ORA-20120: ORA-00604: error occurred at recursive SQL level 1
ORA-01460: unimplemented or unreasonable conversion requested
My call is as follows:
declare
l_conn UTL_TCP.connection;
l_dir varchar2(2000);
l_file varchar2(2000);
begin
l_conn := ftp.login('ursula', '21', 'anonymous', 'schoudhury@abc.com');
ftp.binary(p_conn => l_conn);
l_dir := '/home/oracle/viscomn/admin/out/VIS_dcoraap3';
l_file := 'o2750028.out';
ftp.put(p_conn => l_conn,
p_from_dir => l_dir,
p_from_file => l_file,
p_to_file => '\\ursula\sap_temp\Oracle');
ftp.logout(l_conn);
end;
August 29, 2006 - 4:27 pm UTC
and I have no idea what line of code might be raising that.
how's about a full cut and paste example like I use all of the time?
UTl TCP
A reader, March 04, 2007 - 9:10 am UTC
Tom:
1. Does UTL_TCP allow you to open a network connection from the database machine to any server, Does the remote server has to have a web server installed? or is it only when you want to talk http you need web server on the remote server.
2. Can I use this package to sent a text file or xml file from database to the remote server (HTP PUT request)?
3. Does it work like an http client (browser) where you can post data to anotehr CGI program on the remote machine?
4. What is exactly the difference what this package do and UTL_HTTP?
thank you,
March 04, 2007 - 7:02 pm UTC
hi sam. look where you posted this elsewhere, please.
the questions do not really make sense. utl_tcp and HTTP together - compared as being the "same" is just not sensible.
http is a protocol that is spoken over tcp. You can use utl_tcp to create and have a conversation in http.
UTL_TCP
sam, March 04, 2007 - 8:38 pm UTC
Tom:
sorry, i meant difference bewtween UTL_TCP and UTL_HTTP packages.
When do you use each?
March 05, 2007 - 12:29 pm UTC
use utl_tcp when you want to open a socket and write data/read data to and from a server using any protocol you want - it is up to you to write to the server what it expects (using its protocol)
utl_http - a package that implements the http protocol for talking to a webserver.
If you want to talk to a web server (GET and POST data) utl_http would likely be the right choice as the implementation details of the protocol are hidden from you - one less thing to learn.
UTl_TCP
Sam, March 05, 2007 - 5:06 pm UTC
Tom:
Thanks for the clarification.
If my database machine is behind a firewall and my web server machine (9IAS) is outside the firewall. They talk using TNS/Oracle *Net.
Now I add another unix box "Remote server" with no database insalled in a total new location (another state)
Can I use UTL_TCP from my database machine to:
1. save a text, html, or xml file generated from my database to that remote server. I guess I need to use UTL_FILE here too.
2. Run a CGI "perl" program on that Remote machine.
3. Run a non-cgi program on that Remote machine.
4. It seems UTL_HTTP server the same function as an HTTP client "wget' or "CURL" is that correct.
March 05, 2007 - 8:47 pm UTC
you can do whatever you want. Really - utl_tcp is just a socket library.
If you can open a tcp connection from the database to the application server (unlikely if you ask me, seems they would let traffic come FROM the app server to the database - NOT the other way), you can do whatever you like.
utl_tcp
sam, March 05, 2007 - 11:50 pm UTC
Tom:
But of they only let traffic only from the app server into the database and not vice versa, how would the database send the data back to the app server (before it ends going to the user browser)?
Thanks,
March 06, 2007 - 11:02 am UTC
the firewall lets traffic be initiated from one side to the other.
typically - people would let the application server open a sqlnet conversation to the database server. but would not let the database server open a connection to the application server.
once a conversation is started, of course traffic flows bi-directionally on that pipe.
it is the ACT of starting, initiating, the conversation that is restricted.
RE: Sam's last question
Stew Ashton, March 06, 2007 - 4:45 am UTC
[Apologies if Tom follows this up; as I write, he has not.]
Hi Sam,
TCP is a connection-based, bi-directional protocol. This means that once A connects to B, both A and B can send and receive data on the same connection. It's like if you phone me, we both can talk.
The Firewall allows your App Server to connect to Oracle and it allows data to flow both ways within that connection. This is not the same as allowing the Database Server to open a connection.
Suppose you've created this file you speak of: could the other server just ask Oracle for it? Then all it would take would be a BLOB or a BFILE and a normal connection to Oracle.
If you have to connect from the Database Server, don't re-invent the wheel using a TCP socket. Use a standard protocol like FTP or HTTP.
utl_tcp
sam, March 06, 2007 - 3:15 pm UTC
Tom:
That is a good explanation. But when you use
select utl_http('
http:// ') from dual
are not you running this in the database, so you are making a connection from database to that remote web server using HTTP? How does the flow work.
March 06, 2007 - 4:12 pm UTC
your server process would open a tcp/ip connection to the remote server, if possible.
just like if you use utl_tcp - only the complexity of the http protocol is hidden from you (you do in one line of utl_http code that which would take you dozens or hundreds of lines of code using utl_tcp)
utl_tcp
sam, March 06, 2007 - 10:39 pm UTC
Tom:
yes, but the database is behind firewall and it is configured like you said where app server can connect in only. Does not the use of utl_http successfully mean the database connected to your local web server and then to that other remote server fine. So connection was initiated by database out.
March 07, 2007 - 10:14 am UTC
Sam -
it should be obvious. If you want to use utl_http IN THE DATABASE to connect to another machine that IS NOT THE DATABASE then you will need to be able to connect FROM the database machine TO this other machine.
The DATABASE would INITIATE the connection. It could happen in no other way.
'UTL_TCP' must be declared
Junior, May 01, 2008 - 11:40 am UTC
Tom:
I tried the sample code, but got error. Should I install UTL_TCP? I am using Oracle 10g in Sun Solaris.
Thank you in advance.
SQL> DECLARE
2 l_conn UTL_TCP.connection;
3 BEGIN
4 l_conn := ftp.login('host', '21', 'user', 'userpassword');
5 ftp.ascii(p_conn => l_conn);
6 ftp.get(p_conn => l_conn,
7 p_from_file => '/user/test_3.csv',
8 p_to_dir => 'EXT_DATA',
9 p_to_file => 'test_3.csv');
10 ftp.logout(l_conn);
11 utl_tcp.close_all_connections;
12 END;
13 /
l_conn UTL_TCP.connection;
*
ERROR at line 2:
ORA-06550: line 2, column 11:
PLS-00201: identifier 'UTL_TCP' must be declared
ORA-06550: line 2, column 11:
PL/SQL: Item ignored
ORA-06550: line 4, column 3:
PLS-00320: the declaration of the type of this
expression is incomplete or malformed
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
...
May 01, 2008 - 11:52 am UTC
you should work with your DBA to have them grant you access to this package after they review what is going on...
Marco Aurelio Correia, October 10, 2008 - 6:26 am UTC
Tom:
I am using an FTP package implemented by Tim Hall and i getting the error ORA-29260: network error: TNS:lost contact error. I my case when i use CLOSE_ALL_CONNECTIONS as opposed to CLOSE_CONNECTION(L_CONN) , solve my problem.
PROCEDURE sp_desconecta_ftp ( p_conn IN OUT UTL_TCP.connection ) IS
BEGIN
BEGIN
sicc.Ftp_v2.logout(p_conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
UTL_TCP.close_all_connections;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
UTL_TCP.close_all_connections;
RAISE_APPLICATION_ERROR(-20002, 'pc_siccfp0010.fn_desconecta_ftp : '||SQLERRM);
END sp_desconecta_ftp;
Thanks.
Cant PUT a file with FTP Package
Ian Mc, November 04, 2008 - 6:55 am UTC
I am also having problems with the FTP package trying to send (PUT) a file to a
remote server. I pulled apart the FTP package and the problem seems to lie
in the function code in FTP package "get_local_ascii_data".
I have concluded that the problem is not in the specifications of user-id, password,
directories or file names. The reason I reached this conclusion is that I can
successfully "GET" a remote file, but not "PUT" a remote file, and the file
and paths exist ( I actually setup the FTP connection to the same host as the
procedure runs on, for testing! it seems the UTL_FILE write works OK, but the
).
DECLARE
p_conn UTL_TCP.connection;
ftpSERVER VARCHAR2(128) := '168.89.78.245';
ftpPORT NUMBER := 21;
ftpUSERID VARCHAR2(32) := 'spp???';
ftpPASSWORD VARCHAR2(32) := 'spp???';
wrk_dir VARCHAR2(32) := '/home/users';
ftpTargetDir VARCHAR2(32) := '/home/users';
p_dir VARCHAR2(32) := '/home/users';
p_file VARCHAR2(256) := 'home/users/ianm/A2700001607T05S000115';
l_bfile BFILE;
p_data CLOB;
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
p_conn := ftp.login(ftpSERVER, ftpPORT, ftpUSERID, ftpPASSWORD);
dbms_output.put_line('Connected: '||ftpSERVER);
l_conn := sps_golive.FTP.get_passive(p_conn);
dbms_output.put_line('Passive connetction created: '||ftpSERVER);
sps_golive.FTP.send_command(p_conn, 'STOR ' || p_file, TRUE);
dbms_output.put_line('STOR '|| p_file||' Sent: '||ftpSERVER);
dbms_output.put_line('GETTING LOCAL DATA: '||p_dir||', A2700001607T05S000115' );
p_data := sps_golive.FTP.get_local_ascii_data(p_dir, 'A2700001607T05S000115' );
dbms_output.put_line('GET LOCAL ASCII DATA complete '|| p_dir||', A2700001607T05S000115: ');
l_clob_len := DBMS_LOB.getlength(p_data);
dbms_output.put_line('DBMS.LOB getlength completed: ');
WHILE l_pos <= l_clob_len LOOP
DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
-- IF sps_golive.FTP.g_convert_crlf THEN
-- l_buffer := REPLACE(l_buffer, CHR(13), NULL);
-- END IF;
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;
-- The following line allows some people to make multiple calls from one connection.
-- It causes the operation to hang for me, hence it is commented out by default.
-- get_reply(p_conn);
UTL_TCP.close_connection(l_conn);
EXCEPTION
WHEN OTHERS THEN
UTL_TCP.close_connection(l_conn);
RAISE;
END;
Error report:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at line 51
22285. 00000 - "non-existent directory or file for %s operation"
*Cause: Attempted to access a directory that does not exist, or attempted
to access a file in a directory that does not exist.
*Action: Ensure that a system object corresponding to the specified
directory exists in the database dictionary, or
make sure the name is correct.
220 FTP server Version wu-2.6.2
331 Password required for sureba.
230 User ????? logged in.
Connected: ??.??.147.18
227 Entering Passive Mode (??,??,147,18,255,74).
Passive connetction created: ??.??.147.18
150 Opening ASCII mode data connection for /home/users/ianm/A2700001607T05S000115
STOR /users/ianm/A2700001607T05S000115 Sent: 196.7.147.18
GETTING LOCAL DATA: /home/users, A2700001607T05S000115
November 11, 2008 - 11:59 am UTC
Error report:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at line 51
so, if you code a block that does NOTHING other than try to open that file (eg: get rid of ftp altogether) does it work.
do you see the error - it has nothing to do with ftp, it is all about accessing a file.
Getting ORA-29260: network error: TNS:no listener
aks, November 13, 2009 - 1:32 am UTC
Hey Tom,
I need to write a procedure, which will send a mail. The code is as follows.
**********************************************************
CREATE OR REPLACE PROCEDURE SEND_MAIL_1
(
msg_from varchar2 := 'anjan.sahani@bsci.com',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := 'First Mail from Oracle'
)
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('172.18.23.133', 25); -- open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, 'WHEEPPEEE');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(
-20000, 'Unable to send e-mail message from pl/sql because of: '||
sqlerrm);
END;
/
***********************************************************
The procedure is getting executed successfully. But when I am trying to execute it, I am getting error as below.
SQL> exec send_mail_1(msg_to =>'anjan.sahani@bsci.com', msg_text=>'I can send mail from plsql');
BEGIN send_mail_1(msg_to =>'anjan.sahani@bsci.com', msg_text=>'I can send mail from plsql'); END;
*
ERROR at line 1:
ORA-20000: Unable to send e-mail message from pl/sql because of: ORA-29260:
network error: TNS:no listener
ORA-06512: at "DM.SEND_MAIL_1", line 32
ORA-06512: at line 1
Could you please suggest me on the same?
November 15, 2009 - 2:26 pm UTC
utl_tcp.open_connection('172.18.23.133', 25);
there is no one listening on port 25 at that address.
to prove that, use telnet
telnet 172.18.23.133 25
Thanks
aks, November 20, 2009 - 4:20 am UTC
Hi Tom,
Thanks for your feed backs.
:)
I have always been a reader of your site, and in my career, this site has really helped me a lot, getting a lot of queries clarified and getting new updates on my skills.
Thanks Agian. :)
UTL_TCP END_OF_INPUT Exception
Karthik, November 08, 2012 - 5:40 am UTC
Tom,
Although Oracle documentation says that the END_OF_INPUT exception is - "Raised when no more data is available to read from the connection."
I have seen scenarios where this exception is raised when there is actually data to be read from the source.
Also, after reviewing this thread and few other threads, this exception was caught and nothing was done.(NULL;)
Problem Scenario:
We connect to a TCP/IP server using UTL_TCP in PL/SQL, to connect to a different system, execute a query, fetch the results from the TCP/IP Server and load into our system.
Of late we have been getting this UTL_TCP END_OF_INPUT exception. Analyzing the issue we found "java.net.SocketException: Conection is no more open"
in the log file of the TCP/IP server.
We are sure that neither the TCP/IP server nor the Source system where we are fetching the data closed the connection.
DB Version - 10.2.0.2
Any suggestions to handle this exception would be very helpful.
November 08, 2012 - 8:35 am UTC
you'll have to utilize support for something like this.