Hi,

I am using LLBLGenPro 2.5 with the latest version of the runtime libraries, Oracle 10gR2 / ODP.NET 10g and the SelfServicing templates for .NET 2.0.

• If a stored procedure parameter (or stored function return value) is of type "boolean", the Designer maps it to System.Byte.

• Code generation is successful, but stored procedure invocation using the ActionProcedures class fails with an Oracle exception.

Example 1:

create or replace function test_func (x in integer) return boolean
is
begin
    return x > 0;
end test_func;
/
show errors

Invocation of test_func yields the error:

ORA-06550: line 1, column 15:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Example 2:

create or replace procedure test_proc (x in integer, b out boolean)
is
begin
    b := x > 0;
end test_proc;
/
show errors

Invocation of test_proc yields the error:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEST_PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The same problem very probably exists for the ODP.NET 9.2 driver and the MS Oracle driver, too.

A fix would be much appreciated!

Thanks and best regards, Christoph

Hi cknittel, you can run the type converter plugin from the designer to convert byte types to booleans. the same problem happens with mysql.

goose wrote:

Hi cknittel, you can run the type converter plugin from the designer to convert byte types to booleans. the same problem happens with mysql.

No, unfortunately I can't. As far as I can see, this is only possible for table columns, not for stored procedure parameters. Or am I missing something?

Which ODP.NET version are you using? (complete version number, eg. 2.102.2.10)

I am using 2.102.2.20.

Please post the code from where you call the procedure.

My code is trivial:

        static void Main(string[] args)
        {
            Decimal x = 5;
            byte b = 0;

            ActionProcedures.TestFunc(x, ref b);
        }

(same for ActionProcedures.TestProc).

Boolean isn't an official type of Oracle, as far as I know, as ODP.NET 10g2 doesn't have a boolean type in its type parameter enum. Is 'boolean' a synonym type in your system?

LLBLGen Pro doesn't support it at the moment, IF oracle has added it in their very latest database system, so I'd suggest specifying either a byte or a number(1,0) return value.

Otis wrote:

Boolean isn't an official type of Oracle, as far as I know, as ODP.NET 10g2 doesn't have a boolean type in its type parameter enum. Is 'boolean' a synonym type in your system?

LLBLGen Pro doesn't support it at the moment, IF oracle has added it in their very latest database system, so I'd suggest specifying either a byte or a number(1,0) return value.

In Oracle, there is no boolean column type.

However, there is a boolean type in PL/SQL, and this type can be used for stored procedure parameters. As far as I can remember (which is back to Oracle 7 ;-), this type has always existed in PL/SQL.

The problem in the current version of LLBLGen Pro is that the user gets no message that boolean stored procedure parameters are not supported (yet). Instead, such parameters are mapped to System.Byte, and stored procedure invocation fails at runtime.

I would therefore wish that: - Support for boolean stored procedure parameters should be added in the next LLBLGen version. - In the meantime, the Designer should return an error message on catalog refresh stating that stored procedures with boolean parameters are not supported and should be rewritten to use number parameters.

Thanks and best regards, Christoph

See also

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/03_types.htm#10887

and the stored procedure/function examples using the boolean datatype in

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#591

cknittel wrote:

Otis wrote:

Boolean isn't an official type of Oracle, as far as I know, as ODP.NET 10g2 doesn't have a boolean type in its type parameter enum. Is 'boolean' a synonym type in your system?

LLBLGen Pro doesn't support it at the moment, IF oracle has added it in their very latest database system, so I'd suggest specifying either a byte or a number(1,0) return value.

In Oracle, there is no boolean column type.

However, there is a boolean type in PL/SQL, and this type can be used for stored procedure parameters. As far as I can remember (which is back to Oracle 7 ;-), this type has always existed in PL/SQL.

Ok

The problem in the current version of LLBLGen Pro is that the user gets no message that boolean stored procedure parameters are not supported (yet). Instead, such parameters are mapped to System.Byte, and stored procedure invocation fails at runtime.

