I have this code; however, when I try to run Invoke, the first command will work and return me the correct value but command2 gives me an error posted below the C# code. Why can the C# not see the EPA package and what do I need to do to fix it?
Any help with regards to this would be much appreciated.
The signature of the function I am trying to call in PL/SQL is
FUNCTION NotificationGetNextID return integer;
Other potentially important information : I'm using this DLL to access the database http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
//todo set all returns to void so that no one can see internal structure of db
internal static class Data
{
private const String User = "xxx";
private const String Pass = "xxx";
private const String Source = "xxx";
private const String DateFormat = "dd/MMM/yyyy";
public static DatabasePackage DatabasePackageFactory(DatabasePackageType T)
{
switch (T)
{
case DatabasePackageType.EPA:
return new EPA();
default:
return null;
}
}
private class EPA : DatabasePackage
{
OracleConnection Conn;
public EPA()
{
Conn = new OracleConnection();
Conn.ConnectionString += "User Id=" + User + ";";
Conn.ConnectionString += "Password=" + Pass + ";";
Conn.ConnectionString += "Data Source=" + Source + ";";
Conn.Open();
}
public object Invoke(String identifier, params String [] args)
{
//if you remove the commented out lines the code will not work, however now it works perfectly
var Command = Conn.CreateCommand();
//var Command2 = Conn.CreateCommand();
//Command2.CommandType = CommandType.StoredProcedure;
Command.CommandType = CommandType.Text;
Command.CommandText = "select EPA.NotificationGetNextID from dual";// + identifier + parameters;
//Command2.CommandText = "EPA.NotificationGetNextID";
//var reader2 = ((Command2.ExecuteNonQuery()));
var reader = ((Command.ExecuteReader()));
reader.Read();
return reader[0];
}
public void Dispose()
{
Conn.Close();
}
}
}
internal interface DatabasePackage : IDisposable
{
object Invoke(String identifier, params String[] args);
}
internal enum DatabasePackageType
{
EPA
}
Error :
Oracle.DataAccess.Client.OracleException: ORA-06550: line 1, column 7:
PLS-00221: 'NOTIFICATIONGETNEXTID' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at EPA.Data.EPA.Invoke(String identifier, String[] args)
at EPA.ElectronicPriceAgreement.CompanyCreate()
How it is intended to be used
using (DatabasePackage dbp = Data.DatabasePackageFactory(DatabasePackageType.EPA))
{
return dbp.Invoke("NotificationGetNextId");
//return dbp.Invoke("CompanyCreate","key","description","","","","");
//return result;
}
The reason why this was not working was because I was calling the function incorrectly. In order to get the desire result, Invoke had to be like this
public object Invoke(String identifier, params object[] args)
{
using (var Conn = new OracleConnection(ConnectionString))
{
using (var Command = new OracleCommand())
{
Command.Connection = Conn;
Command.CommandText = "EPA."+ identifier;
Command.CommandType = CommandType.StoredProcedure;
using (var param = new OracleParameter())
{
param.OracleDbType = OracleDbType.Int32;
param.Direction = ParameterDirection.ReturnValue;
param.ParameterName = "return";
Command.Parameters.Add(param);
}
int nCount = 0;
foreach(object o in args)
{
using (var param = new OracleParameter())
{
param.OracleDbType = GetOracleDbType(o);
param.Direction = ParameterDirection.Input;
param.ParameterName = "arg" + nCount++;
param.Value = o;
if(param.OracleDbType != OracleDbType.Date)
Command.Parameters.Add(param);
else{
DateTime dt = (DateTime)o;
Command.Parameters.Add(dt.ToString(DateFormat),OracleDbType.Date).Value = dt;
}
}
}
Conn.Open();
Command.ExecuteNonQuery();
return Int32.Parse((Command.Parameters["return"].Value).ToString());
}
}
}
First and foremost, I need to close dispose all object implementing IDipsosable. Something I was not doing initially.
Secondly I need to change the command type to StoredProcedure, and set the return type. Oracle cares about the order so things should be set in the order they will be taken in by the function. The return should be first.
The function used to determine the type of data was something I copied from somewhere though I do not remember where I grabbed it from, I apologize for not giving proper credit to the initial creator.
private OracleDbType GetOracleDbType(object o)
{
if (o is string)
return OracleDbType.Varchar2;
if (o is DateTime)
return OracleDbType.Date;
if (o is Int64)
return OracleDbType.Int64;
if (o is Int32)
return OracleDbType.Int32;
if (o is Int16)
return OracleDbType.Int16;
if (o is byte)
return OracleDbType.Byte;
if (o is decimal)
return OracleDbType.Decimal;
if (o is float)
return OracleDbType.Single;
if (o is double)
return OracleDbType.Double;
if (o is byte[])
return OracleDbType.Blob;
return OracleDbType.Varchar2;
}