Gettingn Oracle error PLS-00306: wrong number or types of arguments in call to stored procedure, and can't find where

Question

I am new to Oracle and I am using Spring and Hibernate. I am trying to call a stored procedure from Oracle and execute it, but I am getting an error in my logs that haven't pinpointed me to where in the SQL query things are wrong. Additionally, I am not allowed to edit the SQL.

The procedure:

procedure STORED_PROCEDURE
     ( first in varchar2
     , second in varchar2
     , third in varchar2
     , fourth inout varchar2
     , fifth inout varchar2
     , sixth in boolean
     );

This is the code that accessed the stored procedure. It holds 4 IN parameters and 2 INOUT parameters. Three variables I hardcode into the setParameter pieces (fourth, fifth, and sixth). The error occurs on the execute.

StoredProcedureQuery query = entityManager.createStoredProcedureQuery("STORED_PROCEDURE")
    .registerStoredProcedureParameter("first", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("second", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("third", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("fourth", String.class, ParameterMode.INOUT)
    .registerStoredProcedureParameter("fifth", String.class, ParameterMode.INOUT)
    .registerStoredProcedureParameter("sixth", Boolean.class, ParameterMode.IN)
    .setParameter("first", var1)
    .setParameter("second", var2)
    .setParameter("third", var3)
    .setParameter("fourth", "value")
    .setParameter("fifth", "value")
    .setParameter("sixth", true);

query.execute();

The full error:

Caused by: oracle.jdbc.OracleDatabaseException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STORED_PROCEDURE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I have tried changing the Boolean parameter to Integer.class, because I read that there may be an issue with SQL and boolean values, and nothing worked.

Answer 1

In Oracle, there is no BOOLEAN data type in SQL but there is a BOOLEAN data-type in PL/SQL. This means that if you declare the procedure as:

procedure STORED_PROCEDURE
     ( first  in     varchar2
     , second in     varchar2
     , third  in     varchar2
     , fourth in out varchar2
     , fifth  in out varchar2
     , sixth  in     boolean
     );

Then it can only be called from a PL/SQL scope and not from SQL.

You probably want to change to using data types that are supported in both SQL and PL/SQL and define the procedure as:

procedure STORED_PROCEDURE
     ( first  in     varchar2
     , second in     varchar2
     , third  in     varchar2
     , fourth in out varchar2
     , fifth  in out varchar2
     , sixth  in     number
     );

Then, internally to the procedure you can convert sixth from a 0/1 value to boolean FALSE/TRUE values.