kodcu kodcu - 1 month ago 25
C# Question

Asp.Net MVC Execute Oracle PL/SQL Stored Procedure With Patrameters

I Want to execute oracle pl/sql stored procedure with parameters in asp.net MVC code first.

Here is My Stored Procedure:

CREATE OR REPLACE
PROCEDURE sp_getuserhosps(p_city IN VARCHAR2,cnt OUT DECIMAL)
AS
BEGIN

SELECT COUNT(1) INTO cnt FROM "PersonStatus" p1
INNER JOIN HOSPITALS p2 ON P1."HospitalCode"=p2.KURUM_KODU
WHERE P2.IL_KODU=p_city AND p1."Statu"=1;

END;


This returns DBMS OUTPUT
CNT=135555
like this.

I tried Asp.Net MVC Code Like This:

var sql = "DECLARE cnt DECIMAL;BEGIN SP_GETUSERHOSPS(@p_city,cnt);END;";
var total = context.Database.ExecuteSqlCommand(sql, new OdbcParameter("@p_city","07"));


But there is no result from this.And returning as error like this:


Cannot cast 'System.Data.Odbc.OdbcParameter' to 'Oracle.ManagedDataAccess.Client.OracleParameter'


How Can I Solve This?

Thanks

Answer

As the error said you need to pass an instance of Oracle.ManagedDataAccess.Client.OracleParameter instead of System.Data.Odbc.OdbcParameter.

So you'll have this:

var sql = "DECLARE cnt DECIMAL;BEGIN SP_GETUSERHOSPS(:p_city,cnt);END;";
var total = context.Database.ExecuteSqlCommand(sql, new OracleParameter(":p_city","07"));

Notice I replace '@' char with ':' char. because parameter in Oracle are prefixed with the latter while '@' is use for SQL Server.

If you want to get the cnt value then rewrite your code like this:

var cntParameter = new OracleParameter("cnt", OracleDbType.Number, ParameterDirection.Output);
var cityParameter = new OracleParameter("p_city", OracleDbType.Number,  "07", ParameterDirection.Input);
var sql = "BEGIN SP_GETUSERHOSPS(:p_city,:cnt);END;";
var total = context.Database.ExecuteSqlCommand(sql, cityParameter, cntParameter );
// Here you check the cntParameter.Value to get cnt parameter value.