Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)

Part Number A89852-02
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

83
UTL_TCP

With the UTL_TCP package and its procedures and functions, PL/SQL applications can communicate with external TCP/IP-based servers using TCP/IP. Because many Internet application protocols are based on TCP/IP, this package is useful to PL/SQL applications that use Internet protocols.

The UTL_TCP package provides TCP/IP client-side access functionality in PL/SQL. The API provided in the package only allows connections to be initiated by the PL/SQL program. It does not allow the PL/SQL program to accept connections initiated from outside of the program.

This chapter discusses the following topics:

Exceptions

The exceptions raised by the TCP/IP package are listed in Table 83-1.

Table 83-1 TCP/IP Exceptions
Exception  Description 

BUFFER_TOO_SMALL 

Buffer is too small for input that requires look-ahead.  

END_OF_INPUT 

Raised when no more data is available to read from the connection. 

NETWORK_ERROR 

Generic network error. 

BAD_ARGUMENT 

Bad argument passed in an API call (for example, a negative buffer size).  

TRANSFER_TIMEOUT 

No data is read and a read time-out occurred.  

PARTIAL_MULTIBYTE_CHAR 

No complete character is read and a partial multi-byte character is found at the end of the input.  

Example

The following code example illustrates how the TCP/IP package can be used to retrieve a Web page over HTTP. It connects to a Web server listening at port 80 (standard port for HTTP) and requests the root document.

DECLARE
  c  utl_tcp.connection;  -- TCP/IP connection to the Web server
  ret_val pls_integer; 
BEGIN
  c := utl_tcp.open_connection(remote_host => `www.acme.com',
                               remote_port =>  80,
                               charset     => 'US7ASCII');  -- open connection
  ret_val := utl_tcp.write_line(c, `GET / HTTP/1.0');    -- send HTTP request
  ret_val := 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
  END;
  utl_tcp.close_connection(c);
END;

The following code example illustrates how the TCP/IP package might be used by an application to send email. The application connects to an SMTP server at port 25 and sends a simple text message.

PROCEDURE send_mail (sender    IN VARCHAR2, 
                     recipient IN VARCHAR2, 
                   message   IN VARCHAR2)
IS mailhost VARCHAR2(30) := 'mailhost.mydomain.com'; smtp_error EXCEPTION; 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); IF (response <> ok) THEN RAISE smtp_error; END IF; 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); EXCEPTION WHEN OTHERS THEN -- Handle the error END;

Summary of UTL_TCP Subprograms

Table 83-2 UTL_TCP Subprograms
Subprogram  Description 

"connection" 

A PL/SQL record type used to represent a TCP/IP connection. 

"CRLF" 

The character sequence carriage-return line-feed. It is the newline sequence commonly used many communication standards. 

"open_connection Function" 

Opens a TCP/IP connection to a specified service. 

"available Function" 

Determines the number of bytes available for reading from a TCP/IP connection. 

"read_raw Function" 

Receives binary data from a service on an open connection. 

"write_raw Function" 

Transmits a binary message to a service on an open connection. 

"read_text Function" 

Receives text data from a service on an open connection. 

"write_text Function" 

Transmits a text message to a service on an open connection. 

"read_line Function" 

Receives a text line from a service on an open connection.  

"write_line Function" 

Transmits a text line to a service on an open connection.  

"get_raw(), get_text(), get_line() Functions" 

Convenient forms of the read functions, which return the data read instead of the amount of data read. 

"flush Procedure" 

Transmits all data in the output buffer, if a buffer is used, to the server immediately. 

"close_connection Procedure" 

Closes an open TCP/IP connection. 

"close_all_connections Procedure" 

Closes all open TCP/IP connections. 


Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback