OracleType for a pl/sql function with a boolean return value?

Question

Hi,

I'm developing this app where I have to call a function written in PL/SQL that returns a boolean. As I understand, bool is not a type in SQL, but in PL/SQL, so what will the return type for the function be?

command.Parameters.Add("P_RETURN", OracleType.???);

(For the record: I have no control over the PL/SQL end of things, so I am not able to rewrite the function)

Answer 1

You could call the SYS.diutil.bool_to_int function with the result of calling your function. For example:

SYS.diutil.bool_to_int(your_function(...))

From the documentation:

bool_to_int: translates 3-valued BOOLEAN TO NUMBER FOR USE IN sending BOOLEAN parameter / RETURN VALUES BETWEEN pls v1 (client) AND pls v2. since sqlnet has no BOOLEAN bind variable TYPE, we encode booleans AS false = 0, true = 1, NULL = NULL FOR network transfer AS NUMBER

Answer 2

You have to convert the PL/SQL only BOOLEAN type to a SQL supported type. I know, this is painful : welcome to the Oracle world. Here is Steven Feuerstein reusable way to deal with it.

As a side note, BOOLEANs are considered useless in SQL (by Oracle anyway).