ORDS服务调用获取SEQUENCE的NEXTVAL时抛出ORA-02287错误

问题描述 #

通过ORDS服务的REST接口执行以下查询时：

select myseq.nextval from dual;

抛出错误：

The request could not be processed because an error occurred whilst attempting to evaluate a SQL statement associated with this resource. Verify that the URI and payload are correctly specified for the requested operation. If the issue persists then please contact the author of the resource. SQL Error Code: 2287, Error Message: ORA-02287: sequence number not allowed here

该查询在SQL Developer中可正常执行，但另外两个只读查询（select sysdate from dual、select last_number LAST FROM user_sequences WHERE sequence_name = 'myseq'）通过ORDS的GET请求能正常返回结果。

原因分析 #

ORDS对GET请求有只读限制，要求GET操作必须是幂等的（不会改变服务器端状态）。而调用序列的NEXTVAL会递增序列的当前值，属于带有状态变更的操作，不符合GET请求的语义，因此ORDS会拦截这类语句并抛出ORA-02287错误。

解决方案 #

1. 使用POST请求替代GET（推荐） #

将获取NEXTVAL的请求改为POST方法，POST专门用于处理会改变服务器状态的操作，ORDS允许在POST请求中执行带有副作用的SQL语句。

2. 调整ORDS资源配置（不推荐，违反REST规范） #

如果必须使用GET请求，可以在定义ORDS资源时添加allowModificationsInQueries参数并设置为true，允许在查询中执行修改操作：

BEGIN
  ORDS.define_service(
    p_module_name    => 'my_module',
    p_base_path      => '/sequences/',
    p_pattern        => 'nextval',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN :result := myseq.nextval; END;',
    p_items_per_page => 0,
    p_allow_modifications_in_queries => true
  );
  COMMIT;
END;
/

注意：这种方式会破坏GET请求的幂等性，不建议在生产环境使用。

3. 通过PL/SQL过程暴露（最佳实践） #

创建一个返回序列NEXTVAL的PL/SQL过程，然后通过ORDS暴露这个过程，既符合REST规范，又能安全获取序列值：

CREATE OR REPLACE PROCEDURE get_myseq_nextval (p_nextval OUT NUMBER)
IS
BEGIN
  p_nextval := myseq.nextval;
END;
/

BEGIN
  ORDS.define_service(
    p_module_name    => 'my_module',
    p_base_path      => '/sequences/',
    p_pattern        => 'nextval',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_myseq_nextval(:p_nextval); END;',
    p_items_per_page => 0
  );
  ORDS.define_parameter(
    p_module_name        => 'my_module',
    p_pattern            => 'nextval',
    p_method             => 'POST',
    p_name               => 'p_nextval',
    p_bind_variable_name => 'p_nextval',
    p_source_type        => ORDS.source_type_out,
    p_param_type         => ORDS.param_type_query,
    p_data_type          => ORDS.data_type_number
  );
  COMMIT;
END;
/

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