| General Information - also see UTL_TCP |
| Source |
{ORACLE_HOME}/rdbms/admin/utltcp.sql |
| First Availability |
8.1.7 |
| Constants |
| Name |
Data Type |
Value |
|
CRLF |
VARCHAR2(2 CHAR) |
unistr(' D A') |
|
| Defined Data Type |
TYPE connection IS RECORD (
remote_host VARCHAR2(255), -- Remote host name
remote_port PLS_INTEGER, -- Remote port number
local_host VARCHAR2(255), -- Local host name
local_port PLS_INTEGER, -- Local port number
charset VARCHAR2(30), -- Character set for on-the-wire comm.
newline VARCHAR2(2), -- Newline character sequence
tx_timeout PLS_INTEGER, -- Transfer time-out value (in seconds)
private_sd PLS_INTEGER -- For internal use only);
|
| Dependencies |
| DBMS_AQELM |
UTL_SMTP |
| DBMS_MAIL |
UTL_TCP_LIB |
|
| Exceptions |
| Exception Name |
Error Code |
Reason |
| buffer_too_small_errcode |
ORA-29258 |
Buffer is too small for I/O |
| end_of_input_errcode |
ORA-29259 |
End of input from the connection |
| network_error_errcode |
ORA-29260 |
Network error |
| bad_argument_errcode |
ORA-29261 |
Bad argument passed in API call |
| partial_multibyte_char_errcode |
ORA-29275 |
A partial multi-byte character found |
| transfer_timeout |
ORA-29276 |
Transfer time-out occurred |
| network_access_denied_errcode |
ORA-24247 |
Network access denied |
|
| Note: |
A maximum of 16 connections, per
session, is allowed. See metalink Note:280838.1: ORA-30678 after executing UTL_TCP.OPEN_CONNECTION for details. |
| |
| AVAILABLE |
|
Determines the number of bytes available for reading from a TCP/IP connection |
utl_tcp.available(
c IN OUT NOCOPY connection,
timeout IN PLS_INTEGER DEFAULT 0)
RETURN PLS_INTEGER; |
| See READ_TEXT demo |
|
| CLOSE_ALL |
| Closes all open TCP/IP connections |
utl_tcp.close_all_connections; |
| exec utl_tcp.close_all_connections; |
|
| CLOSE_CONNECTION |
| Closes a TCP/IP connection |
utl_tcp.close_connection(c IN OUT NOCOPY connection); |
| See OPEN_CONNECTION demo |
|
| FLUSH |
|
Immediately transmits all the output data in the output queue to the connection |
utl_tcp.flush(c IN OUT NOCOPY connection) |
| See OPEN_CONNECTION
demo |
|
| GET_LINE |
|
A convenient form of the read functions, which return the data read instead of the amount of data read |
utl_tcp.get_line(
c IN OUT NOCOPY connection,
remove_crlf IN BOOLEAN DEFAULT FALSE,
peek IN BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2; |
| See OPEN_CONNECTION
demo |
|
| GET_LINE_NCHAR |
| A convenient form of the read functions, which return the data read instead
of the amount of data read |
utl_tcp.get_line_nchar(
c IN OUT NOCOPY connection,
remove_crlf IN BOOLEAN DEFAULT FALSE,
peek IN BOOLEAN DEFAULT FALSE) RETURN NVARCHAR2; |
| TBD |
|
| GET_RAW |
| A convenient form of the read functions, which return the data read
instead of the amount of data read |
utl_tcp.get_raw(
c IN OUT NOCOPY connection,
len IN PLS_INTEGER DEFAULT 1,
peek IN BOOLEAN DEFAULT FALSE) RETURN RAW; |
| TBD |
|
| GET_TEXT |
| A convenient form of the read functions, which return the data read
instead of the amount of data read |
utl_tcp.get_text(
c IN OUT NOCOPY connection,
len IN PLS_INTEGER DEFAULT 1,
peek IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2; |
| TBD |
|
| GET_TEXT_NCHAR |
| A convenient form of the read functions, which return the data read
instead of the amount of data read |
utl_tcp.get_text_nchar(
c IN OUT NOCOPY connection,
len IN PLS_INTEGER DEFAULT 1,
peek IN BOOLEAN DEFAULT FALSE) RETURN NVARCHAR2; |
| TBD |
|
| OPEN_CONNECTION |
Opens a connection to a TCP/IP service |
utl_tcp.open_connection(
remote_host VARCHAR2,
remote_port PLS_INTEGER,
local_host VARCHAR2 DEFAULT NULL,
local_port PLS_INTEGER DEFAULT NULL,
in_buffer_size PLS_INTEGER DEFAULT NULL,
out_buffer_size PLS_INTEGER DEFAULT NULL,
charset VARCHAR2
DEFAULT NULL,
newline VARCHAR2
DEFAULT CRLF,
tx_timeout PLS_INTEGER DEFAULT NULL)
RETURN connection; |
set serveroutput on
spool c: emp\utl_tcp.txt
DECLARE
c utl_tcp.connection;
-- TCP/IP connection to the Web server
retval PLS_INTEGER;
BEGIN
c := utl_tcp.open_connection(remote_host =>
'www.psoug.org', remote_port => 80, charset => 'US7ASCII');
retval := utl_tcp.write_line(c, 'GET / HTTP/1.0');
-- send request
retval := utl_tcp.write_line(c);
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
WHEN OTHERS THEN
NULL;
END;
BEGIN
utl_tcp.flush(c);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
utl_tcp.close_connection(c);
END;
/
spool off
|
|
| READ_LINE |
Reads a text line from a TCP/IP connection |
utl_tcp.read_line(
c IN OUT NOCOPY connection,
data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
remove_crlf IN
BOOLEAN DEFAULT FALSE,
peek IN
BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER; |
| TBD |
|
| READ_RAW |
Reads binary data from a TCP/IP connection |
utl_tcp.read_raw(
c IN OUT NOCOPY connection,
data IN OUT NOCOPY RAW,
len IN
PLS_INTEGER DEFAULT 1,
peek IN
BOOLEAN DEFAULT FALSE) RETURN PLS_INTEGER; |
| TBD |
|
| READ_TEXT |
Reads text data from a TCP/IP connection |
utl_tcp.read_text(
c IN OUT NOCOPY connection,
data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
len IN
PLS_INTEGER DEFAULT 1,
peek IN
BOOLEAN DEFAULT FALSE) RETURN PLS_INTEGER; |
DECLARE
c utl_tcp.connection;
data VARCHAR2(256);
len PLS_INTEGER;
BEGIN
c := utl_tcp.open_connection(...);
LOOP
IF (utl_tcp.available(c) > 0) THEN
len := utl_tcp.read_text(c, data, 256);
ELSE
---do some other things
. . . .
END IF
END LOOP;
END;
/ |
|
| WRITE_LINE |
| Writes a text line to a TCP/IP connection |
utl_tcp.write_line(
c IN OUT NOCOPY connection,
data IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL)
RETURN PLS_INTEGER; |
| See OPEN_CONNECTION demo |
|
| WRITE_RAW |
| Writes binary data to a TCP/IP connection |
utl_tcp.write_raw(
c IN OUT NOCOPY connection,
data IN RAW,
len IN
PLS_INTEGER DEFAULT NULL) RETURN PLS_INTEGER; |
| TBD |
|
| WRITE_TEXT |
| Writes text data to a TCP/IP connection |
utl_tcp.write_text(
c IN OUT NOCOPY connection,
data IN VARCHAR2 CHARACTER SET ANY_CS,
len IN PLS_INTEGER DEFAULT NULL) RETURN PLS_INTEGER; |
| TBD |
| |
| Demos |
Email Demo |
CREATE OR REPLACE PROCEDURE send_mail(sender VARCHAR2, recipient VARCHAR2, message VARCHAR2) IS
mailhost VARCHAR2(30) := 'smtp.drizzle.com';
smtp_error EXCEPTION;
mail_conn utl_tcp.connection;
-- embedded procedure
PROCEDURE smtp_command(command VARCHAR2, ok VARCHAR2 DEFAULT '250') IS
response VARCHAR2(256);
len PLS_INTEGER;
BEGIN
len := utl_tcp.write_line(mail_conn, command);
response := utl_tcp.get_line(mail_conn);
dbms_output.put_line(response);
response := SUBSTR(response,1,3);
IF (response <> ok) THEN
RAISE smtp_error;
END IF;
END smtp_command;
-- end embedded procedure
BEGIN
mail_conn := utl_tcp.open_connection(remote_host => mailhost,
remote_port => 25, charset => 'US7ASCII');
smtp_command('HELO ' || mailhost);
smtp_command('MAIL FROM: ' || sender);
smtp_command('RCPT TO: ' || recipient);
smtp_command('DATA', '354');
smtp_command(message);
smtp_command('QUIT', '221');
utl_tcp.close_connection(mail_conn);
END send_mail;
/
exec send_mail('[email protected]', '[email protected]', 'Test'); |
