Using Boolean Datatype with Oracle and JDBC
Oracle doesn't use/define the SQL Type Boolean. You can't define a column in a table as Boolean. It's possible to use boolean in PL/Sql e.g. for procedures or functions but you can't call the function from SQL.
Example functions
It's possible to call:
but it's not possible to call:
or
(1 = true, 0 = false; boolean is mapped to integer in Oracle)
If you want to call a procedure or function which defines boolean parameters, it's not possible without tricks with Oracle JDBC driver. This is annoying.
We solved the problem in JVx 2.4 and it's now possible to call functions or procedures without tricks. Here's a "more complex" example. We define a function with an input parameter and a procedure with an input/output parameter in a package:
FUNCTION test(pOutput BOOLEAN) RETURN BOOLEAN;
PROCEDURE testBoolOut(pOutput IN OUT BOOLEAN);
END;
/
CREATE OR REPLACE PACKAGE BODY TestBoolean IS
FUNCTION test(pOutput BOOLEAN) RETURN BOOLEAN IS
BEGIN
IF (pOutput) THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;
PROCEDURE testBoolOut(pOutput IN OUT BOOLEAN) IS
BEGIN
IF (pOutput) THEN
pOutput := FALSE;
ELSE
pOutput := TRUE;
END IF;
END;
END;
/
Now we use JVx' DBAccess to call the function and procedure.
DBAccess dba = new OracleDBAccess();
dba.setUrl("jdbc:oracle:thin:@oravm:1521:XE");
dba.setUsername("test");
dba.setPassword("test");
dba.open();
//Function call
Assert.assertEquals(Boolean.TRUE, dba.executeFunction("TESTBOOLEAN.TEST", Types.BOOLEAN, Boolean.FALSE));
//Procedure call
InOutParam param = new InOutParam(Types.BOOLEAN, Boolean.FALSE);
dba.executeProcedure("TESTBOOLEAN.TESTBOOLOUT", param);
Assert.assertEquals(Boolean.TRUE, param.getValue());
The whole type mapping was done in OracleDBAccess and it's invisible for you. Simply use the API to call procedures or functions.
Development | rjahn | 18/12/2015
RSS-Feed