CREATE TABLE RTIME.DATATYPETEST
(
   INT16  NUMBER(3)
)

CREATE PROCEDURE SPDATATYPETEST(PINT16 OUT DATATYPETEST.INT16%TYPE)
IS
BEGIN
   PINT16 := 3;
END SPDATATYPETEST;

I added this stored procedure as an Action Stored Procedure to an LLBLGen project and generated the code.

This is the code that was generated for the procedure:

public static int Datatypetest(ref System.Int16 pint16)
{
    // create parameters
    OracleParameter[] parameters = new OracleParameter[1];
    parameters[0] = new OracleParameter("PINT16", OracleDbType.Decimal, 0, ParameterDirection.Output, true, 3, 0, "",  DataRowVersion.Current, pint16);
    // Call the stored proc.
    int toReturn = DbUtils.CallActionStoredProcedure("RTIME.SPDATATYPETEST", parameters, null);
    if(parameters[0].Value!=System.DBNull.Value)
    {
        pint16 = (System.Int16)ValueConverter.Convert(parameters[0]);
    }
    for(int i=0;i<1;i++)
    {
        if(parameters[i] != null)
        {
            parameters[i].Dispose();
        }
    }
    return toReturn;
}

It always fails with the InvalidCastException at the line where data type conversion happens.

When you set a breakpoint, on that line, what's the value of parameters[0].Value ?

parameters[0].Value is an Oracle.DataAccess.Types.OracleDecimal. ((OracleDecimal )parameters[0].Value).Value is a System.Decimal, and its value is 3.

It goes wrong because your variable apparantly is an Int16, though the parameter and value are of type Decimal. As PL/SQL reports proc parameters which are of type NUMBER, as simply NUMBER, not NUMBER(3,0) or so, they're reported as NUMBER(38,0), which results in a decimal type, which is the type used for the parameter.

So how it could become a .NET type of Int16 is beyond me. I'll now try your proc and table on 9i to see if I can reproduce it.

Ok, the table colum specification as type indeed reports a NUMBER(3,0) as parameter type. I didn't know that, which is a way to surpass the dreaded NUMBER(38,0) requirement of PL/SQL.

I'll now check why decimal is used as the parameter type instead of Int16

(edit): Ok, this is a problem which requires some serious architectural changes. The problem is that only Oracle requires 3 parameters for retrieving the provider type instead of one: the dbtype, the precision and the scale. All other databases just require the dbtype.

This gives a problem as the code generator has just 1 method to call to retrieve the provider type to emit in the code (the value after OracleDbType. It's now 'decimal', it should be 'Int16'), and that method accepts just the dbtype, which is then used by the driver to find the actual provider type for that dbtype. As the dbtype is NUMBER, the provider type is Decimal. ODP.NET's parameters which have to deal with NUMBER types work ok when their type is OracleDbType.Decimal.

The obvious solution is to add precision and scale to the routine as well, and call that method from the code generator. The problem is that this is an architectural change in 2 parts of the system which will thus break installations and we can't do that.

I'll look into making the change in V2, which is currently in beta, as I can make architectural changes there until that one is released as final.

I could add the converter routine I use in the driver to the proc template as well. This then gives this piece of code for the Decimal case clause in the converter routine in the ActionProcedures.cs file:

                case "Oracle.DataAccess.Types.OracleDecimal":
                    decimal value = ((OracleDecimal)parameter.Value).Value;
                    int actualPrecision = parameter.Precision;
                    if(parameter.Precision==0)
                    {
                        actualPrecision = 38;
                    }
                    toReturn = value;

                    // Following code is based on ODP.NET's conversion routine, as it is pretty obscure what ODP.NET's doing with values read.
                    // the if statements are from ODP.NET. 
                    if(parameter.Scale==0)
                    {
                        if(actualPrecision < 5)
                        {
                            toReturn = System.Convert.ToInt16(value);
                        }
                        else
                        {
                            if(actualPrecision < 10)
                            {
                                toReturn = System.Convert.ToInt32(value);
                            }
                            else
                            {
                                if(actualPrecision < 19)
                                {
                                    toReturn = System.Convert.ToInt64(value);
                                }
                            }
                        }
                    }
                    else
                    {
                        // scale specified.
                        if(actualPrecision < 8)
                        {
                            toReturn = System.Convert.ToSingle(value);
                        }
                        else
                        {
                            if(actualPrecision < 16)
                            {
                                toReturn = System.Convert.ToDouble(value);
                            }
                        }
                    }

This then converts the value properly to the right type, if possible. I'll add the convert routine as mentioned above to the templates so the next build will have this code. You can apply this code to your set of templates now, by altering actionProcedures.template in the folder Drivers\Oracle\Templates\C#

Fixed in next build by adding the routine above to the actionprocedures templates