Permanent ORA-06550 error, calling stored function from java application using jdbc

Question

i'm trying to call several stored functions from my java app, but whatever function i call i got the same error. For example, given this function:

   function insert_value (input_name varchar2) return number;

I'm trying to call it using:

   JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
   SimpleJdbcCall call= new SimpleJdbcCall(jdbcTemplate)
                    .withCatalogName("MY_PACKAGE_NAME")
                    .withFunctionName("insert_value")
                    .withoutProcedureColumnMetaDataAccess()
                    .declareParameters(
                            new SqlParameter("input_name", Types.VARCHAR));
   SqlParameterSource parameterMap = new MapSqlParameterSource()
                 .addValue("input_name", "John Doe");
   int idNumber = call.executeFunction(Integer.class,parameterMap);

I always get the same error:

    java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'INSERT_VALUE'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

As you can see the name of the parameter is correct, i've already checked that my jdbc driver supports Named Parameters and i do not how i can pass indexes instead of parameters names on SimpleJdbcCall.

Any advise? Keep in mind that i have few more complex functions which i invoke in the same way which return the same error.

try to use instead "call.executeFunction(Integer.class" call.executeFunction(BigDecimal.class — Alexander Prisadkov, Jul 29 '20 at 13:03
Nothing changed... this happens also with functions which returns VARCHAR2 — Zhaled Asufian, Jul 29 '20 at 13:08
Could you try with SqlParameterSource parameterMap = new MapSqlParameterSource().addValue("input_name", "John Doe", Types.VARCHAR); — Sujitmohanty30, Jul 29 '20 at 13:32
@Sujitmohanty30 just tried, nice idea, but unfortunately same outcome — Zhaled Asufian, Jul 29 '20 at 13:37
Unfortunately i do not have access to the function body but it works smoothly from the db client. Anyway the function simply takes the name as input, insert it in a table and return the id of the new entry. — Zhaled Asufian, Jul 29 '20 at 14:07

VN'sCorner · Accepted Answer · 2020-07-29T17:57:21.610

Firstly ensure that the object specification on the database matches to what is defined in the java app by checking from metadata tables. Execute the the query(if you don't have permissions better to get it from DBA in-charge)

select object_name,argument_name,position,data_type,data_length,in_out 
from   user_arguments
where  OBJECT_NAME ='MY_PROCEDURE_OR_FUNCTION' 
and    Package_name='MY_PACKAGE'

Sample output for an object,

For Function you need return parameter and it should be the first argument.Modify the code to add the out parameter as below.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
   SimpleJdbcCall call= new SimpleJdbcCall(jdbcTemplate)
                    .withCatalogName("MY_PACKAGE_NAME")
                    .withFunctionName("insert_value")
                    .withoutProcedureColumnMetaDataAccess()
                    .declareParameters(new SqlOutParameter("return",Types.INTEGER),
                            new SqlParameter("input_name", Types.VARCHAR));
   SqlParameterSource parameterMap = new MapSqlParameterSource()
                 .addValue("input_name", "John Doe");
   int idNumber = call.executeFunction(Integer.class,parameterMap);

However when dealing with stored procedure it is slightly different,

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(springTemplate) 
.withCatalogName("PACKAGE_NAME").withProcedureName("PROCEDURE_NAME")
.withReturnValue().withoutProcedureColumnMetaDataAccess()
.declareParameters(new SqlParameter("INPUT_PARAMETER", Types.VARCHAR),
new SqlOutParameter("OUTPUT_PARAMETER_NAME", Types.VARCHAR));

If you have over-rided functions/procedures, then use useInParameterNames to specify the list of IN parameter names to include for a given signature.

Sujitmohanty30 · Answer 2 · 2020-07-30T06:48:15.180

Try below, (there could be other way still)

If I see the failure message, the input_name is not bind to the actual call.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate).withCatalogName("MY_PACKAGE_NAME")
                .withFunctionName("insert_value");

        SqlParameterSource parameterMap = new MapSqlParameterSource().addValue("input_name", "John Doe",
                Types.VARCHAR);

        BigDecimal idNumber = call.executeFunction(BigDecimal.class, parameterMap);

OR

With your approach we have to specify the return parameter as in case of function the first argument is considered being the return value as a result in this case the first parameter is always omitted during the actual call.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate).withCatalogName("MY_PACKAGE_NAME")
                .withFunctionName("insert_value").withoutProcedureColumnMetaDataAccess().declareParameters(
                        new SqlOutParameter("return", Types.INTEGER), new SqlParameter("input_name", Types.VARCHAR));

        SqlParameterSource parameterMap = new MapSqlParameterSource().addValue((String) "input_name", "John Doe",
                Types.VARCHAR);

        Integer idNumber = call.executeFunction(Integer.class, parameterMap);

Permanent ORA-06550 error, calling stored function from java application using jdbc

2 Answers2