To create a listener process in oracle using PLSQL

Invariably for all applications there is a requirement to monitor for an event to occur, and based on the occurance of that event to take a necessary action.

Example

For replicating data into another schema, I will require to continuously check in the queue table if any records are inserted which are pending to be replicated to the replication schema. On occurance of this event I will call a process to replicate these records in the replicate schema in Oracle.

For this I will create a process which will run in an infinite loop and poll for any records to be processed. If no records are to be processed, this process will sleep for x seconds before it will again poll the table for any records to be processed. To run this process in the background I will schedule it through a DBMS_SCHEDULER job.

This is a very simple mechanism to create a basic light weight listener. To start and stop this listener, we set a flag in a table. If we keep this flag in START(ON) status, the polling process will continuously run in the loop and check for records to be processed at a regular interval of 20 seconds. After 20 seconds it will check for the status of the flag, If the value of this flag in the table is STOP (OFF), it will exit from the infinite loop and exit the process and thus the scheduler will no longer be running.

To restart the process, we set the flag to START and run the scheduler again with a start date as per the required time and the process will again run in the background in an infinite loop and listen for any records to be processed.

Below are sample scripts to start the listener process.

Create the table with a flag to start/stop the listener process

CREATE TABLE LSNR_CNTRL 
   (	
     START_STOP VARCHAR2(10 BYTE)
   ) TABLESPACE HR_ARCHIVE ;

We insert a record in this table to START/STOP the listener process.

INSERT INTO LSNR_CNTRL VALUES ('START');

Create the listener process to run in an infinite loop to listen on the table on which records are to be processed

CREATE OR REPLACE PROCEDURE LISTENER_PROCESS AS 

  v_program_name   VARCHAR2(100) := 'LISTENER_PROCESS';
  v_table_name     VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc       VARCHAR2(500);
  v_error_code     NUMBER       := PKG_ARCHIVE.SUCCESS;
  v_start_stop  VARCHAR2(10) := 'STOP';
  v_loop_cntr NUMBER := 0;
BEGIN

  WHILE TRUE
  LOOP --{
      SELECT START_STOP
      INTO v_start_stop
      FROM LSNR_CNTRL;
      IF v_start_stop = 'START'
      THEN
      
        v_loop_cntr := v_loop_cntr + 1;
        PR_PROCESS_RECORDS;
        v_err_desc := 'Calling PR_PROCESS_RECORDS ' || v_loop_cntr;
        PR_ARCHIVE_ERROR_LOG(v_program_name, v_table_name ,v_err_desc, SQLCODE , SQLERRM , SYSDATE , USER, v_error_code);
        COMMIT;
          
      ELSE
        EXIT;
      END IF;  
      DBMS_LOCK.SLEEP (20);    
  
  END LOOP; --}
END LISTENER_PROCESS;
/

Process called from the listener, to check and process the records.

CREATE OR REPLACE PROCEDURE PR_PROCESS_RECORDS AS 

  v_program_name   VARCHAR2(100) := 'PR_PROCESS_RECORDS';
  v_table_name     VARCHAR2(100) := NULL;
  ARCHIVE_EXCEPTION   EXCEPTION;
  v_err_desc       VARCHAR2(500);
  v_error_code     NUMBER       := PKG_ARCHIVE.SUCCESS;
  i_repl_cnt       NUMBER := 0;
 
BEGIN

    SELECT COUNT(1)
    INTO   i_repl_cnt
    FROM CUST.REPL_QUEUE
    WHERE REPL_STAT = 'PNDNG';
    
    IF i_repl_cnt > 0
    THEN
      dbms_output.put_line('process records');
    END IF;
      
END PR_PROCESS_RECORDS;
/ 

Scheduler process to start the listener. The start date should have a date and time stamp later than the system current timestamp for the scheduler to start from that time.

BEGIN
    DBMS_SCHEDULER.create_job (job_name     => 'LISTENERPROCESS',
                               job_type     => 'STORED_PROCEDURE',
                               job_action   => 'LISTENER_PROCESS',
                               start_date   => TO_DATE('20-JAN-25 09:40:05','DD-MON-YY HH24:MI:SS'),
                               enabled      => TRUE);
END;
/

If we want to stop the listener process for any maintenance activity, changes in the record processing logic we run the below update.

UPDATE LSNR_CNTRL
SET START_STOP = 'STOP';

Thought for the day

Do not winnow in every wind

  or follow every path

Stand firm for what you know

  and let your speech be consistent

Be quick to hear

  but deliberate in answering

If you know what to say,

  answer your neighbour

But if not, put your hand 

  over your mouth

Honor and dishonor comes from speaking

 and the tongue of mortals may be their downfall

Do not be called double tongued

  and do not lay traps with your tongue

for shame comes to the thief

  and severe condemnation to the double-tongued.

Sirach 5:9-14