Error "PLS-00306: wrong number or types of arguments in call to 'TEST'" when invoking Java an Oracle function with parameters of type BOOLEAN

Question

Error "PLS-00306: wrong number or types of arguments in call to 'TEST'" when invoking Java an Oracle function with parameters of type BOOLEAN

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm trying to call an Oracle function from Java, and I get the following error message:

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

This is the way I'm invoking the function:

String ejecucion = "{ ?=call prueba(?,?,?,?,?,?,?,?,?) }";

CallableStatement cons = c.ejecutarProcedimiento(ejecucion, 0);  // Aquí invoco al método "precall"
double monto = 0;

cons.registerOutParameter(1, java.sql.Types.FLOAT);

// Parametro del procedimiento almacenado
cons.setFloat(2, 2);
cons.setDouble(3, importe);
cons.setDate(4,TareasGenerales.convertirFecha(Parametros.cabezal.getFechaVencimientoPresentacion()));                                                                                 
 cons.setDate(5,TareasGenerales.convertirFecha(Parametros.cabezal.getFechaVencmientoPago()));
cons.setDate(6, TareasGenerales.convertirFecha(Parametros.fechaActual));
cons.setDate(7, TareasGenerales.convertirFecha(Parametros.fechaActual));
cons.setBoolean(8, false);
cons.setInt(9, Integer.valueOf(Parametros.cabezal.getAnioMesDesde()));
cons.setBoolean(10, false);

cons.executeQuery();

This is the way in which the function is implemented:

declare
 -- Boolean parameters are translated from/to integers: 
 -- 0/1/null <--> false/true/null 
  pa_a boolean := sys.diutil.int_to_bool(:a);
  pa_b boolean := sys.diutil.int_to_bool(:b);
   begin
 -- Call the function
 :result := prueba(c=> :c,
                                       d => :d,
                                       e => :e,
                                       f => :f,
                                       g => :g,
                                       h => :g,
                                       i => i,
                                       j => :j,
                                       k => k);
end;

FUNCTION Prueba(tipo_calc           IN NUMBER,
                    impte           IN NUMBER,
                    f_venc         IN DATE,
                    f_venc_p         DATE,
                    f_pre          IN DATE,
                    f_pa            IN DATE,
                    pa_tiene IN BOOLEAN,
                    pa_anio         IN NUMBER,
                    pa_buen     IN BOOLEAN) RETURN NUMBER;

Do you know what the source of the error may be?

java oracle jdbc plsql

asked by Rick Grimes 26.01.2017 в 22:17

source

2 answers

Problem to login with PHP Wordpress - Help with borders

score 0 · Answer 1

0

Finally I solved the problem, passing the arguments directly in the "execution" String instead of adding them later.

answered by 27.01.2017 в 20:47

score 0 · Answer 2

Indeed, as mentioned in the comments, Oracle JDBC drivers from versions prior to 12.2 are not able to handle the type BOOLEAN .

Beginning with Oracle 12.2 , it seems that it is possible to do so ( finally).

But in the meantime, instead of resorting to concatenating the parameters directly in the SQL string, a simple way to correct the problem is by passing a int instead of boolean in the Java code, and then using an expression CASE in SQL to convert int back to boolean :

String ejecucion = "{ ?=call prueba(?,?,?,?,?,?,case when ? = 0 then false else true end,?,case when ? = 0 then false else true end) }";

// ....

cons.setInt(8, 0); // 0 => false
// ...
cons.setInt(10, 1); // 1 => true