Using Boolean Datatype with Oracle and JDBC

Post to Twitter

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:

SELECT bfunc('Hello') FROM dual;

but it's not possible to call:

SELECT afunc(true) FROM dual;

or

SELECT afunc(1) FROM dual;

(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:

CREATE OR REPLACE PACKAGE TestBoolean IS
 
  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.

//DB connection
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.

Leave a Reply

Spam protection by WP Captcha-Free