Sql – Spring JDBC and Oracle Function issues

functionoraclespringsql

I am trying to execute an SQL Function using Spring JDBC.

My code is

SimpleJdbcCall caller = new SimpleJdbcCall(this.jdbcTemplateRandOnline).withCatalogName("RATELIMIT_OWN").withFunctionName("Get_Logs");
RateLimitLogBean resultBean = null;
SqlParameterSource paramMap = new MapSqlParameterSource().addValue(P_YYYYMM, inputBean.getMonth(), Types.VARCHAR).addValue(P_NUMEC, inputBean.getNumec(), Types.INTEGER);
resultBean = caller.executeFunction(RateLimitLogBean.class, paramMap);

but when I run this code, I am getting below error

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call RATELIMIT_OWN.GET_LOGS()}];
SQL state [99999]; error code [17041]; Missing IN or OUT parameter at index:: 1;
nested exception is java.sql.SQLException: Missing IN or OUT parameter at index:: 1 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:388) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:351) at org.springframework.jdbc.core.simple.SimpleJdbcCall.executeFunction(SimpleJdbcCall.java:154)

I couldn't understand why it's throwing this error. For prepared statements we declare input and output params and compile them. But for functions, how can we compile when we are passing it as SqlParamSource

Sql Function

FUNCTION RATELIMIT_OWN.Get_Logs ( p_yyyymm VARCHAR2, p_numec NUMBER )

This function returns records of type

TYPE RATELIMIT_OWN.LOG_RECORD AS OBJECT
   (EVENTID              VARCHAR2(15),
    MSG                    VARCHAR2(2000),
    CREATE_DATE      DATE);

I have debugged to Spring framework. What I found is, in

org.springframework.jdbc.core.metadata.CallMetaDataContext.reconcileParameters method, I am getting this.metaDataProvider.getCallParameterMetaData() this arraylist with all values are null.

Any reason?

Best Answer

You need to add

.withoutProcedureColumnMetaDataAccess()

To your SimpleJdbcCall.

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

What’s the difference between a method and a function

A function is a piece of code that is called by name. It can be passed data to operate on (i.e. the parameters) and can optionally return data (the return value). All data that is passed to a function is explicitly passed.

A method is a piece of code that is called by a name that is associated with an object. In most respects it is identical to a function except for two key differences:

A method is implicitly passed the object on which it was called.
A method is able to operate on data that is contained within the class (remembering that an object is an instance of a class - the class is the definition, the object is an instance of that data).

(this is a simplified explanation, ignoring issues of scope etc.)

Best Answer

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

What’s the difference between a method and a function

Related Topic