从c# web服务调用PL/SQL存储过程时出现ORA 00936错误

我需要一些帮助来调用一个存储过程，该存储过程被设计为通过Api POST方法添加的每一行添加序列号。

此刻我得到这个错误信息:"ExceptionMessage": "ORA-20001: get Next Sequence Failed.. "-936 ORA-00936:缺少表达式' ora -06512: at '"FOO_BAR. exe "PROC_NEXT_SEQUENCE'"， line 19'nORA-06512: at line 1"

我已经尝试了许多在这里和其他地方的问题中建议的代码更改，但由于我缺乏c#/Oracle的专业知识，我开始觉得我好像在兜圈子。

此程序已在SQL Developer中成功测试。

我使用VS2013(更新5)，实体框架v6.1.3, ODPnet通过Nuget, Oracle 11.2和邮差。

存储过程

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 from dual';
else v_sql := 'select '||p_owner||'.'||p_table||'_seq.nextval 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).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

namespace FooBarApi.Models
{
using System;
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);
    }
  }
}

错误消息

"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 FooBarApi.Controllers.LocationController.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()"
}

更新

我已经更新了我的代码来反映Gary Myers和Aisha发现的错误。我还添加了错误消息/堆栈跟踪，以防这有帮助。

提前感谢。

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 from dual';
else v_sql := 'select '||p_owner||'.'||p_table||'_seq.nextval 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;