Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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;
/
