From the stored procedure, return the OUT parameter & OUT cursor and the result of the parsing (Oracle)

Question

Question: Can I use OUT:

Both: variable and cursor A, from my code below <

---

I saw a similar question for SqlDB, but after a very long search, no solution was found 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 the code below ..

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, since I am reading both the value and the cursor, because ..

, if only the param out then flag , I would use db.ExecuteNonQuery (..) & amp; if only the cursor, then , I would use db.ExecuteReader (..)

Answer 1

thanks for answers

- , , :

---

Oracle .

---

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)
         //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

Answer 2

, . , Oracle #:

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()) {
}

Answer 3

, Oracle... , ExecuteNonQuery PL/SQL- ( )... , dotconnect dotart ... ( , )

Answer 4

            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);
                }
            }

}

Answer 5

You can consider an alternative to repeated request in your procedure. For instance:

CREATE OR REPLACE
PROCEDURE SPGETRESULTANDSETFLAG
(
 pFilter VARCHAR2,
 pTableID RAW,
 myCursor OUT types.cursorType
)
AS
  DataQuery VARCHAR(20000) := '';
BEGIN
  DataQuery := 'SELECT COUNT(*) OVER () AS TheCount, T.* FROM '
                || F_GET_TABLENAME_FROM_ID(PTABLEID => pTableID) 
                || ' AS T WHERE ' || pFilter; 
  --Get the Return Cursor
  Open myCursor for DataQuery;

END SPGETRESULTANDSETFLAG;

Thus, you do not need to query the table twice, you have an account in each row of your result set. You can also get rid of your parameters related to the maximum number of rows and check the count value in your calling procedure by extracting one row.

Just an alternative thought ...