Invalid column type: 16

You might have encountered with Error: Invalid column type: 16 or java.sql.SQLException: Invalid column type: 16 when calling a procedure/function in in Java Environment. 

Cause of this Error:

We get this error while executing an oracle DB stored function which has return type as "BOOLEAN", in Java Environment:

CREATE OR REPLACE FUNCTION Check_Available_Money (v_card_no VARCHAR2, v_amount NUMBER)
  RETURN BOOLEAN
IS
  v_cur_Amount NUMBER(5);
BEGIN
  SELECT Balance into v_cur_Amount 
  FROM Card_Details
  WHERE cardno = v_card_no;

  IF v_cur_Amount> v_amount THEN
RETURN TRUE;
  ELSE
RETURN FALSE;
  END IF;
END;

/

Solution:

https://forums.oracle.com/message/10572948

BOOLEAN is a PL/SQL type and cannot be used in SQL.

You will need to write you own wrapper function that takes an integer and converts it to a boolean and calls your function.

Or you can use the 'sys.diutil.int_to_bool' which does that conversion.

BOOLEAN Data Type

The PL/SQL data type BOOLEAN stores logical values, which are the Boolean values TRUE and FALSE and the value NULL. NULL represents an unknown value.

Because SQL has no data type equivalent to BOOLEAN, you cannot:

* Assign a BOOLEAN value to a database table column

* Select or fetch the value of a database table column into a BOOLEAN variable

* Use a BOOLEAN value in a SQL statement, SQL function, or PL/SQL function invoked from a SQL statement.

According to Oracle's own FAQ, Oracle's JDBC driver doesn't support boolean datatypes sent/returned from the database. As the FAQ says the solution is:

* For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN, or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. 

* Obviously if you can't change the database you've a problem with this raw JDBC approach. Maybe have a look to JPublisher to see what it can do for you in this case.

Workaround for such situation is:

Creating a another function that uses that and return String:

CREATE OR REPLACE FUNCTION Check_Withdraw_Amount(v_card_no VARCHAR2, v_amount NUMBER)

RETURN VARCHAR2

IS

Exist BOOLEAN;

BEGIN

 Exist := Check_Available_Money(v_card_no, v_amount);

 IF Exist THEN

 RETURN 'TRUE';

 ELSE 

 RETURN 'FALSE';

 END IF;

END;

/