I have a project that connects to an Oracle DB that contains some packages and stored procedures. The problem is that Spring JDBC returns an error message when try to call a stored procedure from a REST web service.
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call PKG_RECLAMO.SP_INSERTAR_RECLAMO()}]; SQL state [90022]; error code [90022]; Función "SP_INSERTAR_RECLAMO" no encontrada Function "SP_INSERTAR_RECLAMO" not found; SQL statement: call PKG_RECLAMO.SP_INSERTAR_RECLAMO() [90022-197]; nested exception is org.h2.jdbc.JdbcSQLException: Función "SP_INSERTAR_RECLAMO" no encontrada Function "SP_INSERTAR_RECLAMO" not found; SQL statement: call PKG_RECLAMO.SP_INSERTAR_RECLAMO() [90022-197]
some lines below ... shows another message ...
Caused by: org.h2.jdbc.JdbcSQLException: Función "SP_INSERTAR_RECLAMO" no encontrada Function "SP_INSERTAR_RECLAMO" not found; SQL statement: call PKG_RECLAMO.SP_INSERTAR_RECLAMO() [90022-197] at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
It seems that Spring JDBC template is looking for the procedure in H2 DB and is not using the Oracle connector instead.
@Repository
public class ClsReclamoDao implements ClsIReclamoDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public Long insertReclamo(ClsReclamoRequestBean objRequest) {
SimpleJdbcCall jdbcCall = new
SimpleJdbcCall(jdbcTemplate).withCatalogName("PKG_RECLAMO")
.withProcedureName("SP_INSERTAR_RECLAMO");
SqlParameterSource parameterSource = new MapSqlParameterSource()
.addValue("p_nuReclamoTipoReclamo",
"123")
.addValue("p_nuAnonimo", "1")
[more parameters...]
Map<String, Object> returnMap = jdbcCall.execute(parameterSource);
...
}
These are my POM dependencies
<dependency>
<groupId>com.oracle</groupId>
<artifactId>oracleConnector</artifactId>
<version>7.0</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
There is a way to tell spring that always uses the Oracle Connector instead of H2 DB ?
Update
When I try to put SCOPE = TEST on H2 dependency, like this...
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>test</scope>
</dependency>
Application fails at start and show this error message.
APPLICATION FAILED TO START
***************************
Description:
Failed to configure a DataSource: 'url' attribute is not specified and
no embedded datasource could be configured.
Reason: Failed to determine a suitable driver class
Action:
Consider the following:
If you want an embedded database (H2, HSQL or Derby),
please put it on the classpath.
If you have database settings to be loaded from a
particular profile you may need to activate it
(no profiles are currently active).
Update
this is my application properties file
spring.jmx.default-domain:appOracleService
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=userdb
spring.datasource.password=passdb
spring.datasource.driver-class-oracle.jdbc.driver.OracleDriver
## LOGGING
logging.config=classpath:logback.xml
logging.exception-conversion-word=%wEx
logging.file.max-history=0
logging.file.max-size=10MB
logging.level.org.springframework.web=INFO