It is indeed mapped to System.Byte (and OracleDbType.Byte)

I would therefore wish that: - Support for boolean stored procedure parameters should be added in the next LLBLGen version. - In the meantime, the Designer should return an error message on catalog refresh stating that stored procedures with boolean parameters are not supported and should be rewritten to use number parameters.

Thanks and best regards, Christoph

We can't support types which aren't specifyable as OracleDbType. OracleDbType doesn't contain an enum value for Boolean. This means that either the Boolean type is internally mapped onto something else than a byte or it's simply not usable from within .NET. ODP.NET's documentation doesn't talk about Boolean. Googling suggests Oracle doesn't care and that it's actually an int32.

So it is either: 1) we'll ignore procs with returntype boolean or 2) we'll map boolean to a different type.

Could you try in the generated code for the proc, where the parameters are specified, if you specify OracleDbType.Number or an int type, the proc will succeed without an error?

You are right, the boolean datatype is not avaible as an OracleDbType (for whatever reason...), see

http://forums.oracle.com/forums/thread.jspa?threadID=180923&tstart=0

So it seems that the only way to invoke a function/procedure with boolean parameters is to wrap it in an anonymous PL/SQL block which maps the booleans to e.g. chars ('Y'/'N') and to do the reverse mapping in C# after the invocation.

Example:

                int x = 5;
                string cmdtxt = "begin if TEST_FUNC(:1) then :2 := 'Y'; else :2 := 'N'; end if; end;";
                OracleCommand cmd = new OracleCommand(cmdtxt, con);
                cmd.CommandType = CommandType.Text;

                OracleParameter p1 = cmd.Parameters.Add("x", OracleDbType.Decimal, x, ParameterDirection.Input);
                OracleParameter p2 = cmd.Parameters.Add("b", OracleDbType.Char, 1, null, ParameterDirection.Output);

                cmd.ExecuteNonQuery();
                OracleString value = (OracleString)cmd.Parameters[1].Value;
                bool myBool = value.Value == "Y";

This actually works, but as it is ugly beyond belief you probably won't want to include it in LLBLGen...

So it would probably be best if the Designer was able to detect boolean stored procedure parameters during catalog refresh and to return an appropriate error message to the user.

Though I googled on this and ran into a patch for nhibernate related to this, which suggested that Integer was the type to use. I don't have a boolean proc in our oracle testschemas at the moment, hence my question if you could try if by altering the generated code for the proc call (in ActionProcedures.cs) if you could by changing the OracleDbType from Byte to Integer make it work. If that works, I can change the mapping in the driver.

Otis wrote:

Though I googled on this and ran into a patch for nhibernate related to this, which suggested that Integer was the type to use. I don't have a boolean proc in our oracle testschemas at the moment, hence my question if you could try if by altering the generated code for the proc call (in ActionProcedures.cs) if you could by changing the OracleDbType from Byte to Integer make it work. If that works, I can change the mapping in the driver.

I tried several types including OracleDbType.Int32 and couldn't get it to work. In the end I came up with the above code...

Could you point me to that NHibernate patch, please?

cknittel wrote:

Otis wrote:

Though I googled on this and ran into a patch for nhibernate related to this, which suggested that Integer was the type to use. I don't have a boolean proc in our oracle testschemas at the moment, hence my question if you could try if by altering the generated code for the proc call (in ActionProcedures.cs) if you could by changing the OracleDbType from Byte to Integer make it work. If that works, I can change the mapping in the driver.

I tried several types including OracleDbType.Int32 and couldn't get it to work. In the end I came up with the above code...

Could you point me to that NHibernate patch, please?

Sorry, found it already, I believe you were referring to

http://jira.nhibernate.org/browse/NH-302

For some reason they set DbType, not OracleDbType?!? Anyway, I didn't get it to work with that information either...

Ok, that closes it then. Thanks a million for testing. I'll add a warning to the designer.

(we'll add this in v2.6)