How to Execute an Oracle stored procedure that returns LOB parameter using Entity Framework Core in .Net Core 3.1 with C#

Problem:

How to Execute an Oracle stored procedure that returns LOB parameter using Entity Framework Core in .Net Core with C#.

Solution:

Executing a simple stored procedure without LOB value is not a headache in Entity Framework, but if you try to execute a procedure that returns an LOB parameter, it may be a headache for you, because using any other method like FromSQLRaw, etc, procedure is executed but the Oracle connection closes before fetching LOB value. Here is the sample code to execute a stored procedure, lets say 'MY_PROC' that returns a CLOB parameter.

 private void ExecuteProcedure(IDbConnection dbConnection, Document d, DocumentDetail di, bool IsApproved, long? AgentKey, out string Status,out string Error)

        {

            try

            {

                Error = "";

                OracleParameter p1 = new OracleParameter("LOT_TYPE", OracleDbType.Varchar2, (d.Category == LotCategory.IMPORT) ? "BL" : "CRN", System.Data.ParameterDirection.Input );

                OracleParameter p2 = new OracleParameter("LOT_ID", OracleDbType.Varchar2, (d.Category == LotCategory.IMPORT) ? d.BLNo : d.CRNNo, System.Data.ParameterDirection.Input);

                OracleParameter p3 = new OracleParameter("ERRR", OracleDbType.Clob, System.Data.ParameterDirection.Output);

                OracleParameter p4 = new OracleParameter("STATUS", OracleDbType.Varchar2, System.Data.ParameterDirection.Output);

                OracleParameter p5 = new OracleParameter("DOC_ID", OracleDbType.Varchar2, di.BillingDocId,System.Data.ParameterDirection.Input);

                OracleParameter p6 = new OracleParameter("pIS_APPROVED", OracleDbType.Int16, (IsApproved)? 1:0, System.Data.ParameterDirection.Input);

                OracleParameter p7 = new OracleParameter("pVALIDITY", OracleDbType.Date, di.Validity, System.Data.ParameterDirection.Input);

                OracleParameter p8 = new OracleParameter("pAGENT_KEY", OracleDbType.Int64, AgentKey, System.Data.ParameterDirection.Input);

                OracleParameter p9 = new OracleParameter("pCREATOR", OracleDbType.Varchar2, HttpContext.ConnectedUser(), System.Data.ParameterDirection.Input);

                p3.Size = 5000;

                p4.Size = 50;

                

                var CON = _context.Database.GetDbConnection();

                {

                    con.Open();

                    var CMD = new OracleCommand();

                    CMD.Connection = (OracleConnection)CON;

                    CMD.CommandText = "MY_PROC";

                    CMD.CommandType = CommandType.StoredProcedure;

                    CMD.Parameters.Add(p1);

                    CMD.Parameters.Add(p2);

                    CMD.Parameters.Add(p3);

                    CMD.Parameters.Add(p4);

                    CMD.Parameters.Add(p5);

                    CMD.Parameters.Add(p6);

                    CMD.Parameters.Add(p7);

                    CMD.Parameters.Add(p8);

                    CMD.Parameters.Add(p9);

                    

                    CMD.ExecuteNonQuery();

                    Status = ((Oracle.ManagedDataAccess.Types.OracleString)p4.Value).Value;

                    if (!((Oracle.ManagedDataAccess.Types.OracleClob)p3.Value).IsNull)

                    {

                        Error = ((Oracle.ManagedDataAccess.Types.OracleClob)p3.Value).Value;

                    }

                     con.Close();

                };

            

               

                                

            }

            catch (Exception x)

            {

                log.Error("Exception : " + x);

                Status = "Error";

                Error = x.Message;

            }

        }