java - 使用对象列表调用 oracle 存储过程

Question

我有一个带有过程的 oracle 包

 TYPE rpa_type IS RECORD (
        OPNAME RPA_SUMMARY_AUDIT.OPNAME%TYPE,
        PROCESSDATE RPA_SUMMARY_AUDIT.PROCESSDATE%TYPE,
        SESSIONID RPA_SUMMARY_AUDIT.SESSIONID%TYPE,
        TOTALCOUNT RPA_SUMMARY_AUDIT.TOTALCOUNT%TYPE,
        SUCCESSCOUNT RPA_SUMMARY_AUDIT.SUCCESSCOUNT%TYPE,
        FAILEDCOUNT RPA_SUMMARY_AUDIT.FAILEDCOUNT%TYPE
        );
   TYPE rpa_tab IS TABLE OF rpa_type INDEX BY BINARY_INTEGER;
    PROCEDURE save_rpa_summary(
            p_parm IN rpa_tab, 
            p_affiliate_code IN VARCHAR);

和描述

PROCEDURE save_rpa_summary(
        p_parm IN rpa_tab,
        p_affiliate_code IN VARCHAR 
)  IS
    p_response_code VARCHAR(500);
    ver_count NUMBER;
  BEGIN
      SELECT 'toto'  INTO p_response_code FROM dual;
     FOR i IN p_parm.first .. p_parm.last
      LOOP
      -- 
      INSERT INTO RPA_SUMMARY_AUDIT
(ID, OPNAME, PROCESSDATE, SESSIONID, TOTALCOUNT, SUCCESSCOUNT, FAILEDCOUNT, AFFILIATE, CRATEDDATE)
VALUES(RPA_SUMMARY_AUDIT_SEQ.nextval, p_parm(i).OPNAME, p_parm(i).PROCESSDATE, p_parm(i).SESSIONID, p_parm(i).TOTALCOUNT, p_parm(i).SUCCESSCOUNT, p_parm(i).FAILEDCOUNT, p_affiliate_code, CURRENT_DATE);

       ver_count:=ver_count+1;

      END LOOP;

     p_response_code:=ver_count;
    dbms_output.put_line(p_response_code);
     EXCEPTION 
   WHEN PROGRAM_ERROR THEN 
      dbms_output.put_line('ID must be greater than zero!'); 
     p_response_code:=-1;
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
     p_response_code:=-1;
   WHEN others THEN 
      dbms_output.put_line('Error!');  
     p_response_code:=-1;
  END save_rpa_summary;

我可以使用测试它来调用它

DECLARE
      v_t VARCHAR(255);
     t_rpa test_pkg.rpa_tab;
    BEGIN
    t_rpa(1).OPNAME := 'process 1';
    t_rpa(1).PROCESSDATE := 'Dec-12-2019';
    t_rpa(1).SESSIONID := null;
    t_rpa(1).TOTALCOUNT :=3000;
    t_rpa(1).SUCCESSCOUNT :=2500;
    t_rpa(1).SUCCESSCOUNT :=500;
    t_rpa(2).OPNAME := 'process 2';
    t_rpa(2).PROCESSDATE := 'Dec-12-2019';
    t_rpa(2).SESSIONID := 'SESSION 1';
    t_rpa(2).TOTALCOUNT :=2500;
    t_rpa(2).SUCCESSCOUNT :=1350;
    t_rpa(2).SUCCESSCOUNT :=null;
   TEST_PKG.SAVE_RPA_SUMMARY(t_rpa,'ENG');
    END;

我正在尝试使用 spring data jpa 来调用此过程或 SimpleJdbcCall

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Entity;
import java.io.Serializable;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class RpaDbProcessingSumarry implements Serializable {

    private static final long serialVersionUID = 1L;

    private String opName ;
    private String processDate;
    private String sessionId;
    private Long totalCount ;
    private Long successCount ;
    private Long failedCount ;
}

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity(name = "RPASUMMARYAUDIT")
@NamedStoredProcedureQueries({



        @NamedStoredProcedureQuery(name = "SAVE_RPA_SUMMARY",

                procedureName = "MMHUSER.TEST_PKG.SAVE_RPA_SUMMARY",

                parameters = {
                        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_parm", type = RpaDbProcessingSumarry[].class),
                        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_affiliate_code", type = String.class)

                })

})
public class RpaProcessProcessSummary {

    @Id
    private Long id;
    private String name;
    private String prccessDate;
    private String session;
    private Long totalCount;
    private Long successCount;
    private Long failedCount;
}

@Repository
public interface RpaProcessProcessSummaryRepositary extends CrudRepository<RpaProcessProcessSummary, Long> {



    @Transactional(propagation = Propagation.REQUIRED,readOnly = false)
    @Procedure(name = "SAVE_RPA_SUMMARY")
    void  saveRpaSummaryData( RpaDbProcessingSumarry[] p_parm, String p_affiliate_code);
}

  public void call(RpaDbProcessingSumarry[] p_parm ) {



         SqlParameterSource in = new MapSqlParameterSource().addValue("p_parm", p_parm).addValue("p_affiliate_code","eng");
        Map<String, Object> execute = new SimpleJdbcCall(this.jdbcTemplate).withCatalogName("MMHUSER")
                .withProcedureName("TEST_PKG.SAVE_RPA_SUMMARY")
                .execute(in);

在这两种情况下，我都会遇到异常

PLS-00306：调用“SAVE_RPA_SUMMARY”时参数的数量或类型错误

ORA-06550：第 1 行，第 7 列：

有没有办法以任何方式从spring调用这个存储过程

标签： javaoraclestored-proceduresspring-data-jpajdbctemplate