Dear Team,
I have a oracle stored procedure as below. This Oracle stored procedure accepts Input parameter and output parameters.
create or replace NONEDITIONABLE PROCEDURE GetDetailsWithOutputParams_Multiple(id IN INTEGER,valuepath OUT VARCHAR2,valueNumeric OUT NUMBER)
AS
BEGIN
for c in (SELECT k.VALUEPATH as VALUEPATH,k.VALUENUMERIC as VALUENUMERIC INTO valuepath,valueNumeric FROM KPI k where k.ID=id)
loop
valuepath:=c.VALUEPATH;
valueNumeric:=c.VALUENUMERIC;
end loop;
END;
Now I am calling this procedure using C#.net as below:
using (OracleConnection conn = new OracleConnection(_connectionString))
{
await conn.OpenAsync();
string str = @"DECLARE ID NUMBER;VALUEPATH VARCHAR2(200);VALUENUMERIC NUMBER;BEGIN GETDETAILSWITHOUTPUTPARAMS_MULTIPLE(ID, VALUEPATH, VALUENUMERIC); :VALUEPATH := VALUEPATH; :VALUENUMERIC := VALUENUMERIC;END;";
OracleCommand objCmd = new OracleCommand(str, conn);
objCmd.CommandType = CommandType.Text;
OracleParameter parameter = null;
parameter = new OracleParameter("ID", OracleDbType.Decimal, ParameterDirection.Input);
parameter.Value = 2;
objCmd.Parameters.Add(parameter);
parameter = new OracleParameter("VALUEPATH", OracleDbType.Varchar2, ParameterDirection.Output);
parameter.Size = 200;
objCmd.Parameters.Add(parameter);
parameter = new OracleParameter("VALUENUMERIC", OracleDbType.Decimal, ParameterDirection.Output);
objCmd.Parameters.Add(parameter);
string str1, str2;
using (OracleDataReader objReader = objCmd.ExecuteReader())
{
str1 = objCmd.Parameters["VALUEPATH"].Value.ToString();
str2 = objCmd.Parameters["VALUENUMERIC"].Value.ToString();
objReader.Close();
}
}
Getting Error as below:
Please advise, how to execute oracle stored procedure as a command text .