From Stored Procedure, return OUT parameter & OUT cursor & parse result (Oracle)

Question

Question : Is it possible to return using OUT :

Both : A variable & A cursor, from my code below ??

---

I saw a similar question for SqlDB but after a really long search found no solution for OracleDB.

In PLSQL :

CREATE OR REPLACE
PROCEDURE SPGETRESULTANDSETFLAG
(
 pFilter VARCHAR2,
 pMaxRowCount VARCHAR2,
 pTableID RAW,
 myFlag OUT NUMBER,
 myCursor OUT types.cursorType
)
AS
BEGIN
 Declare
  CountQuery VARCHAR(20000) := '';
  DataQuery VARCHAR(20000) := '';
  ResultingRows NUMBER := -1;
 Begin
  myFlag := -1;

  CountQuery := 'SELECT COUNT(*) FROM ' 
                || F_GET_TABLENAME_FROM_ID(PTABLEID => pTableID)
                || ' WHERE ' || pFilter;
  EXECUTE IMMEDIATE CountQuery INTO ResultingRows;


  --Get the Return Value
  if( pMaxRowCount > ResultingRows ) then myFlag := 1; end if;


  DataQuery := 'SELECT * FROM '
                || F_GET_TABLENAME_FROM_ID(PTABLEID => pTableID) 
                || ' WHERE ' || pFilter; 
  --Get the Return Cursor
  Open myCursor for DataQuery;

 End;
END SPGETRESULTANDSETFLAG;

---

In Code Behind..

Database db = DBSingleton.GetInstance();
using (DbCommand command = db.GetStoredProcCommand(spName))
{
    //The three Add In Parameters... & then the Add out Parameter as below
    db.AddOutParameter(command, "myFlag", System.Data.DbType.Int32, LocVariable );
    using ( IDataReader reader = db.ExecuteReader(command))
    {
         //Loop through cursor values & store them in code behind class-obj(s)
    }
}

---

I Thought this was not possible as how do I read both the value & the cursor, because..

if only flag param out then i would use db.ExecuteNonQuery(..) & if only cursor out then i would use db.ExecuteReader(..)

Solution 1

Thank you for the answers

I was really desperate to get a working result & somehow came across a solution & after reading a bit found out why it worked :

---

Oracle Stored Procedure as is with no change.

---

Code Behind - Changed as follows :

Database db = DBSingleton.GetInstance();
using (DbCommand command = db.GetStoredProcCommand(spName))
{
    //The three Add In Parameters... & then the Add out Parameter as below
    db.AddOutParameter(command, "myFlag", System.Data.DbType.Int32, LocVariable );
    using ( IDataReader reader = db.ExecuteReader(command))
    {
         //Loop through cursor values & store them in code behind class-obj(s)
         //The reader must be closed before trying to get the "OUT parameter"
         reader.Close();

         //Only after reader is closed will any parameter result be assigned
         //So now we can get the parameter value.
         //if reader was not closed then OUT parameter value will remain null
         //Getting the parameter must be done within this code block
         //I could not get it to work outside this code block
         <Type> result = (typecast)command.Parameters["OUT_parameter_name"];
    }
}
//I USED THIS APPROACH TO RETURN MULTIPLE PARAMETERS ALONG WITH THE CURSOR READ

Solution 2

Yes, it is possible to have more than one out parameter. Here's an example that I use to call an Oracle stored procedure in c#:

OracleParameter op = null;
OracleDataReader dr = null;

/* custom code here. Yours would look a little different */
OracleCommand cmd = (OracleCommand) this.FactoryCache.Connection.CreateCommand();

cmd.CommandText = "pkg_prov_index.getNextPanel";
cmd.CommandType = CommandType.StoredProcedure;

op = new OracleParameter("pCurrentPanelId", OracleType.VarChar);
op.Direction = ParameterDirection.Input;
op.Value = masterProviderIndex.CurrentPanelId;
cmd.Parameters.Add(op);

op = new OracleParameter("pRefCursor", OracleType.Cursor);
op.Direction = ParameterDirection.Output;
cmd.Parameters.Add(op);

op = new OracleParameter("pReturnCode", OracleType.Number);
op.Direction = ParameterDirection.Output;
op.Size = 5;
cmd.Parameters.Add(op);

op = new OracleParameter("pReturnMessage", OracleType.VarChar);
op.Direction = ParameterDirection.Output;
op.Size = 4000;
cmd.Parameters.Add(op);

cmd.ExecuteNonQuery();

returnCode = Convert.ToInt16(cmd.Parameters[2].Value);
returnMessage = cmd.Parameters[3].Value.ToString();

dr = (OracleDataReader) cmd.Parameters[1].Value;

while (dr.Read()) {
}

Solution 3

            using (myCmd)
            {
                myCmd.Parameters.AddWithValue("p_session_id", sessionId);
                myCmd.Parameters.AddWithValue("p_user", SessionHelper.UserEmailID);

                OracleParameter retval = new OracleParameter("p_status", OracleType.NVarChar, 35);
                retval.Direction = ParameterDirection.Output;
                myCmd.Parameters.Add(retval);


                OracleParameter retval2 = new OracleParameter("p_status_dtl", OracleType.NVarChar, 300);
                retval2.Direction = ParameterDirection.Output;
                myCmd.Parameters.Add(retval2);

                OracleParameter retval3 = new OracleParameter("p_output", OracleType.Cursor);
                retval3.Direction = ParameterDirection.Output;
                myCmd.Parameters.Add(retval3);
                myCmd.ExecuteNonQuery();
                status = myCmd.Parameters["p_status"].Value.ToString();
                statusDetail = myCmd.Parameters["p_status_dtl"].Value.ToString();

                using (OracleDataReader reader = (OracleDataReader)myCmd.Parameters["p_output"].Value)
                {
                    outPutDt.Load(reader);
                }
            }

}

Comments

[Sunny]

@yahia, thank you for your reply, i am using the Microsoft Practices Enterprise Library

[Yahia]

sorry - never used that... I could be mistaken but from what I see in the documentation what you want is not possible...

[Sunny]

oh well, i guess i got to find some other way, i'm right now trying even a double cursor out ?? .. once again thank you for your time

[Sunny]

thank you for your suggestion, I didn't know of the OVER() function at all earlier. I'm sorry for not having mentioned details about the dynamic table, it has a minimum of over 20~30 k rows... & around 3-4 columns, hence adding that many redundant Tot_row_counts (filter is rarely applicable) may not be entirely practical as this procedure is invoked simultaneously by multiple user's accessing the same table space.

[Sunny]

I had used the same code as you have written above, the issue was that i was not able to access the OUT value parameter, even though i could get the OUT cursor data. But i eventually found out that the ExecuteReader() would also pass the OUT but only @ the end of an entire cursor-result read() operation. Please see the code I used to get it to work. Thanks a lot for taking the effort to answer, I am a newbie to this community, do forgive me for not having been clear with my question.

[Sunny]

Although I did not come across the need to yet perform this, I am really curious to know : Is it possible to Add More Than One Cursor Out & (not a REF Cursor) , If so how would one go about reading it in code behind using the Microsoft Practices Enterprise Library DataReader Class, If anyone were to come up with an answer then I will edit the question to reflect the same.

[Tanavirrul haq]

you can use return multiple value from Proceedure