CODEWITHSUNDEEP
javadatabaseoracle-database
ggkmath
ggkmath

Reputation: 4246

Oracle database: DBMS_ALERT usage with java application

I have a java application server communicating with an oracle 11.2 database server using JDBC. A client's request is send to the application server, which runs a java method that calls a first stored procedure in the database.

I want the java method, after calling the stored procedure, to sit and wait for a signal from the database (the signal indicates that some result is ready for retrieving from the database).

To avoid polling the database, can DBMS_ALERT be used to inform the java method (or methods, since multiple instances may be running, one instance per unique user) that the result is ready to retrieve, in which case the java method can proceed executing the rest of its code?

Can someone provide a simple example of using DBMS_ALERT in this scenario? That is, how to configure it in the database such that an alert is sent when a predetermined field in a table changes (e.g. to a predetermined value, e.g. 'done'), and how to configure the java method such that it can receive an alert message (including how it processes this message to examine it's contents, e.g. if (received_message == 'done') etc )?

Upvotes: 3

Views: 4077

Answers (1)

ggkmath
ggkmath

Reputation: 4246

This is a good example what I was looking for:

https://web.archive.org/web/20111203004656/http://www.oracleutilities.com/Packages/dbms_alert.html

It shows how a java application can work with an Oracle database using DBMS_ALERT.

Some other useful links for reference:

http://vapvarun.com/study/oracle/prentice.programming.oracle.triggers.and.stored.procedures.3rd.edition.ebook-lib/0130850330_ch11lev1sec21.html

http://www.morganslibrary.org/reference/pkgs/dbms_alert.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1101233460502

Upvotes: 3

Related Questions