This website uses cookies for visitor traffic analysis. By using the website, you agree with storing the cookies on your computer.More information

Posts tagged: Oracle

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

CREATE OR REPLACE FUNCTION afunc(pIn BOOLEAN) RETURN BOOLEAN IS
BEGIN
  RETURN pIn;
END afunc;
/

CREATE OR REPLACE FUNCTION bfunc(pIn VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN pIn;
END bfunc;
/

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.

categories Development | rjahn | 18/12/2015 | comments Comments (0)

categories ,

© SIB Visions GmbH