1

I have an oracle package with procedure

 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);

and descirption

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;

i can invoke it using test it using

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;

i am trying to use spring data jpa to invoke this procedure or 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);

in both cases i am getting exception

PLS-00306: wrong number or types of arguments in call to 'SAVE_RPA_SUMMARY'

ORA-06550: line 1, column 7:

is there a way to invoke this stored procedure from spring in any way

  • Oracle JDBC driver does ___not___ support TYPEs defined within PL/SQL routines or packages. You need to create an explicit database type using the `CREATE TYPE` (DDL) statement. – Abra Dec 29 '19 at 10:47
  • Refer to _JDBC Developer's Guide_, which is part of the Oracle documentation, for your Oracle version. – Abra Dec 29 '19 at 10:58
  • Duplicate of https://stackoverflow.com/q/58026111/1509264 – MT0 Dec 29 '19 at 15:53

0 Answers0