Click here to Skip to main content
15,879,535 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I need some help calling a stored procedure designed to add sequence number to each row added via a web service 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"

The procedure was tested successfully in SQL Developer.

I’m using VS2013 (update 5), .NET 4, Entity Framework v6.1.3, ODP.net via Nuget, Oracle 11.2 and Postman.

Stored procedure
SQL
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#
SQL
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;}


Thank you.

What I have tried:

I have tried numerous code changes suggested on various developer forums but due to my lack of c#/Oracle specific knowledge I'm beginning to feel as if I'm going round in circles.
Posted
Comments
Bernhard Hiller 24-Aug-16 4:28am    
cmd.Parameters.Add("p_owner", OracleDbType.Varchar2); - here you define the parameter. Where in your C# code does it get a value?
Julian_h 25-Aug-16 9:57am    
Thank you for your response Bernhard.

There are no declared values in the c# and I’m aware now that is at least part of where my problem lies. I’m very new to this type of dv and my learning curve has had to be a steep one. I have attempted to add the values - cmd.Parameters.Add("p_table", OracleDbType.Varchar2).Value = LOCATION - but received the ‘does not exist in current context’ message and I was trying to work out how to reference those values in the code, unsuccessfully.

The latest position is that I have built a c# console app containing the code to call the procedure and hard coded the parameters in that procedure to match a specific db/table. This runs successfully and displays the expected sequence number in a command console. So the mapping of the parameters is one problem and I believe my attempt at integrating the code to execute the procedure is the other. I’ve been lead to believe that the code to execute the procedure should be separated out into it’s own class, as per MVC best practice. I feel that I’making a degree of progress.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900