linq2db: Execute function returning boolean with input parameters in PLSQL

Ok, there is nothing we can do here as this error is returned by Oracle.

I’ve managed to call function using this definition (taken from here) but it is not very exciting…:

Option 1: use Execute

db.Execute(@"WITH
FUNCTION convert_bool(i IN VARCHAR2) RETURN NUMBER AS
BEGIN
	RETURN CASE ISSUE3742(i) WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;
END convert_bool;
SELECT convert_bool(:p) FROM SYS.DUAL", new { p = "test" });

Option 2: use Sql.Expression and query prefix

// ** marker instructs linq2db to add "hint" before query
db.NextQueryHints.Add(@"**WITH
FUNCTION convert_bool(i IN VARCHAR2) RETURN NUMBER AS
BEGIN
	RETURN CASE ISSUE3742(i) WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;
END convert_bool;");

db.Select(() => Issue3742Function("test"));

[Sql.Expression("convert_bool({0})", ServerSideOnly = true)]
public static bool Issue3742Function(string parameter) => ThrowHelper.ThrowInvalidOperationException<bool>();

Option 3: you can always add wrapper function in Oracle to call plsql function and convert result to number

Sometimes I wonder what’s wrong with Oracle? 🤦‍♂️

Why introduce boolean and then make it unavailable to SQL?

I suspect that the default Oracle mappings for data types might be at fault here.

Oracle has a BOOLEAN in PL/SQL but not in regular SQL. That’s why the default linq2db mapping for C# bool is number (1 is true and 0 is false).

But in a funny twist, if you’re calling directly into a PL/SQL function that returns bool, such as MY_FUNCTION you actually get an OracleBoolean, not OracleNumber.

Most likely the problem here is that linq2db expects a number from the db call and fails because it’s a boolean.

Note that support for PL/SQL booleans in managed ODP.NET is semi-recent. In managed ODP.NET it’s supported since 12.2 and in ODP.NET Core since 18.3.