调用存储过程时的参数数量或类型错误 [英] wrong number or types of arguments while calling Stored Proc

问题描述

我正在从 Spring Data JPA 调用存储过程:

I am calling Stored Proc from Spring Data JPA :

程序是:

create or replace procedure GET_LATEST_GC (arg1 IN VARCHAR2, res1 OUT VARCHAR2, res2 OUT VARCHAR2)
AS
BEGIN
  DELETE FROM GC_T WHERE id = arg1;

  COMMIT;

  BEGIN
    SELECT gc.NAME, s.SIP INTO res1, res2
    FROM GC_T gc, STAFF_T s WHERE s.id = gc.id 
    AND START_TIME = (SELECT MAX(START_TIME) FROM GC_T);

  EXCEPTION
  WHEN others THEN  
    res1 := '';    
  END;
END;

Spring Data JPA 代码

Spring Data JPA code

//仓库

 public interface ActiveDao extends JpaRepository<GcT,Integer>  {
        @Procedure(procedureName="GET_LATEST_GC")
        Object[] plus1(@Param("arg1") String arg1);
    }

//实体

@Data
@Entity
@NamedStoredProcedureQuery(name = "GET_LATEST_GC",
        procedureName = "GET_LATEST_GC", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg1", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res1", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res2", type = String.class)})
@Table(schema = "abc", name = "GC_T")
public class GcT implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    private String id;

    @Column(name = "NAME")
    private String name;


}

//调用

Object[] activeGCInfo =activeDao.plus1(arg);

过程正在接受一个参数，我也传递了 1 个参数.然后我也收到了这个错误:

Procedure is accepting one parameter and I am also passing 1 argument.Then also I am getting this error:

休眠:{调用 GET_LATEST_GC(?,?)}错误 ohejdbc.spi.SqlExceptionHelper - ORA-06550:第 1 行，第 7 列:\nPLS-00306:调用 'GET_LATEST_GC' 时参数数量或类型错误\nORA-06550:第 1 行，第 7 列:\nPL/SQL: 语句被忽略\n

Hibernate: {call GET_LATEST_GC(?,?)} ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'GET_LATEST_GC'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n

请告诉我我哪里做错了.谢谢

Please let me know where I am doing wrong. Thank you

更新 - 也根据建议尝试添加 OUT 参数

//Repo
 public interface ActiveDao extends JpaRepository<GcT,Integer>  {
            @Procedure(procedureName="GET_LATEST_GC")
            Object[] plus1(@Param("arg1") String arg1,@Param("res1") String res1,@Param("res2") String res2);
        }

//Call
Object[] activeGCInfo =activeDao.plus1(arg,"","");

我正在发送三个参数，但它显示我有 4 个错误:

I am sending three args but it is showing me 4 args in error:

Hibernate: {call GET_LATEST_GC(?,?,?,?)} SqlExceptionHelper - ORA-06550:第 1 行，第 7 列:\nPLS-00306:错误的数量或类型调用GET_LATEST_GC"的参数\nORA-06550:第 1 行，列7:\nPL/SQL: 语句被忽略\n

Hibernate: {call GET_LATEST_GC(?,?,?,?)} SqlExceptionHelper - ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'GET_LATEST_GC'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n

推荐答案

尝试将结果从 Object[] 更改为 Map，同时使用 name 引用 proc name 而不是 procedureName.基于错误，我不确定它会修复它.Spring Data JPA 确实期望一个 Map 作为多个输出参数的返回值，因此可以找到每个输出参数作为该 Map 中的键.但我认为主要的错误是procedureName直接映射到db，但是name=会映射到正确的Entity

Try changing the result from Object[] to Map<String, Object, along with referencing the proc name with name instead of procedureName. Based on the error, I'm not sure that it will fix it. Spring Data JPA does expect a Map as the return value for multiple output params, so each output param can be found as the key in that Map. But I think the main error is that procedureName maps directly to the db, but name= will map to the correct Entity

//Repo
 public interface ActiveDao extends JpaRepository<GcT,Integer>  {
            @Procedure(name="GET_LATEST_GC")
            Map<String, Object> plus1(@Param("arg1") String arg1);
        }

//Call
Map<String, Object> activeGCInfo =activeDao.plus1(arg);

这篇关于调用存储过程时的参数数量或类型错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！