| General Information |
| 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_CONNECTIONS |
| 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  (New parameters 11.2.0.2) |
| 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,
wallet_path VARCHAR2 DEFAULT NULL,
wallet_password VARCHAR2 DEFAULT NULL)
RETURN connection; |
set serveroutput on
spool c:\temp\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.morganslibrary.org', remote_port => 80, charset => 'US7ASCII');
utl_tcp.secure_connection(c);
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 |
| 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; |
| |
| 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(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;
/ |
| |
| SECURE_CONNECTION (new 11.2.0.2) |
| Secures a TCP/IP connection using SSL/TLS. Requires an Oracle Wallet spec. |
utl_tcp.secure_connection(c IN OUT NOCOPY connection) |
| See OPEN_CONNECTION demo |
| |
| 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('dan@morganslibrary.org', 'dan@morganslibrary.org', 'Test'); |
