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
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;}
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.