I am currently evaluating DotConnect for Oracle an ran into a problem.
I would like to exeute a stored function that returns a scalar using entity framewok. My current approach runs with SQL-Server, it did run with the oracle data provider but it fails using dotConnect from Oracle.
I am new to Oracle.
Here's my function definition
Code: Select all
CREATE FUNCTION "FORMAT_IVEAS"."NEXTRECCOUNTER"
( vchTABLENAME IN NVARCHAR2 ) RETURN NUMBER AS nRID NUMBER(11,0);
BEGIN
...
RETURN nRID;
END
Code: Select all
<Function Name="NEXTRECCOUNTER" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="FORMAT_IVEAS">
<Parameter Name="vchTableName" store:Name="VCHTABLENAME" Type="NVARCHAR2" Mode="In" />
</Function>
Code: Select all
<FunctionImport Name="NEXTRECCOUNTER">
<Parameter Name="vchTableName" Mode="In" Type="String" />
</FunctionImport>
Code: Select all
<FunctionImportMapping FunctionImportName="NEXTRECCOUNTER" FunctionName="IVEASModel.Store.NEXTRECCOUNTER" />
Code: Select all
ent.Connection.Open();
using (DbCommand cmd = ent.Connection.CreateCommand())
{
cmd.CommandText = "IVEASEntities.NEXTRECCOUNTER";
cmd.CommandType = CommandType.StoredProcedure;
var tableparam = cmd.CreateParameter();
tableparam.DbType = DbType.String;
tableparam.ParameterName = "vchTableName";
tableparam.Direction = ParameterDirection.Input;
tableparam.Value = tableName;
cmd.Parameters.Add(tableparam);
// Add param
var retval = cmd.CreateParameter();
retval.DbType = DbType.Int32;
retval.ParameterName = "RetVal";
retval.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(retval);
try
{
cmd.ExecuteNonQuery();
}
finally
{
ent.Connection.Close();
}
return Convert.ToInt32(retval.Value);
}
I traced the commands sent to the database using dbMonitor.Devart.Data.Oracle.OracleException: ORA-06550: line 2, column 3:
PLS-00221: 'NEXTRECCOUNTER' is not a procedure or is undefined
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
What I found there looks quite confus. S.th. like
is sent to the server.BEGIN NEXTRECCOUNTER(:vchTableName); END;
The Parameter list only shows the input parameter, but no return parameter. I believe that this problem causes the PLS-00221.
So, is there a way the retrieve the function return value using dotconnect?
Any help would be appreciated.
brgds
Sven Weiberg