Could you provide MyPkg.MyFunction definition? C# mapping and sql (no function logic required)

Hi, sure:

[Sql.Expression("MyPkg.MyFunction({0})", ServerSideOnly=true)]
public static bool MyFunction(string myParameter)

What is your function definition? It doesn't reproduce with function like that:

CREATE OR REPLACE FUNCTION issue3742(param TEXT) RETURNS BOOL
AS $$ BEGIN RETURN true; END $$ LANGUAGE PLPGSQL;

Hi the function definition in plsql is:

FUNCTION MY_FUNCTION (myParameter in VARCHAR2) RETURN BOOLEAN IS
BEGIN
--...function implementation....
END

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.

my bad, didn't noticed it is oracle, tried testing it using pgsql (that's why we have all those pesky fields in issue template... 😄 )

From the C# definition, there's no way to know/assume if the PL/SQL function is defined as BOOLEAN or NUMBER.
So I suppose it has to be fixed using a DbType or similar on Sql.Expression attribute.

Notice that a PL/SQL function that returns BOOLEAN cannot be used in SQL like so WHERE my_function(), as boolean is not supported in SQL statements.
It can only be used inside PL/SQL blocks or called directly as in this issue.

Instead that kind of things must be done as usual with a function returning another type, e.g. number, and a comparison, maybe WHERE my_number_function() = 1

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

Why introduce boolean and then make it unavailable to SQL?

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

oh yeah, sorry I missed this:

[Sql.Expression("MyPkg.MyFunction({0})", ServerSideOnly=true)]

You cannot use a function that returns BOOLEAN inside SQL.
Oracle just doesn't support it.
BOOLEAN can only be used inside PL/SQL code (a.k.a. BEGIN..END procedural code, packages, etc.).

Somehow I thought this issue was about invoking such a function through

new OracleCommand 
{ 
  CommandType = CommandType.StoredProcedure,
  CommandText = "MyPkg.MyFunction",
}

Which works on recent ODP.NET releases and returns a bool.

Woah I did not know that you can do WITH FUNCTION...END SELECT... 👍

@jods4 could you clarify how to call it as sproc? I get PLS-00221: xxx is not a procedure or is undefined

In principle, you should create a DbCommand, set the CommandText to the function name (incl. schema and package if necessary), set CommandType to StoredProcedure, set parameters appropriately (incl. one for the return value, which is its own specific ParameterType) and call ExecuteNonQuery.

Note that if the function is in a package, it needs to be exported in the package header.
Be sure to have the grants required to call it.

Haaa, it works. But we need to change boolean handling for oracle to use linq2db stored procedures API.

But not for this release definitely

@MaceWindu not sure what your plan is, but keep in mind that creating a bool function in the model of an Oracle DB is quite ambiguous in terms of what the DB implementation is...

If I were to only call this function directly from C#, I might create a function that returns BOOLEAN.

If I wanted to include it in SQL code, I'd probably create a function that returns NUMBER and count on linq2db handling to convert a C# query like table.Where(t => MyFunc(t.x)) into WHERE MyFunc(t.x) = 1.

That said, nothing prevents me from calling this second function from C# as well, so linq2db needs a "hint" to decide if it returns a real boolean or a converted bool (number). Or maybe you can probe for a true bool in the stored proc code and if not, fallback to the converted bool. Works be sounds more complicated and specific for linq2db.

We need to enable boolean type support and not break existing mappings to number. Which could be challenging. In referenced PR I just disabled number mappings to make test work.