Simple guide to open Socket Connection using Oracle PL SQL Programming

Pavan February 12, 2017 Oracle, How To Guide 7 Comments

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 <USER>;

1	GRANT ALL ON UTL_TCP TO <USER>;

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();
		}
	}
}

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 –

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   => '127.0.0.1',
        REMOTE_PORT   => 9898,
        TX_TIMEOUT    => 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) > 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;
/

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 => '127.0.0.1',

REMOTE_PORT => 9898,

TX_TIMEOUT => 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) > 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.

7 Comments

mohsen
March 3, 2020 | Reply

Hi thank your for such a great post.
just a question.
how can i communicate with parallel port with oracle form 10g ?
for example I want to send a alert to a light when a condition is ture!
thanks again ?
RUMAIZ
November 20, 2019 | Reply

That is a great piece of code that helpmed me learn socket communication. Could you also let me know if there is a PLSQL way to make oracle database work like socket server receiving and processing socket messages.

Pavan
November 27, 2019 | Reply

Hi Rumaiz
Glad you found this useful. Given Oracle capabilities, many things will be possible. But you need to review your use case as using oracle as Socket Server is something most of people will not recommend.

xeilok
September 5, 2019 | Reply

Thank you, one of the few complete examples I could find. nice blog! Keep up the great work!

Shilpa
September 8, 2019 | Reply

Thank You !
Keep Visiting, Keep Learning !

John
February 16, 2018 | Reply

Hi Pavan,
This is such a great blog.

Just a quick question. How do you do with WebSocket?

Pavan
February 16, 2018 | Reply

Hi John,
Thanks for the kind words.
So you want to send data from Oracle to WebSocket? Well, I guess it would be similar. Let me try some sample code and I will update you.

Simple guide to open Socket Connection using Oracle PL SQL Programming

Tools Used

Add a Comment

Openstrap 2.0.3

Aadya 2.0.3

Awakening 2.0.3

Tools Used

Related Posts

Add a Comment

Openstrap 2.0.3

Aadya 2.0.3

Awakening 2.0.3