Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to execute Oracle stored procedure which has Input and Output parameter as CommandText in C#.net

User_U2UBUMay 30 2022

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:
image.pngPlease advise, how to execute oracle stored procedure as a command text .

Comments

Post Details

Added on May 30 2022
3 comments
13,048 views