ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
This package supports application communications with external TCP/IP-based servers using TCP/IP. This package can be used when PL/SQL code neds to use Internet protocols and/or e-mail.
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.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
CRLF
VARCHAR2(2 CHAR)
unistr('\000D\000A')
Data Types
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
/
DECLARE
c utl_tcp.connection; -- TCP/IP connection to the web server
retval PLS_INTEGER;
BEGIN
c := utl_tcp.open_connection(remote_host => 'www.morganslibrary.org', remote_port => 80, charset => 'US7ASCII');
utl_tcp.secure_connection(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
A function that ping's ports to determine whether they exist
CREATE OR REPLACE FUNCTION ping (pHostName VARCHAR2, pPort NUMBER DEFAULT 1000)
RETURN VARCHAR2 AUTHID DEFINER IS
tcpCnx utl_tcp.connection;
cOk CONSTANT VARCHAR2(2) := 'OK';
cFail CONSTANT VARCHAR2(5) := 'ERROR';
BEGIN
tcpCnx := utl_tcp.open_connection (pHostName, pPort);
utl_tcp.close_connection(tcpCnx);
RETURN cOk;
EXCEPTION
WHEN utl_tcp.network_error THEN
IF (UPPER(SQLERRM) LIKE '%HOST%') THEN
RETURN cFail;
ELSIF (UPPER(SQLERRM) LIKE '%LISTENER%') THEN
RETURN cOk;
ELSE
RAISE;
END IF;
WHEN OTHERS THEN
RAISE;
END ping;
/
SELECT ping('usps997lt.usa.morganslibrary.com', 1521)
FROM dual;
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;
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(remote_host => 'www.morganslibrary.org', remote_port => 80, charset => 'US7ASCII');
LOOP
IF (utl_tcp.available(c) > 0) THEN
len := utl_tcp.read_text(c, data, 256);
ELSE
---do some other things
NULL;
END IF;
END LOOP;
utl_tcp.close_connection(c);
END;
/
CREATE OR REPLACE PROCEDURE send_mail(
sender IN VARCHAR2,
recipient IN VARCHAR2,
message IN VARCHAR2)
AUTHID DEFINER IS
mailhost VARCHAR2(30) := 'smtp.drizzle.com';
smtp_error EXCEPTION;
mail_conn utl_tcp.connection;
-- embedded procedure
PROCEDURE smtp_command(command IN VARCHAR2, ok IN 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;
/
