Currently, I am trying to use a store procedure (SP) that was created in Oracle, from Spring Data, with JPA and the annotation @Procedure.
It seems easy, but the procedure has a Table type parameter (cause it is an array), and as soon as I execute the method in the repository I am getting the following error message:
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_NAME' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
The SP_NAME (store procedure name) has the following declaration:
PROCEDURE SP_NAME(mv_name IN MVIEWS_ARRAYS) as ...... more code
Like you can see in the declaration SP, it is making reference to MVIEWS_ARRAYS type, that is declared like a TYPE, like follows:
create or replace TYPE MVIEWS_ARRAYS IS TABLE OF VARCHAR2 (1000);
When I try to use the SP from the Oracle client, with the following code, it is working fine:
DECLARE
mv_name MVIEWS_ARRAYS := MVIEWS_ARRAYS ('1', 'Two', 'Something else');
BEGIN
SP_NAME(mv_name);
END;
However when I try to call the SP from the JAVA, with the following code like an example of a repository;
@Repository
public interface InterfaceRepositoryExample extends JpaRepository<SomeEntity, Integer>{
@Async
@Procedure("SP_NAME")
public void spName(String[] mvName);
}
I am getting the error in the parameter types (the error that is above).
I have searched about this, but I have not had luck, there are not a type relation between Table type in Oracle and some primitive or wrapper type in Java, MoreOver, I have found lots post without response and also some codes to create dynamic objects from Java, but I do not know if this is I am searching.
Does someone have some idea about how I can solve this mistake?.
Regards.
EDITED:
I have tried to do it in different ways, but I was not able to use the SP.
1.- Using Array instead String[], (Ref: https://docs.spring.io/spring-data/jdbc/old-docs/2.0.0.M1/reference/html/orcl.datatypes.html, 6.2 epigraph)
@Repository
public interface InterfaceRepositoryExample extends JpaRepository<SomeEntity, Integer>{
@Async
@Procedure("SP_NAME")
public void spName(Array mvName);
}
from the service I am converting String[] to Array:
java.sql.Array sqlArray = dataSource.getConnection().unwrap(OracleConnection.class).createOracleArray("MVIEWS_ARRAYS ", data);
But I am getting the error message:
org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null
2.- With CallableStatement:
public void useSP(Array array) throws SQLException {
CallableStatement proc = null;
String sql = "{ call SP_NAME(?) }";
try{
proc = dataSource.getConnection().unwrap(OracleConnection.class).prepareCall(sql);
proc.setArray(1, array);
proc.execute();
}finally{
proc.close();
}
}
from the service I am converting String[] to Array:
java.sql.Array sqlArray = dataSource.getConnection().unwrap(OracleConnection.class).createOracleArray("MVIEWS_ARRAYS ", data);
useSP(sqlArray);
But I am getting the error message:
java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_NAME' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
