Spring JDBC Oracle会话在线程下发生了更改

Question

我想使用Spring中的ORACLE DBMS_ALERT包来接收来自数据库的信号。

在pl/sql中，如果10秒内可用，以下代码将接收一条消息：

DECLARE 
name VARCHAR2(4000 CHAR);
message VARCHAR2(1800 CHAR);
status NUMBER;
BEGIN
DBMS_ALERT.REGISTER('mytopic');

DBMS_ALERT.WAITANY(name, message, status, 10);

DBMS_OUTPUT.put_line('name: '||name);
DBMS_OUTPUT.put_line('status: '||status);
DBMS_OUTPUT.put_line('message: "'||message||'"');
END;

在我的Java代码中，我试图用泛滥的代码接收消息：

private static final String TOPIC = "NAME";
private static final String MESSAGE = "MESSAGE";
private static final String STATUS = "STATUS";
private static final String TIMEOUT = "TIMEOUT";
public static final String CALL_DBMS_ALERT_REGISTER = "{ CALL DBMS_ALERT.REGISTER(?) }";
public static final String CALL_DBMS_ALERT_WAITANY = "{ CALL DBMS_ALERT.WAITANY(?, ?, ?, ?) }";
private final JdbcTemplate jdbcTemplate;

public DbmsAlertRepository(final JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void dbmsAlertRegister(final String topic) {
final List<SqlParameter> parameters = new ArrayList();
parameters.add(new SqlParameter(TOPIC, Types.VARCHAR));
final Map<String, Object> resultData = jdbcTemplate.call(connection -> {
final CallableStatement callableStatement = connection.prepareCall(CALL_DBMS_ALERT_REGISTER);
callableStatement.setString(1, topic);
return callableStatement;
}, parameters);
logger.trace("DBMS_ALERT.REGISTER output: {}", resultData);
}
public Optional<DbmsAlertMessage> dbmsAlertWaitAny(final int timeout) {
final List<SqlParameter> parameters = new ArrayList();
final Optional<DbmsAlertMessage> result;
parameters.add(new SqlOutParameter(TOPIC, Types.VARCHAR));
parameters.add(new SqlOutParameter(MESSAGE, Types.VARCHAR));
parameters.add(new SqlOutParameter(STATUS, Types.NUMERIC));
parameters.add(new SqlParameter(TIMEOUT, Types.NUMERIC));
final Map<String, Object> resultData = jdbcTemplate.call(connection -> {
final CallableStatement callableStatement = connection.prepareCall(CALL_DBMS_ALERT_WAITANY);
callableStatement.registerOutParameter(1, Types.VARCHAR);
callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.registerOutParameter(3, Types.NUMERIC);
callableStatement.setInt(4, timeout);
return callableStatement;
}, parameters);
logger.trace("DBMS_ALERT.WAITANY output: {}", resultData);
final int status = BigDecimal.class.cast(resultData.get(STATUS)).intValue();
if (status == 1) {
result = Optional.empty();
} else {
result = Optional.of(
new DbmsAlertMessage(
String.class.cast(resultData.get(TOPIC)),
String.class.cast(resultData.get(MESSAGE))
)
);
}
return result;
}

我在后台线程中通过一个无限循环调用上面的代码：

public void run() {
dbmsAlertRepository.dbmsAlertRegister("mytopic");
while (!Thread.currentThread().isInterrupted()) {
final Try<Optional<DbmsAlertMessage>> message = Try.of(() -> dbmsAlertRepository.dbmsAlertWaitAny(DBMS_ALERT_TIMEOUT));
message.onSuccess(msg -> {
msg.ifPresent(alertMessage -> {
final DbmsAlertMessageProcessTask task = new DbmsAlertMessageProcessTask(alertMessage);
threadPoolTaskExecutor.execute(task);
});
});
message.onFailure(e -> {
logger.error(e.getMessage(), e);
});
}
}

它是有效的，但有时我会得到以下错误：

2020-12-23 09:52:57.199 DEBUG 20206 --- [s-alert-watch-1] o.s.jdbc.core.JdbcTemplate               : Calling stored procedure
2020-12-23 09:52:57.294  INFO 20206 --- [s-alert-watch-1] h.e.common.service.DbmsAlertService      : Message arrived: Failure(org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException; SQL state [72000]; error code [20000]; ORA-20000: ORU-10024: there are no alerts registered.
ORA-06512: a(z) "SYS.DBMS_ALERT", helyen a(z) 295. sornál
ORA-06512: a(z) helyen a(z) 1. sornál
; nested exception is java.sql.SQLException: ORA-20000: ORU-10024: there are no alerts registered.
ORA-06512: a(z) "SYS.DBMS_ALERT", helyen a(z) 295. sornál
ORA-06512: a(z) helyen a(z) 1. sornál

在我看来，Oracle会话已更改。我该如何避免？

Answer 1

会话不可避免地会因为这样或那样的原因而结束。例如，如果RDBMS崩溃/重新启动，则会话无效。虽然您无法确保会话过期不会发生，但您可以确保代码恢复。正如我们所看到的，这个代码有自己的线程。因此，运行线程的代码的另一部分可以同步，每当这个线程加入父线程时，您都会重新启动它

这意味着您用作该线程的父线程的线程肯定不是主线程，因此，您可能需要稍微更改线程策略。