CODEWITHSUNDEEP
javaoracle-databaseservletsplsqljboss
sebster
sebster

Reputation: 1320

DBMS_ALERT usage in Java Servlet

I'm developing a client/server application using Android as a client and JBoss AS 7.x as the server. I am doing all of this via a servlet and the DataSource is configured to make use of connection pooling.

One of the requirements is to immediately invalidate the client session when certain data from the DataSource(Oracle) is deleted. For this I was checking the DBMS_ALERT.

As far as I can understand DBMS_ALERT needs a polling mechanism that will "hang" the application until a notification occurs. So my servlet will halt all operations and wait for the notification. Please correct me if I'm wrong as I have little to no experience with PL/SQL.

I have already checked the resources provided in this question but I didn't manage to understand what's the right track to take.


The code for the DBMS_ALERT DataSource side is:

CREATE OR REPLACE TRIGGER apps0000_datuser_biur
    BEFORE  INSERT OR UPDATE OR DELETE on apps0000_t
    REFERENCING NEW AS NEW OLD AS OLD
    for EACH ROW
begin
    if (deleting) then
        DBMS_ALERT.signal('appdelete',:old.APPS_PACKAGE);
    end if;
end;


The code for the DBMS_ALERT servlet side is(please correct me if I got something wrong here):

try {
    String strDSName1 = "java:/OracleDSJNDI";
    ctx = new InitialContext();
    ds1 = (javax.sql.DataSource) ctx.lookup(strDSName1);
} catch (Exception e) {
    System.out.println("ERROR getting 1'st DS : " + e);
}

 conn = ds1.getConnection();

 String sql = null;
 String message = null;
 String status = null;

 sql = "{call dbms_alert.register('appdelete')}";
 CallableStatement cs1 = conn.prepareCall(sql);
 cs1.execute();
 sql = "{call dbms_alert.waitone('appdelete', ?, ?, ?)}";

 CallableStatement cs2 = conn.prepareCall(sql);
 cs2.registerOutParameter(1, Types.VARCHAR);
 cs2.registerOutParameter(2, Types.VARCHAR);
 cs2.registerOutParameter(3, Types.VARCHAR);

 int x = 0;

 while (x == 0) {
     cs2.execute();
     String Result = cs2.getString(1);
     System.out.print(Result + "\n");
 }


My question is: How do I implement this in my servlet without causing it to hang when it reaches the DBMS_ALERT part(if it's a viable solution)?

Thanks

Upvotes: 1

Views: 1018

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12169

If you look at the message function in the API documentation, it shows:

DBMS_ALERT.WAITONE (
   name      IN   VARCHAR2,
   message   OUT  VARCHAR2,
   status    OUT  INTEGER,
   timeout   IN   NUMBER DEFAULT MAXWAIT);

The "timeout" parameters defaults to forever. The comments state:

timeout

Maximum time to wait for an alert.

If the named alert does not occurs before timeout seconds, this returns a status of 1.

So, you could set this timeout to some # of seconds, and then poll in a loop. HOWEVER, I would contend that it is a poor approach, as you are just eating network and application/server resources using polling. If possible, I would take a look at using Oracle Advanced Queuing which allows for total async processing (See DBMS_AQ package).

Upvotes: 2

Related Questions