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?

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
 

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...

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.

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?

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.

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,

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,

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

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?

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,

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

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.

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;

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;

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,

Tom:

sorry, i meant difference bewtween UTL_TCP and UTL_HTTP packages.

When do you use each?

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.

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,

[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.

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.

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.

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
...

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.

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

Error report:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at line 51

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?

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. :)

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.