Oracle ODP data types error calling stored procedures

Question

I am converting a legacy application from using the deprecated Microsoft .Net data provider for Oracle (Sql.Data.OracleClient.dll) to Oracle's own .Net ODP provider (OracleManagerDataAccess.dll) using the latest Nuget package. The database server is Oracle 11g

A problem I don't understand is that when the application calls a stored procedure using the Microsoft provider it is fine, but when I switch to using the Oracle ODP I get the following error when calling the stored procedure:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error\nORA-06512: at line 1

I have distilled the code down to a very simple console application that exhibits the same problem (see below), but I can't work out what I need to do differently with the Oracle ODP. Executing 'normal' sql emitted from the application seems fine.

The calling IDbCommand object passes 4 parameters, two of which are string, two are ints. The stored procedure header is

PROCEDURE  CreateSampleResults(varSampleCode SampleResults.SampleCode%TYPE, varTestPosition SampleResults.TestPosition%TYPE, varTestCode TestComponents.TestCode%TYPE, varTestVersion TestComponents.AuditNumber%TYPE) 

Nothing in the database has changed, just the data provider in the .Net application. The command Parameters collections are identical whether using either the Microsoft or Oracle data providers.

Here's the simple application exhibiting the problem:

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
//using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace oracleconnect
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                InitializeDBConnection();
                CreateSampleResults("S0106", "a", 2, 1);
            }
            finally
            {
                if (_con.State==System.Data.ConnectionState.Open)
                _con.Close();
            }
        }

        static private OracleConnection _con;
        private const string connectionString =
        "Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledbserver2)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = orcdb10g))); User ID = myDatabasenameHere; Password=myPasswordhere";

        private static void InitializeDBConnection()
        {
            _con = new OracleConnection();
            _con.ConnectionString = connectionString;
            _con.Open();
        }

        public static IDbCommand CreateCommand(IDbConnection cn, string procedureName)
        {

            IDbCommand command = cn.CreateCommand();
            command.CommandTimeout = 30;
            command.Connection = cn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = procedureName;

            return command;
        }

        public static void CreateSampleResults(string sampleCode, string testCode, short testVersion, short testPosition)
        {
            {
                using (IDbCommand cmd = CreateCommand(_con, "CreateSampleResults"))
                {
                    SetParameter("Oracle", cmd, "SampleCode", sampleCode);
                    SetParameter("Oracle", cmd, "TestCode", testCode);
                    SetParameter("Oracle", cmd, "TestVersion", testVersion);
                    SetParameter("Oracle", cmd, "TestPosition", testPosition);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static void SetParameter(string databaseType, IDbCommand command, string name, object value)
        {
            System.Data.IDataParameter commandParameter = command.CreateParameter();
            commandParameter.ParameterName = string.Format("var{0}", name);
            commandParameter.Value = value;
            var x = commandParameter.DbType;
            command.Parameters.Add(commandParameter);
        }

        public enum DatabaseType { DBError, OLEDB, SQLServer, Oracle, /*SQLite,*/ Odbc };

    }
}

I suspect the problem may be related to the use of inferred datatypes in the stored procedure (ie tablename%TYPE), but that is a guess. Does anyone know what needs to be done to get stored procedure calls to work with the Oracle ODP?

TIA

Answer 1

You should add to your commandParameter the database DbType. The error is related to a type conversion in oracle, when that final command is called. This error is cause by a conversion of commandParameter.Value to the final database type.

https://msdn.microsoft.com/en-us/library/system.data.idataparameter.dbtype(v=vs.110).aspx