Simple guide to open Socket Connection using Oracle PL SQL Programming

A socket is used to communicate between two programs. Socket forms one endpoint of a two-way communication link between the programs. 

In this article we will see an example of  how to open a socket using PL SQL programming . We will also see how to read and write from the socket.

Oracle is more than database. You can write a complex business logic that can do core work for your organization using Oracle PL SQL Programming

Tools Used

• Oracle Express Edition
• Java 1.8
• Eclipse IDE

For this example we will use Oracle UTL_TCP package. UTL_TCP package will help to communicate with external servers using TCP/IP.

In order to use UTL_TCP, first we need to grant access to user before the PL SQL program is executed. For that below is the command you need to execute as system or sysdba user.

GRANT ALL ON UTL_TCP TO xUSERx;

Here we have given all access to UTL_TCP. You can choose to give access to connect to only specific server and port.

Now lets see to our java program that will be our socket server. This server will listen for localhost or 127.0.0.1 on port 9898. The server will take input that is put on port in lower case. It will respond back with same input in upper case.

package com.opencodez.server;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.net.ServerSocket;
import java.net.Socket;

/**
 * @author pavan.solapure
 *
 */
public class SocketServer {
	
	/**
	 * Runs the server.
	 */
	public static void main(String[] args) throws IOException {
		ServerSocket listener = new ServerSocket(9898);
		try {
			while (true) {
				Socket socket = listener.accept();
				BufferedReader in = new BufferedReader(new InputStreamReader(socket.getInputStream()));
				try {
					PrintWriter out = new PrintWriter(socket.getOutputStream(), true);

					String inputLine = in.readLine();
					if (null != inputLine) {
						out.println(inputLine.toUpperCase());
					}
					out.flush();
					out.close();
				} finally {
					socket.close();
				}
			}
		} finally {
			listener.close();
		}
	}
}

Now the mail Oracle PL SQL Programming unit x

SET SERVEROUTPUT ON;

DECLARE
    CONN         UTL_TCP.CONNECTION;
    RETVAL       BINARY_INTEGER;
    L_RESPONSE   VARCHAR2(1000) := '';
    L_TEXT  VARCHAR2(1000);    
BEGIN
    
    --OPEN THE CONNECTION
    CONN := UTL_TCP.OPEN_CONNECTION(
        REMOTE_HOST   =x '127.0.0.1',
        REMOTE_PORT   =x 9898,
        TX_TIMEOUT    =x 10
    );

    L_TEXT := 'Hello World!';
    --WRITE TO SOCKET
    RETVAL := UTL_TCP.WRITE_LINE(CONN,L_TEXT);
    UTL_TCP.FLUSH(CONN);
    
    -- CHECK AND READ RESPONSE FROM SOCKET
    BEGIN
        WHILE UTL_TCP.AVAILABLE(CONN,10) x 0 LOOP
            L_RESPONSE := L_RESPONSE ||  UTL_TCP.GET_LINE(CONN,TRUE);
        END LOOP;
    EXCEPTION
        WHEN UTL_TCP.END_OF_INPUT THEN
            NULL;
    END;

    DBMS_OUTPUT.PUT_LINE('Response from Socket Server : ' || L_RESPONSE);
    UTL_TCP.CLOSE_CONNECTION(CONN);
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20101,SQLERRM);
        UTL_TCP.CLOSE_CONNECTION(CONN);
END;
/

Each block in above program is simple and clear. To execute this first run our java program. Once started it will keep on listening on the port 9898. After that copy and paste above code in sql developer and execute. You will see below output.

Hope above example helps you. Please get back to us if you have any question or need help in running above samples.