Great question—avoiding polling is smart for efficiency, and Oracle has two solid non-polling approaches to make this happen. Let’s walk through both options tailored to your use case of triggering a Java processor when an insert occurs on the EMP_NAME table:

This is Oracle's built-in feature that lets your Java app receive real-time notifications when changes happen to a specific table. It’s lightweight and directly tied to JDBC, making it a straightforward choice.

Step 1: Grant Required Privileges #

First, give your database user the permissions needed to register for notifications:

GRANT CHANGE NOTIFICATION TO your_db_user;
GRANT SELECT ON EMP_NAME TO your_db_user;

Step 2: Java Implementation #

Here’s a complete example to register a listener that triggers on inserts:

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class EmpInsertListener implements DatabaseChangeListener {

    // This method fires when a table change is detected
    @Override
    public void onDatabaseChangeNotification(DatabaseChangeEvent event) {
        // Verify it's an insert on EMP_NAME (filter out updates/deletes if needed)
        if (event.getTableChangeDescription()[0].getTableName().equalsIgnoreCase("EMP_NAME") &&
            event.getTableChangeDescription()[0].getRowChangeDescription()[0].getRowOperation() == 
            oracle.jdbc.dcn.RowChangeDescription.ROW_OPERATION.INSERT) {
            
            System.out.println("New insert detected on EMP_NAME!");
            // Call your custom processor class here
            YourJavaProcessor.handleInsert(event.getTableChangeDescription()[0].getRowChangeDescription()[0]);
        }
    }

    public static void main(String[] args) throws Exception {
        // Configure connection properties
        Properties props = new Properties();
        props.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
        props.setProperty("user", "your_db_user");
        props.setProperty("password", "your_db_password");

        // Establish JDBC connection
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@your_db_host:1521:your_sid", props);
        OracleConnection oracleConn = (OracleConnection) conn;

        // Register for database change notifications
        DatabaseChangeRegistration dcr = oracleConn.registerDatabaseChangeNotification(props);

        try {
            // Attach our listener to the registration
            dcr.addListener(new EmpInsertListener());

            // Create a statement linked to the registration
            OracleStatement stmt = (OracleStatement) conn.createStatement();
            stmt.setDatabaseChangeRegistration(dcr);

            // Execute a dummy query to tell Oracle we care about EMP_NAME changes
            stmt.executeQuery("SELECT * FROM EMP_NAME WHERE 1=0");
            
            System.out.println("DCN listener active. Waiting for inserts...");
            // Keep the app running (use proper lifecycle management in production)
            Thread.sleep(Long.MAX_VALUE);
        } finally {
            // Cleanup resources gracefully
            oracleConn.unregisterDatabaseChangeNotification(dcr);
            conn.close();
        }
    }
}

Key Notes #

• The dummy query SELECT * FROM EMP_NAME WHERE 1=0 tells Oracle to monitor changes to this table without returning any rows.
• You’ll need the Oracle JDBC driver (e.g., ojdbc8.jar) in your classpath.
• DCN works for Oracle 10g and later.

This approach uses Oracle’s messaging system to queue insert events, then your Java app listens to the queue. It’s ideal if you need guaranteed event delivery (even if your app is down temporarily) or want to process events asynchronously.

Step 1: Create Queue Infrastructure in Oracle #

First, set up a queue table and queue to store insert events:

-- Create queue table to hold text messages
BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table => 'EMP_INSERT_QUEUE_TBL',
    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
    compatible => '10.0'
  );
END;
/

-- Create the queue
BEGIN
  DBMS_AQADM.CREATE_QUEUE(
    queue_name => 'EMP_INSERT_QUEUE',
    queue_table => 'EMP_INSERT_QUEUE_TBL'
  );
END;
/

-- Start the queue to enable messaging
BEGIN
  DBMS_AQADM.START_QUEUE(queue_name => 'EMP_INSERT_QUEUE');
END;
/

Step 2: Create Trigger to Enqueue Insert Events #

Write a trigger that adds a message to the queue whenever a row is inserted into EMP_NAME:

CREATE OR REPLACE TRIGGER TRG_EMP_INSERT
AFTER INSERT ON EMP_NAME
FOR EACH ROW
DECLARE
  enqueue_options DBMS_AQ.enqueue_options_t;
  message_properties DBMS_AQ.message_properties_t;
  message_handle RAW(16);
  message SYS.AQ$_JMS_TEXT_MESSAGE;
BEGIN
  // Create a message with details of the inserted row (customize based on your table columns)
  message := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
  message.set_text('Inserted employee ID: ' || :NEW.EMP_ID || ', Name: ' || :NEW.NAME);

  // Enqueue the message
  DBMS_AQ.enqueue(
    queue_name => 'EMP_INSERT_QUEUE',
    enqueue_options => enqueue_options,
    message_properties => message_properties,
    payload => message,
    msgid => message_handle
  );
  COMMIT;
END;
/

Step 3: Java Queue Listener #

Write a Java app to listen to the queue and process messages:

import oracle.jdbc.OracleConnection;
import oracle.jdbc.aq.AQDequeueOptions;
import oracle.jdbc.aq.AQMessage;
import oracle.jdbc.aq.AQQueue;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class EmpAQListener {

    public static void main(String[] args) throws Exception {
        // Configure connection
        Properties props = new Properties();
        props.setProperty("user", "your_db_user");
        props.setProperty("password", "your_db_password");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@your_db_host:1521:your_sid", props);
        OracleConnection oracleConn = (OracleConnection) conn;

        // Get reference to the queue
        AQQueue queue = oracleConn.createAQQueue("EMP_INSERT_QUEUE");

        // Configure dequeue options to wait indefinitely for new messages
        AQDequeueOptions dequeueOpts = new AQDequeueOptions();
        dequeueOpts.setWaitTime(AQDequeueOptions.WAIT_FOREVER);
        dequeueOpts.setNavigationMode(AQDequeueOptions.NAVIGATION_FIRST_MESSAGE);

        System.out.println("AQ listener active. Waiting for insert events...");

        // Continuously listen for messages
        while (true) {
            AQMessage message = queue.dequeue(dequeueOpts);
            // Extract the message payload
            String payload = ((oracle.sql.STRUCT) message.getPayload()).getAttribute("TEXT").toString();
            
            // Process the insert event
            System.out.println("Received insert event: " + payload);
            YourJavaProcessor.handleInsert(payload);
        }
    }
}

Key Notes #

• Grant your user AQ permissions: GRANT EXECUTE ON DBMS_AQ TO your_db_user; and GRANT ALL ON EMP_INSERT_QUEUE_TBL TO your_db_user;
• Messages stay in the queue until processed, so you won’t miss events if your app restarts.

• Filtering: Both methods let you narrow down to only insert events (as shown in examples).
• Performance: DCN is lighter for simple notifications; AQ is better for complex workflows or guaranteed delivery.
• Error Handling: Add retry logic and connection recovery in production to handle outages.

Hope this helps you get up and running! Feel free to ask follow-up questions if you hit any roadblocks.

内容的提问来源于stack exchange，提问作者Subrat