How to read the Oracel stored procedure Output parameter type of "Varchar2(50) data using the data adapter?

Question

Hello,

I have two oracle stored procedures as below:

1st stored procedure

create or replace NONEDITIONABLE PROCEDURE PROCEDURE1(User_Name1 in varchar2,User_Id1 OUT NUMBER)
 AS
 BEGIN
 select user_id into User_Id1 from EMP_CONTACT_INFO where user_name=User_Name1 and ROWNUM=1;
 END;

2nd Stored Procedure

create or replace NONEDITIONABLE PROCEDURE       GetListOfEmp (
    Emp OUT SYS_REFCURSOR
)
AS
Begin
Open Emp For
SELECT ID, Name  FROM EMP where ROWNUM<=5;
End;

Now I am looking for generic c#.net code snippet, which read the data from either of the stored procedure based on the calling of the stored procedure from the UI.

I have done the following code

using (OracleConnection conn = new OracleConnection(_connectionString))
            {
                await conn.OpenAsync();
                OracleCommand objCmd = new OracleCommand(sqlQuery, conn);
                objCmd.CommandType = CommandType.StoredProcedure;

                dynamic lsitofParams = JsonConvert.DeserializeObject(jsonParamData);


                if (lsitofParams.Count > 0 && lsitofParams[0] != null)
                {
                    for (int i = 0; i < lsitofParams.Count; i++)
                    {
                        OracleDbType dbType = GetDataTypeByName(lsitofParams[i]["DataType"].ToString().ToLower());
                        if (lsitofParams[i]["Direction"].ToString().ToLower() == "input")
                            objCmd.Parameters.Add(lsitofParams[i]["Parameter"].ToString(), dbType, Convert.ToInt32(lsitofParams[i]["Size"].ToString()), lsitofParams[i]["ParamValue"].ToString(), ParameterDirection.Input);
                        else
                        {
                            objCmd.Parameters.Add(lsitofParams[i]["Parameter"].ToString(), dbType, ParameterDirection.Output);
                        }
                    }
                    using (OracleDataReader objReader = objCmd.ExecuteReader())
                    {
                       for (int i = 0; i < objReader.FieldCount; i++)
                        {
                            System.Console.Write("{0}\t", objReader.GetName(i));
                        }
                        System.Console.Write("\n");
                        while (objReader.Read())
                        {
                            for (int i = 0; i < objReader.FieldCount; i++)
                            {
                                System.Console.Write("{0}\t", objReader[i].ToString());
                            }
                            System.Console.Write("\n");
                        }

                    }
              }
      }

But this works fine with 2nd stored procedure. But not for the 1st stored procedure.

Answer 1

What exactly you're using to connect to your Oracle database? I did a quick Google Search and found this sample (in VB.NET) which may be of some help

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-from-multiple-ref-cursors

This may be a better solution
https://stackoverflow.com/questions/49480207/issue-with-oracle-stored-procedure-returning-sys-refcursor-and-entity-framework