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