Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle UTL_TCP Package      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: UTL_TCP

Definition:
In Oracle PL/SQL, UTL_TCP is a built in package which communicates with external TCP/IP based servers in internet based applications. Before the introduction of UTL_MAIL, UTL_TCP was the primary utility package to give TCP/IP connections through PL/SQL programs. It can also interact with the TCP/IP client side access functionality in PL/SQL.

The possible exceptions associated with the UTL_TCP package are listed below:

  • BUFFER_TOO_SMALL - Indicates that the buffer size is too small.
  • END_OF_INPUT - Indicates that the connection doesn't have sufficient input to read.
  • NETWORK_ERROR - Raised when connection encounters network congestion.
  • BAS_ARGUMENT - Raised when invalidated input is given to its subprogram.
  • TRANSFER_TIMEOUT - Indicates that no data is available for reading in connection.
  • PARTIAL_MULTIBYTE_ERROR - Raised when complete character data is missing.
The subprograms of the package are listed below, as per Oracle documentation.

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

Example Syntax:

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 communication
newline VARCHAR2(2), -- newline character sequence
tx_timeout PLS_INTEGER, -- transfer time-out value (in seconds)
private_sd PLS_INTEGER, -- for internal use
);


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

Example Syntax:

CRLF varchar2(10);


3. OPEN_CONNECTION Function - Opens a TCP/IP connection to a specified service.

Example Syntax:

UTL_TCP.OPEN_CONNECTION  (remote_host IN VARCHAR2,
remote_port IN PLS_INTEGER,
local_host IN VARCHAR2 DEFAULT NULL,
local_port IN PLS_INTEGER DEFAULT NULL,
in_buffer_size IN PLS_INTEGER DEFAULT NULL,
out_buffer_size IN PLS_INTEGER DEFAULT NULL,
charset IN VARCHAR2 DEFAULT NULL,
newline IN VARCHAR2 DEFAULT CRLF,
tx_timeout IN PLS_INTEGER DEFAULT NULL)
RETURN connection;


4. AVAILABLE Function - Determines the number of bytes available for reading from a TCP/IP connection.

Example Syntax:

UTL_TCP.AVAILABLE (
c IN OUT NOCOPY connection,
timeout IN PLS_INTEGER DEFAULT 0)
RETURN PLS_INTEGER;


5. READ_RAW Function - Receives binary data from a service on an open connection.

Example Syntax:

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;


6. WRITE_RAW Function - Transmits a binary message to a service on an open connection.

Example Syntax:

UTL_TCP.WRITE_RAW (c    IN OUT NOCOPY connection,
data IN RAW,
len IN PLS_INTEGER DEFAULT NULL)
RETURN PLS_INTEGER;


7. READ_TEXT Function - Receives text data from a service on an open connection.

Example Syntax:

UTL_TCP.READ_TEXT (c    IN OUT NOCOPY connection,
data IN OUT NOCOPY VARCHAR2,
len IN PLS_INTEGER DEFAULT 1,
peek IN BOOLEAN DEFAULT FALSE) RETURN PLS_INTEGER;


8. WRITE_TEXT Function - Transmits a text message to a service on an open connection.

Example Syntax:

UTL_TCP.WRITE_TEXT (c    IN OUT NOCOPY connection,
data IN VARCHAR2,
len IN PLS_INTEGER DEFAULT NULL)
RETURN PLS_INTEGER;


9. READ_LINE Function - Receives a text line from a service on an open connection.

Example Syntax:

UTL_TCP.READ_LINE (c           IN OUT NOCOPY connection,
data IN OUT NOCOPY VARCHAR2,
remove_crlf IN BOOLEAN DEFAULT FALSE,
peek IN BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER;


10. WRITE_LINE Function - Transmits a text line to a service on an open connection.

Example Syntax:

UTL_TCP.WRITE_LINE (c    IN OUT NOCOPY connection,
data IN VARCHAR2 DEFAULT NULL)
RETURN PLS_INTEGER;


11. 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.

Example Syntax:

UTL_TCP.GET_RAW (c     IN OUT NOCOPY connection,
len IN PLS_INTEGER DEFAULT 1,
peek IN BOOLEAN DEFAULT FALSE) RETURN RAW;
UTL_TCP.GET_TEXT (c IN OUT NOCOPY connection,
len IN PLS_INTEGER DEFAULT 1,
peek IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2;
UTL_TCP.GET_LINE (c IN OUT NOCOPY connection,
remove_crlf IN BOOLEAN DEFAULT FALSE,
peek IN BOOLEAN DEFAULT FALSE) RETURN
VARCHAR2;


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

Example Syntax:

UTL_TCP.FLUSH (c IN OUT NOCOPY connection);


13. CLOSE_CONNECTION Procedure - Closes an open TCP/IP connection.

Example Syntax:

UTL_TCP.close_CLOSE_CONNECTION (c IN OUT NOCOPY connection);


14. CLOSE_ALL_CONNECTIONS Procedure - Closes all open TCP/IP connections.

Example Syntax:

UTL_TCP.CLOSE_ALL_CONNECTIONS;



Related Links:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 220 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?