Call oracle function from Java JDBC
We have an Oracle function that we are trying to call from JDBC. It takes 3 inputs (String, Number and Date) and returns 1 Number:
create or replace function mww_avgcost
(in_prd_lvl_number prdmstee.prd_lvl_number%type,
in_org_lvl_number orgmstee.org_lvl_number%type,
in_sales_date prcmstee.prc_from_date%type)
RETURN NUMBER
AS
begin
Using Java JDBC code like this:
String call = "{ ? = call PMM.MWW_AVGCOST(?, ?, ?) }";
CallableStatement cstmt = conn.prepareCall(call);
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, productNumber);
cstmt.setInt(3, storeNumber);
// convert XML input to SQL date
java.sql.Date sqlDate = new java.sql.Date(saleDate.toGregorianCalendar().getTimeInMillis());
cstmt.setDate(4, sqlDate);
cstmt.execute();
BigDecimal resultFromFunction = cstmt.getBigDecimal(1);
log.info("resultFromFunction:" + resultFromFunction);
The result always returns 1, not the correct number. We did a great job with SQL Developer with the same parameters and it looks great. Is this the correct way to call a SQL function from JDBC?
source to share
It looks like you have a type mismatch. You set the out parameter to a sql integer, but then you get it as BigDecimal.
If sql function returns integer then use:callableStatement.getInt(1)
If sql function returns floating point number use
callableStatement.registerOutParameter(1, java.sql.Types.DOUBLE)
and also
callableStatement.getDouble(1)
The java and oracle type mapping can be found here: https://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html
If you really need BigDecimal due to rounding issues or whatever, your sql function should return DECIMAL or NUMERIC. And you should set your out parameter and getBigDecimal () as you are doing now.
source to share