I am unable to run a .net stored proc with the above error. I have tried on 2 different oracle 12c installs. The procedure deploys ok and the function looks correct in the db, it's just the sample function from the documentation (as per below). I see then error when trying to run the function from VS or when calling select sys.getdeptno(1) from dual from pl/sql developer.
I have sys access to the DB in question.
I am using .net 2015 enterprise and building the solution as x86 with the Oracle Unmanaged DataAccess dll 4.121.2.0. With the same dll I can run an x86 app from the server itself that just creates a simple connection. I have also tried building as x64 and AnyCpu and using OracleDataAccess.dll 2.121.2.0 but without success. Any ideas?
p.s. i am tagetting .net framework 4. I have enable ODT.NET tracing which looks as follows:
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) OraClrExecute()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) NormalizeAndCheckAssemblyName()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) NormalizeAndCheckAssemblyName(): hr=0 Line=4725
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) CreateMethodParameterList()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) CreateNewOracleMethodParameterList()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) CreateNewOracleMethodParameterList(): hr=0 Line=3656
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) SetOracleMethodParameter()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) SetOracleMethodParameter(): hr=0 Line=4285
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) CreateMethodParameterList(): hr=0 Line=2406
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) CreateReturnParameter()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) SetOracleMethodParameter()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) SetOracleMethodParameter(): hr=0 Line=4285
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) CreateReturnParameter(): hr=0 Line=4773
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) ExecuteMethod()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) ConvertParamListToSafeArray()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) ConvertParamListToSafeArray(): hr=0 Line=4374
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ERROR) ExecuteMethod(): hr=80070057 Line=2070
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) ExecuteMethod(): hr=80070057 Line=2140
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ERROR) OraClrExecute(): hr=80070057 Line=973
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) FreeMethodParameterList()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) FreeMethodParam()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) FreeMethodParam(): hr=0 Line=2535
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) FreeMethodParameterList(): hr=0 Line=2437
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) FreeMethodParam()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) FreeMethodParam(): hr=0 Line=2535
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ERROR) OraClrExecute(): hr=80070057 Line=1022
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) RaiseOracleException()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (ENTRY) ssextproccallmem_calloc()
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) ssextproccallmem_calloc(): hr=0 Line=4403
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) RaiseOracleException(): hr=0 Line=4605
TIME:2017/05/09-11:25:56:023 TID:5dc4 (EXIT) OraClrExecute(): hr=80070057 Line=1044
code:
public static int GetDeptNo(int empno)
{
int deptno = 0;
// Check for context connection
OracleConnection conn = new OracleConnection();
if (OracleConnection.IsAvailable == true)
{
conn.ConnectionString = "context connection=true";
}
else
{
throw new InvalidOperationException("context connection" +
"not available");
}
conn.Open();
//Create and execute a command
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT DEPTNO FROM EMP WHERE EMPNO = :1";
cmd.Parameters.Add(":1", OracleDbType.Int32, empno,
System.Data.ParameterDirection.Input);
OracleDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
deptno = rdr.GetInt32(0);
rdr.Close();
cmd.Dispose();
conn.Close();
return deptno;
} // GetDeptNo