ORA 00936 error calling a PL/SQL stored procedure from a c# web service

Question

User-931116031 posted

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()"
}

Answer 1

User269602965 posted

When using Oracle NUMBER data type

I have better luck using

OracleDbType.Decimal

instead of

OracleDbType.Int32

Answer 2

User269602965 posted

Also I routinely force column alias when using DUAL like

'select '||p_owner||'.'||p_seq_name||'.nextval AS NV from dual';

Answer 3

User-931116031 posted

Lannie, thank you for your help but unfortunately the error is still there.

Answer 4

User269602965 posted

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.

Answer 5

User269602965 posted

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;

Answer 6

User-1184805932 posted

Thx Lannie, its works but again it showing the same can u plz tell me why it is again showing ora-error...

Bank Exams

Answer 7

User269602965 posted

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