带游标的存储过程调用抛出无效的列名异常 [英] The stored procedure call with cursors throws invalid column name exception

问题描述

我们有一个 Spring Boot 应用程序，我们需要在其中连接到 Oracle DB 并通过存储过程获取数据.我们的每个存储过程都有 REF_CURSOR 作为 OUT 参数.我正在尝试使用 @NamedStoredProcedureQuery 和 @Entity 注释.我们在 pom.xml 和 Oracle12cDialect 中使用 ojdbc14.jar 在 application.properties 文件中.我在执行我的一段代码时遇到异常无效的列名.同样在实体类中，我不得不引入一个带有注释 @Id 的字段，尽管我的存储过程的 REF_CURSOR 没有返回这样的字段.这可能是个问题吗?也不定义 @Id 字段不是一个选项，因为 Hibernate 会抛出异常.任何提示将不胜感激.

We have a Spring Boot application where we need to connect to Oracle DB and fetch data via stored procedures. Each of our stored procedure has REF_CURSOR as OUT parameters. I am trying the same using @NamedStoredProcedureQuery and @Entity annotations. We are using ojdbc14.jar in pom.xml and Oracle12cDialect in application.properties file. I get the exception Invalid Column Name while executing my piece of code. Also in the entity class I had to introduce a field with annotation @Id, although there is no such field being returned by the REF_CURSOR of my stored procedure. Can this be a problem? Also not defining @Id field is not an option since Hibernate throws an exception then. Any hints would be highly appreciated.

实现和问题与问题非常相似调用时无效的列名异常通过 JPA 2.1 使用 ref_cursor 的 Oracle 存储过程

Implementation and Problem is very similar to the question Invalid column name exception when calling an Oracle stored procedure with ref_cursor through JPA 2.1

但是那里没有发布任何答案

But no answer is posted there

推荐答案

如何实现它的简单示例:

The simple example how you can achieve it:

1. 数据库架构.

create table MY_PATIENT
(
   PAT_RECID  number,
   PAT_NAME varchar2(100),
   
   constraint PAT_PK primary key(PAT_RECID)
);

create table MY_ORDER
(
   ORD_RECID  number,
   ORD_CODE varchar2(15),
   ORD_PATID number,
   
   constraint ORD_PK primary key(ORD_RECID),
   constraint ORD_PAT_FK foreign key(ORD_PATID) references MY_PATIENT(PAT_RECID),
   constraint ORD_CODE_UNIQUE unique (ORD_CODE)
);

CREATE OR REPLACE PROCEDURE fetch_patient_orders(
  patientId IN NUMBER, 
  patientOrders OUT SYS_REFCURSOR)
AS
BEGIN
   OPEN patientOrders FOR
   SELECT *
   FROM MY_ORDER
   WHERE ORD_PATID = patientId;
END;

1. 实体定义.

@NamedStoredProcedureQueries(
   @NamedStoredProcedureQuery(
      name = "fetch_patient_orders",
      procedureName = "fetch_patient_orders",
      resultClasses = Order.class, 
      parameters = {
         @StoredProcedureParameter(
            name = "patientId",
            type = Long.class,
            mode = ParameterMode.IN
         ),
         @StoredProcedureParameter(
            name = "patientOrders",
            type = Class.class,
            mode = ParameterMode.REF_CURSOR
         )
      }
   )
)
@Entity
@Table(name = "MY_ORDER")
public class Order
{
   @Id
   @Column(name = "ORD_RECID")
   private Long id;
   
   @Column(name = "ORD_CODE")
   private String code;

   @ManyToOne
   @JoinColumn(name = "ORD_PATID")
   private Patient patient;
}

1. 和用法:

List<Order> orders = session.createNamedStoredProcedureQuery("fetch_patient_orders")
    .setParameter("patientId", 2L)
    .getResultList();

使用 hibernate 5.4.12.Final、ojdbc8.jar、Oracle12cDialect 对其进行了测试.另请参阅 hibernate 文档.

It was tested with hibernate 5.4.12.Final, ojdbc8.jar, Oracle12cDialect. See also the hibernate documentation.

上述方法适用于纯休眠应用程序，但不适用于 Spring Boot 应用程序.

The described above approach will work in a pure hibernate application, but not in spring boot app.

根据spring boot 文档:

According to the spring boot documentation:

连接到生产数据库

生产数据库连接也可以通过使用池DataSource 自动配置.Spring Boot 使用以下算法来选择特定的实现:

Production database connections can also be auto-configured by using a pooling DataSource. Spring Boot uses the following algorithm for choosing a specific implementation:

1. 我们更喜欢 HikariCP 的性能和并发性.如果 HikariCP 可用，我们总是选择它.

1. We prefer HikariCP for its performance and concurrency. If HikariCP is available, we always choose it.

否则，如果 Tomcat 池化 DataSource 可用，我们就使用它.

Otherwise, if the Tomcat pooling DataSource is available, we use it.

如果 HikariCP 和 Tomcat 池化数据源都不可用，并且如果 Commons DBCP2 可用，我们使用它.

If neither HikariCP nor the Tomcat pooling datasource are available and if Commons DBCP2 is available, we use it.

如果您使用 spring-boot-starter-jdbc 或 spring-boot-starter-data-jpa starters"，您将自动获得对 HikariCP 的依赖.

If you use the spring-boot-starter-jdbc or spring-boot-starter-data-jpa "starters", you automatically get a dependency to HikariCP.

您可以完全绕过该算法并通过设置spring.datasource.type 属性来指定要使用的连接池.

You can bypass that algorithm completely and specify the connection pool to use by setting the spring.datasource.type property.

所以，spring boot默认使用HikariCP JDBC连接池.而且看起来REF_CURSOR参数注册有问题:

So, spring boot uses HikariCP JDBC connection pool by default. And it looks like it has a problem with REF_CURSOR parameter registering:

o.h.r.j.i.ResourceRegistryStandardImpl   : Registering statement [HikariProxyCallableStatement@770201936 wrapping oracle.jdbc.driver.OracleCallableStatementWrapper@528a6369]
o.h.type.descriptor.sql.BasicBinder      : binding parameter [patientId] as [BIGINT] - [2]
o.h.s.i.AbstractServiceRegistryImpl      : Initializing service [role=org.hibernate.engine.jdbc.cursor.spi.RefCursorSupport]
o.h.engine.jdbc.spi.SqlExceptionHelper   : Error registering REF_CURSOR parameter [patientOrders] [n/a]

当我在application.properties中使用oracle特定的数据源池时:

When I use the oracle specific data source pool in the application.properties:

# com.zaxxer.hikari.HikariDataSource (default value)
spring.datasource.type=oracle.jdbc.pool.OracleDataSource

一切正常.

这篇关于带游标的存储过程调用抛出无效的列名异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！