The
utl_tcp package provides PL/SQL programs the
capability to communicate via TCP/IP (Transmission Control
Protocol/Internet Protocol). This package requires that the Oracle
XMLDB component was chosen during initial database creation or
subsequent modification utilizing DBCA. Otherwise, the following
series of error messages will come up:
SQL> @send_email
DECLARE
*
ERROR at line 1:
ORA-24248: XML DB extensible security not
installed
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at line 7
Just like
utl_smtp, this is one occasion where an example is worth
more than the reference material. The
email_myself_tcp_demo.sql PL/SQL code is a snippet of
PL/SQL to email a message:
email_myself_tcp_demo.sql script
DECLARE
sender
VARCHAR2(30) := 'bert.scalzo@yahoo.com';
recipient
VARCHAR2(30) := 'bert.scalzo@yahoo.com';
message
VARCHAR2(30) := 'This is a test message';
mailhost
VARCHAR2(30) := '127.0.0.1';
mail_conn
utl_tcp.connection;
PROCEDURE smtp_command (command IN
VARCHAR2, ok IN VARCHAR2 DEFAULT '250')
IS
response varchar2(3);
len
pls_integer;
BEGIN
len := utl_tcp.write_line(mail_conn,
command);
response := substr(utl_tcp.get_line(mail_conn),
1, 3);
END;
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;
/
Next, examine the procedures and functions provided by the
utl_tcp package. Key to the process is creating a TCP
connection and then using that on subsequent
utl_tcp calls. One also needs to work with the following
data type and constants:
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
);
buffer_too_small
EXCEPTION; --
Buffer is too small for I/O
end_of_input
EXCEPTION; --
End of input from connection
network_error
EXCEPTION; --
Network error
bad_argument
EXCEPTION; --
Bad argument passed in API call
partial_multibyte_char
EXCEPTION; --
A partial multi-byte char found
transfer_timeout
EXCEPTION; --
Transfer time-out occurred
network_access_denied
EXCEPTION; --
Network access denied
buffer_too_small_errcode
CONSTANT PLS_INTEGER:= -29258;
end_of_input_errcode
CONSTANT PLS_INTEGER:= -29259;
network_error_errcode
CONSTANT PLS_INTEGER:= -29260;
bad_argument_errcode
CONSTANT PLS_INTEGER:= -29261;
partial_multibyte_char_errcode CONSTANT
PLS_INTEGER:= -29275;
transfer_timeout_errcode
CONSTANT PLS_INTEGER:= -29276;
network_access_denied_errcode
CONSTANT PLS_INTEGER:= -24247;
Close_data
is a function that determines the number of bytes available for
consumption from a TCP/IP connection and returns a
pls_integer.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
C
|
UTL_TCP.CONNECTION
|
IN | OUT
|
|
|
TIMEOUT
|
PLS_INTEGER
|
IN
|
0
|
Table 6.157:
Close_data Parameters
Close_all_connections
is a procedure that simply closes all the open TCP/IP connections. It
takes no parameters.
Close_connection
is a procedure that closes the specified open TCP/IP connection.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
C
|
UTL_TCP.CONNECTION
|
IN | OUT
|
|
Table 6.158:
Close_connection Parameter
Flush
is a procedure that immediately transmits any and all data in the
output buffer.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
C
|
UTL_TCP.CONNECTION
|
IN | OUT
|
|
Table 6.159:
Flush Parameters
Get_line
is a function that reads a line of data and returns a VARCHAR2. The
peek parameter allows one to look ahead in the buffer
without actually consuming the message.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
C
|
UTL_TCP.CONNECTION
|
IN | OUT
|
|
|
REMOVE_CRLF
|
BOOLEAN
|
IN
|
FALSE
|
|
PEEK
|
BOOLEAN
|
IN
|
FALSE
|
Table 6.160:
Get_line Parameters
Get_line_nchar
is a function that reads a line of data, and returns a NVARCHAR2. The
peek parameter allows one to look ahead in the buffer
without actually consuming the message.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
C
|
UTL_TCP.CONNECTION
|
IN | OUT
|
|
|
REMOVE_CRLF
|
BOOLEAN
|
IN
|
FALSE
|
|
PEEK
|
BOOLEAN
|
IN
|
FALSE
|
Table 6.161:
Get_line_nchar Parameters
