CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle UTL_TCP
Version 11.1
 
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('DA')

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('damorgan@psoug.org', 'damorgan@psoug.org', 'Test');
 
Related Topics
UTL_SMTP
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [299 users online]    © 2010 psoug.org