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

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

    Friday, August 19, 2016 4:51 PM

All replies

  • User269602965 posted

    When using Oracle NUMBER data type

    I have better luck using

    OracleDbType.Decimal

    instead of

    OracleDbType.Int32

    Saturday, August 20, 2016 12:48 AM
  • User269602965 posted

    Also I routinely force column alias when using DUAL like

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

    Saturday, August 20, 2016 1:27 AM
  • User-931116031 posted

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

    Monday, August 22, 2016 3:17 PM
  • 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.

    Monday, August 22, 2016 10:17 PM
  • 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;

    Thursday, August 25, 2016 11:14 PM
  • 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

    Tuesday, October 4, 2016 5:21 AM
  • 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

    Tuesday, October 4, 2016 11:49 PM