I need some help calling a stored procedure designed to add sequence number to each row added by an Api POST method.
At the moment I get this error message: "ExceptionMessage": "ORA-20001: Get Next Sequence Failed.. -936 ORA-00936: missing expression\nORA-06512: at \"FOO_BAR.PROC_NEXT_SEQUENCE\", line 19\nORA-06512: at line 1"
I have tried numerous code changes suggested in questions on here and elsewhere but due to my lack of c#/Oracle specific knowledge I'm beginning to feel as if I'm going round in circles.
The procedure was tested successfully in SQL Developer.
I’m using VS2013 (update 5), Entity Framework v6.1.3, ODPnet via Nuget, Oracle 11.2 and Postman.
PROCEDURE PROC_NEXT_SEQUENCE(
p_owner varchar2,
p_table varchar2,
p_seq_name varchar2,
p_seq_value out number)
AS
v_sql varchar2(4000) ; v_seq_value number :=0;
BEGIN
if length(p_seq_name) >0 then v_sql := 'select '||p_owner||'.'||p_seq_name||'.nextval AS NV from dual';
else v_sql := 'select '||p_owner||'.'||p_table||'_seq.nextval AS NV from dual';
end if;
execute immediate v_sql into v_seq_value; p_seq_value := v_seq_value;
exception when others then raise_application_error(-20001, 'Get Next Sequence Failed.. '||sqlcode||' '||sqlerrm);
end;
C#
using System;
using System.Net;
using System.Data;
using System.Net.Http;
using System.Web.Http;
using System.Linq;
using System.Web.Http.Description;
using System.Collections.Generic;
using Oracle.ManagedDataAccess.Client;
using FooBarApi.Models;
// POST: api/Location
[HttpPost]
[ResponseType(typeof(LOCATION))]
[Route("", Name = "AddLocation")]
public HttpResponseMessage AddLocation([FromBody]LOCATION Location)
{
// Access config file and connect to database
OracleConnection conn = new OracleConnection("User Id=FOO_BAR; Password=foo_bar; Data Source=FOOBARTEST");
// Setup call to stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "PROC_NEXT_SEQUENCE";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// Assign parameters
cmd.Parameters.Add("p_owner", OracleDbType.Varchar2);
cmd.Parameters.Add("p_table", OracleDbType.Varchar2);
cmd.Parameters.Add("p_seq_name", OracleDbType.Varchar2);
cmd.Parameters.Add("p_seq_value", OracleDbType.Decimal, 4000).Direction = ParameterDirection.Output;
// Execute stored procedure
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
db.LOCATIONS.Add(Location);
db.SaveChanges();
var response = Request.CreateResponse<LOCATION>(HttpStatusCode.Created, Location);
string uri = Url.Link("GetLocations", new { LOSEQ = Location.LOSEQ });
response.Headers.Location = new Uri(uri);
return response;
LocationModel.Context.cs
using System.Linq;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Core.Objects;
public partial class LocationEntities : DbContext
{
public LocationEntities()
: base("name=LocationEntities")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
public virtual DbSet<LOCATION> LOCATIONS { get; set; }
public virtual Decimal PROC_NEXT_SEQUENCE(string p_OWNER, string p_TABLE, string p_SEQ_NAME, ObjectParameter p_SEQ_VALUE)
{
var p_OWNERParameter = p_OWNER != null ?
new ObjectParameter("P_OWNER", p_OWNER) :
new ObjectParameter("P_OWNER", typeof(string));
var p_TABLEParameter = p_TABLE != null ?
new ObjectParameter("P_TABLE", p_TABLE) :
new ObjectParameter("P_TABLE", typeof(string));
var p_SEQ_NAMEParameter = p_SEQ_NAME != null ?
new ObjectParameter("P_SEQ_NAME", p_SEQ_NAME) :
new ObjectParameter("P_SEQ_NAME", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("PROC_NEXT_SEQUENCE", p_OWNERParameter, p_TABLEParameter, p_SEQ_NAMEParameter, p_SEQ_VALUE);
}
}
}
Thank you.
**Update**
I'm adding the Error Message/Stack Trace in case this helps.
"Message": "An error has occurred.",
"ExceptionMessage": "ORA-20001: Get Next Sequence Failed.. -936 ORA-00936: missing expression\nORA-06512: at \"FOO_BAR.PROC_NEXT_SEQUENCE\", line 19\nORA-06512: at line 1",
"ExceptionType": "Oracle.ManagedDataAccess.Client.OracleException",
"StackTrace": "
at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
at BedsPersonnelBase.Controllers.FlocationController.PostLocation(LOCATION Location) in c:\\mvcApps\\FooBarApi\\ FooBarApi\\Controllers\\LocationController.cs:line 71
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()"
}
The owner/role with privileges to use the procedure calling the SEQUENCE object must have SELECT privileges on the SEQUENCE OBJECT, just like a TABLE or VIEW.
If this named sequence object does not exist, ||p_table||'_seq.nextval then nextval will fail.
If p_table = let's say the table PEOPLE then PEOPLE_SEQ must exist as a SEQUENCE OBJECT (not column name) in the same owner schema for nextval call to work.
None
0 Points
2 Posts
ORA 00936 error calling a PL/SQL stored procedure from a c# web service
Aug 19, 2016 04:51 PM|julian_hullis|LINK
I need some help calling a stored procedure designed to add sequence number to each row added by an Api POST method.
At the moment I get this error message: "ExceptionMessage": "ORA-20001: Get Next Sequence Failed.. -936 ORA-00936: missing expression\nORA-06512: at \"FOO_BAR.PROC_NEXT_SEQUENCE\", line 19\nORA-06512: at line 1"
I have tried numerous code changes suggested in questions on here and elsewhere but due to my lack of c#/Oracle specific knowledge I'm beginning to feel as if I'm going round in circles.
The procedure was tested successfully in SQL Developer.
I’m using VS2013 (update 5), Entity Framework v6.1.3, ODPnet via Nuget, Oracle 11.2 and Postman.
C#
using System; using System.Net; using System.Data; using System.Net.Http; using System.Web.Http; using System.Linq; using System.Web.Http.Description; using System.Collections.Generic; using Oracle.ManagedDataAccess.Client; using FooBarApi.Models; // POST: api/Location [HttpPost] [ResponseType(typeof(LOCATION))] [Route("", Name = "AddLocation")] public HttpResponseMessage AddLocation([FromBody]LOCATION Location) { // Access config file and connect to database OracleConnection conn = new OracleConnection("User Id=FOO_BAR; Password=foo_bar; Data Source=FOOBARTEST"); // Setup call to stored procedure OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "PROC_NEXT_SEQUENCE"; cmd.CommandType = System.Data.CommandType.StoredProcedure; // Assign parameters cmd.Parameters.Add("p_owner", OracleDbType.Varchar2); cmd.Parameters.Add("p_table", OracleDbType.Varchar2); cmd.Parameters.Add("p_seq_name", OracleDbType.Varchar2); cmd.Parameters.Add("p_seq_value", OracleDbType.Decimal, 4000).Direction = ParameterDirection.Output; // Execute stored procedure conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); db.LOCATIONS.Add(Location); db.SaveChanges(); var response = Request.CreateResponse<LOCATION>(HttpStatusCode.Created, Location); string uri = Url.Link("GetLocations", new { LOSEQ = Location.LOSEQ }); response.Headers.Location = new Uri(uri); return response;LocationModel.Context.cs
using System.Linq; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Data.Entity.Core.Objects; public partial class LocationEntities : DbContext { public LocationEntities() : base("name=LocationEntities") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { throw new UnintentionalCodeFirstException(); } public virtual DbSet<LOCATION> LOCATIONS { get; set; } public virtual Decimal PROC_NEXT_SEQUENCE(string p_OWNER, string p_TABLE, string p_SEQ_NAME, ObjectParameter p_SEQ_VALUE) { var p_OWNERParameter = p_OWNER != null ? new ObjectParameter("P_OWNER", p_OWNER) : new ObjectParameter("P_OWNER", typeof(string)); var p_TABLEParameter = p_TABLE != null ? new ObjectParameter("P_TABLE", p_TABLE) : new ObjectParameter("P_TABLE", typeof(string)); var p_SEQ_NAMEParameter = p_SEQ_NAME != null ? new ObjectParameter("P_SEQ_NAME", p_SEQ_NAME) : new ObjectParameter("P_SEQ_NAME", typeof(string)); return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("PROC_NEXT_SEQUENCE", p_OWNERParameter, p_TABLEParameter,p_SEQ_NAMEParameter, p_SEQ_VALUE); } } }
Thank you.
**Update**
I'm adding the Error Message/Stack Trace in case this helps.
Contributor
3412 Points
1327 Posts
Re: ORA 00936 error calling a PL/SQL stored procedure from a c# web service
Aug 20, 2016 12:48 AM|Lannie|LINK
When using Oracle NUMBER data type
I have better luck using
OracleDbType.Decimal
instead of
OracleDbType.Int32
Contributor
3412 Points
1327 Posts
Re: ORA 00936 error calling a PL/SQL stored procedure from a c# web service
Aug 20, 2016 01:27 AM|Lannie|LINK
Also I routinely force column alias when using DUAL like
'select '||p_owner||'.'||p_seq_name||'.nextval AS NV from dual';
None
0 Points
2 Posts
Re: ORA 00936 error calling a PL/SQL stored procedure from a c# web service
Aug 22, 2016 03:17 PM|julian_hullis|LINK
Lannie, thank you for your help but unfortunately the error is still there.
Contributor
3412 Points
1327 Posts
Re: ORA 00936 error calling a PL/SQL stored procedure from a c# web service
Aug 22, 2016 10:17 PM|Lannie|LINK
The owner/role with privileges to use the procedure calling the SEQUENCE object must have SELECT privileges on the SEQUENCE OBJECT, just like a TABLE or VIEW.
Contributor
3412 Points
1327 Posts
Re: ORA 00936 error calling a PL/SQL stored procedure from a c# web service
Aug 25, 2016 11:14 PM|Lannie|LINK
Try
Clear parameters
Oracle.DbType.Number
smaller SIZE parameter like 12 instead of 4000
// Assign parameters
cmd.Parameters.Clear;
cmd.Parameters.Add("p_owner", OracleDbType.Varchar2);
cmd.Parameters.Add("p_table", OracleDbType.Varchar2);
cmd.Parameters.Add("p_seq_name", OracleDbType.Varchar2);
cmd.Parameters.Add("p_seq_value", OracleDbType.Number, 12).Direction = ParameterDirection.Output;
None
0 Points
1 Post
Re: ORA 00936 error calling a PL/SQL stored procedure from a c# web service
Oct 04, 2016 05:21 AM|bankexams|LINK
Thx Lannie, its works but again it showing the same can u plz tell me why it is again showing ora-error...
Bank Exams
Contributor
3412 Points
1327 Posts
Re: ORA 00936 error calling a PL/SQL stored procedure from a c# web service
Oct 04, 2016 11:49 PM|Lannie|LINK
If this named sequence object does not exist, ||p_table||'_seq.nextval then nextval will fail.
If p_table = let's say the table PEOPLE then PEOPLE_SEQ must exist as a SEQUENCE OBJECT (not column name) in the same owner schema for nextval call to work.
Schemas own database objects
TABLES
VIEWS
INDEXES
SEQUENCES
and other things