"Invalid SQL statement" error calling Oracle stored proc

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
cbruen1
Posts: 9
Joined: Wed 23 Nov 2011 20:18

"Invalid SQL statement" error calling Oracle stored proc

Post by cbruen1 » Wed 23 Nov 2011 20:43

Hi - I'm using EF 4.1 Code First talking to an Oracle 10 database. Up until now I've been using EF to create the tables and do the foreign key relationships etc an everything's been fine. However I needed to add a simple stored proc that returns a sequence number and call it when a record gets created. This is causing an error no matter what way I try to call it. Here's the stored proc and sequence code which works fine when called from Oracle itself:

--Sequence:
CREATE SEQUENCE "NDFIBIOBAS"."SID_SEQ" MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ;

--SP:
create or replace
PROCEDURE GET_NEXT_SID_SEQUENCENR
(sid_seqnr OUT NUMBER)
AS
BEGIN
select SID_SEQ.NEXTVAL INTO sid_seqnr from DUAL;
END GET_NEXT_SID_SEQUENCENR;

And here's some of the ways I try to call it:

var parameter = new Devart.Data.Oracle.OracleParameter();
parameter.ParameterName = "SID_SEQNR";
parameter.Direction = ParameterDirection.Output;
parameter.OracleDbType = Devart.Data.Oracle.OracleDbType.Number;

//Attempt 1
var rc = this.Database.ExecuteSqlCommand("GET_NEXT_SID_SEQUENCENR", parameter);
return rc;

//Attempt 2
var rc = this.Database.SqlQuery("GET_NEXT_SID_SEQUENCENR", new OracleParameter() { DbType = System.Data.DbType.Int64, Direction = System.Data.ParameterDirection.Output, ParameterName = "SID_SEQNR" }).SingleOrDefault();
return rc;

//Attempt 3
var rc = this.Database.SqlQuery("EXECUTE GET_NEXT_SID_SEQUENCENR", parameter).SingleOrDefault();
return rc;

The error I'm getting is pretty vague:
{"ORA-00900: invalid SQL statement"}

Here's the stack trace:
at Devart.Data.Oracle.ap.d()
at Devart.Data.Oracle.y.m()
at Devart.Data.Oracle.y.c()
at Devart.Data.Oracle.v.a(Int32 A_0, bf A_1)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at System.Data.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, MergeOption mergeOption, Object[] parameters)
at System.Data.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery[TElement](String sql, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlQueryAsIEnumerable[TElement](String sql, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery(Type elementType, String sql, Object[] parameters)
at System.Data.Entity.Internal.InternalSqlNonSetQuery.GetEnumerator()
at System.Data.Entity.Internal.InternalSqlQuery`1.GetEnumerator()
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
at StudyDatabase.StudyDesignContext.GetNextSIDSequenceValue(Int32 dummy) in C:\dev\checkouts\trunk\StudyDatabase\StudyDesignContext.cs:line 213
at StudyRepository.EFStudyDesignRepository.GetNextSequenceForSid() in C:\dev\checkouts\trunk\StudyRepository\EFStudyDesignRepository.cs:line 517
at StudyDefinition.Controllers.StudyDesignController.GetNextSequenceForSid() in C:\dev\checkouts\trunk\StudyDefinition\Controllers\StudyDesignController.cs:line 1007
at StudyDefinition.Controllers.StudyDesignController.Create(StudyDesignViewModel studyDesignViewModel, ICollection`1 nccs) in C:\dev\checkouts\trunk\StudyDefinition\Controllers\StudyDesignController.cs:line 180
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.c__DisplayClass15.b__12()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)

Thanks in advance..any help is much appreciated

cbruen1
Posts: 9
Joined: Wed 23 Nov 2011 20:18

Post by cbruen1 » Fri 25 Nov 2011 09:28

Ok so does anyone have any thoughts on this..is what I'm trying to do supported by the devArt drivers?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 25 Nov 2011 11:04

Please try using

Code: Select all

            //Attempt 1
            var rc = ctx.Database.ExecuteSqlCommand("BEGIN GET_NEXT_SID_SEQUENCENR(:SID_SEQNR); END;", parameter);
            return parameter.Value;
instead of

Code: Select all

            //Attempt 1 
            var rc = this.Database.ExecuteSqlCommand("GET_NEXT_SID_SEQUENCENR", parameter); 
            return rc;
Our support has a two business day response policy: http://www.devart.com/dotconnect/oracle ... pport.html.

cbruen1
Posts: 9
Joined: Wed 23 Nov 2011 20:18

Post by cbruen1 » Fri 25 Nov 2011 12:36

Shalex most excellent this appears to work so thanks. Wasn't aware of the 2 day response policy, duly noted :)

Post Reply