UTL TCP

UTL_TCP

The UTL_TCP package controls TCP access in a stored procedure.

The procedures and functions which are comprised of the UTL_TCP package are listed in the following table below.

Procedures/Functions	Description
CLOSE_ALL_CONNECTIONS	Procedure closes all the handles connected to the session.
CLOSE_CONNECTION	Closes the access handle which is connected.
IS_CONNECT	Checks on the connection status of access handle.
OPEN_CONNECTION	Accesses to remote server by creating a socket.
WRITE_RAW	Transmits RAW type data to the remote server

CLOSE_ALL_CONNECTIONS

The CLOSE_ALL_CONNECTIONS is a procedure which closes all the connection handles currently being accessed.

Syntax

UTL_TCP.CLOSE_ALL_CONNECTIONS;

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
UTL_TCP.CLOSE_ALL_CONNECTIONS();
END;
/

CLOSE_CONNECTION

The CLOSE_CONNECTION is a procedure closing the accessed connection handle.

Syntax

UTL_TCP.CLOSE_CONNECTION(c IN CONNECT_TYPE);

Parameter

Name	In/Output	Data Type	Description
c	IN	CONNECT_TYPE	Connection handle

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
 AS
 V1 CONNECT_TYPE;
 V2 INTEGER;
 BEGIN
 V1 := UTL_TCP.OPEN_CONNECTION('127.0.0.1', 22007, NULL, NULL, 1024);
 V2 := UTL_TCP.WRITE_RAW(V1, TO_RAW('MESSAGE'), RAW_SIZEOF('MESSAGE'));
 UTL_TCP.CLOSE_CONNECTION(V1);
 END;
 /

IS_CONNECT

The IS_CONNECT procedure verifies the connection status of connection handle.

Syntax

UTL_TCP.IS_CONNECT(c IN CONNECT_TYPE);

Parameter

Name	In/Output	Data Type	Description
c	IN	CONNECT_TYPE	The connection handle

Return Value

1 is returned If it is successfully executed and when it fails, it returns -1.

Exception

There is no exception.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
    AS
    V1 CONNECT_TYPE;
    V2 INTEGER;
    BEGIN
    V1 := UTL_TCP.OPEN_CONNECTION('127.0.0.1', 22007, NULL, NULL, 1000);
    V2 := UTL_TCP.IS_CONNECT(V1);
    IF V2 = 0 THEN
        PRINTLN('CONNECTED');
        V2 := UTL_TCP.WRITE_RAW(V1, TO_RAW('MESSAGE'), RAW_SIZEOF('MESSAGE'));
        UTL_TCP.CLOSE_CONNECTION(V1);
    ELSE
        PRINTLN('NOT CONNECTD');
    END IF;
    END;
    /

OPEN_CONNECTION

The OPEN_CONNECTION is a procedure which creates a socket in order to access the remote server.1

Syntax

UTL_TCP.OPEN_CONNECTION(
  remote_host IN VARCHAR(64),
  remote_port IN INTEGER,
  local_host IN VARCHAR(64) DEFAULT NULL,
  local_port IN INTEGER DEFAULT NULL,
  in_buffer_size IN INTEGER DEF DEFAULT NULL,
  out_buffer_size IN INTEGER DEF DEFAULT NULL,
  charset IN VARCHAR(16) DEFAULT NULL,
  newline IN VARCHAR(2) DEFAULT CRLF,
  tx_timeout IN INTEGER DEF DEFAULT NULL,
  wallet_path IN VARCHAR(256) DEFAULT NULL,
  wallet_password IN VARCHAR DEFAULT NULL));

Parameters

Name	In/Output	Data Type	Description
remote_host	IN	VARCHAR(64)	The IP address of remote server
remote_port	IN	INTEGER	The port number of remote server
local_host	IN	VARCHAR(64)	This is parameter only for compatibility and it is neglected.
local_port	IN	INTEGER	This is parameter only for compatibility and it is neglected.
in_buffer_size	IN	INTEGER	This is parameter only for compatibility and it is neglected.
out_buffer_size	IN	INTEGER	This parameter sets the size of internal transmission buffer. The minimum value is 2048 bytes whereas 32767 is the maximum value. Null is set to be the minimum value.
charset	IN	VARCHAR(16)	This is parameter only for compatibility and it is neglected.
newline	IN	VARCHAR(2)	This is parameter only for compatibility and it is neglected.
tx_timeout	IN	INTEGER	This is parameter only for compatibility and it is neglected.
wallet_path	IN	VARCHAR(256)	This is parameter only for compatibility and it is neglected.
wallet_password	IN	VARCHAR	This is parameter only for compatibility and it is neglected.

Return Value

If it is successfully executed, the CONNECT_TYPE connection handle is returned.

Exception

If exception occurs, such as network connection failure, NULL value is returned with CONNECT_TYPE. The connection status of access handle can be confirmed through the UTL_TCP.IS_CONNECT()function.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
 AS
 V1 CONNECT_TYPE;
 V2 INTEGER;
 BEGIN
 V1 := UTL_TCP.OPEN_CONNECTION('127.0.0.1', 22007, NULL, NULL, 1024);
 V2 := UTL_TCP.WRITE_RAW(V1, TO_RAW('MESSAGE'), RAW_SIZEOF('MESSAGE'));
 UTL_TCP.CLOSE_CONNECTION(V1);
 END;
 /

WRITE_RAW

The handle accessed to the network transmits the inserted RAW type data to the remote server through the WRITE_RAW function.

Syntax

UTL_TCP.WRITE_RAW(
  c IN CONNECT_TYPE,
  data IN RAW(65534),
  len IN INTEGER DEFAULT NULL);

Parameters

Name	In/Output	Data Type	Description
c	IN	CONNECT_TYPE	The connection handle
data	IN	RAW(65534)	The transmitting data
len	IN	INTEGER	This parameter is only for compatibility, and the value of this parameter can be neglected.

Return Value

If successfully executes, the length of data which has been transmitted to the network is returned. If it fails, -1 is returned.

Exception

The status of accessed connection handle can be checked by using the UTL_TCP.IS_CONNECT() function if the connection of the connection handle is lost.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
    AS
    V1 CONNECT_TYPE;
    V2 INTEGER;
    BEGIN
    V1 := UTL_TCP.OPEN_CONNECTION('127.0.0.1', 22007, NULL, NULL, 1024);
    V2 := UTL_TCP.WRITE_RAW(V1, TO_RAW('MESSAGE'), RAW_SIZEOF('MESSAGE'));
    UTL_TCP.CLOSE_CONNECTION(V1);
    END;
    /