Spring Repository调用Oracle存在的序列报ORA-02289，错误显示小写序列名

我在Oracle中有一个带模式名的序列，在SqlDeveloper等SQL工具中执行以下语句可以正常获取值：

select CRI_API."ISEQ$$_159767".nextval from dual;
=> 22

Java应用通过CRI_API用户正常连接数据库，该用户已验证可通过jdbcTemplate执行多个select语句访问此模式。

Oracle中的表定义如下：

ERROR_ID    NUMBER  No  "CRI_API"."ISEQ$$_159767".nextval   1   
TICKET_NUMBER   VARCHAR2(100 BYTE)  No      2   
ERROR_MESSAGE   VARCHAR2(1000 BYTE) Yes     3   
CREATED_AT  TIMESTAMP(6)    Yes "CURRENT_TIMESTAMP" 4   

尝试三种方式定义实体类插入数据时，均报“序列不存在”错误，且报错信息中序列名始终显示为小写的iseq$$_159767（实际不存在该小写序列）：

方式1：序列名不含模式名，直接使用大写 #

@Table(name = "MY_ERROR_TABLE", schema = "CRI_API")
public class CsolApiS3Error {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ERROR_ID_SEQ")
    @SequenceGenerator(name = "ERROR_ID_SEQ", sequenceName = "ISEQ$$_159767", allocationSize = 1)
    @Column(name = "ERROR_ID")
    private Long errorId;
}

报错信息：

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet [ORA-02289: sequence does not exist] [select "iseq$$_159767".nextval from dual]; SQL [select "iseq$$_159767".nextval from dual]

方式2：序列名包含模式名 #

@Table(name = "MY_ERROR_TABLE", schema = "CRI_API")
public class CsolApiS3Error {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ERROR_ID_SEQ")
    @SequenceGenerator(name = "ERROR_ID_SEQ", sequenceName = "CRI_API.ISEQ$$_159767", allocationSize = 1)
    @Column(name = "ERROR_ID")
    private Long errorId;
}

报错信息：

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet [ORA-02289: sequence does not exist] [select cri_api."iseq$$_159767".nextval from dual]

方式3：模式名和序列名都用双引号转义 #

@Table(name = "MY_ERROR_TABLE", schema = "CRI_API")
public class CsolApiS3Error {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ERROR_ID_SEQ")
    @SequenceGenerator(name = "ERROR_ID_SEQ", sequenceName = "\"CRI_API\".\"ISEQ$$_159767\"", allocationSize = 1)
    @Column(name = "ERROR_ID")
    private Long errorId;
}

报错信息：

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet [ORA-02289: sequence does not exist] [select "cri_api"."iseq$$_159767".nextval from dual]

请问问题出在哪里？

核心问题是Oracle区分大小写的标识符需要用双引号包裹，而Hibernate默认会把未加双引号的标识符转成小写，你的序列名ISEQ$$_159767带特殊字符且大小写敏感，必须确保Hibernate生成的SQL中序列名保持大写并被双引号正确包裹。

• 正确配置@SequenceGenerator，单独指定schema并保留序列名大小写
  不要直接在sequenceName里拼接模式名，而是用schema属性单独指定，同时给序列名加上双引号，强制Hibernate保留原始大小写：

  @Table(name = "MY_ERROR_TABLE", schema = "CRI_API")
  public class CsolApiS3Error {
  
      @Id
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ERROR_ID_SEQ")
      @SequenceGenerator(
              name = "ERROR_ID_SEQ",
              schema = "CRI_API",
              sequenceName = "\"ISEQ$$_159767\"",
              allocationSize = 1
      )
      @Column(name = "ERROR_ID")
      private Long errorId;
  }
  

• 全局配置Hibernate统一处理大小写敏感标识符（可选）
  如果项目中有多个大小写敏感的数据库对象，可添加全局配置，让Hibernate对所有标识符自动添加双引号，避免转小写：

  # application.properties
  spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
  spring.jpa.properties.hibernate.globally_quoted_identifiers=true
  

  YAML格式：

  # application.yml
  spring:
    jpa:
      hibernate:
        naming:
          physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
      properties:
        hibernate:
          globally_quoted_identifiers: true
  

• 额外验证权限
  确认CRI_API用户对目标序列有SELECT权限：

  GRANT SELECT ON CRI_API."ISEQ$$_159767" TO CRI_API;
  

内容的提问来源于stack exchange，提问作者gene b.