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 fla开发者_如何学Gog param out then i would use db.ExecuteNonQuery(..) & if only cursor out then i would use db.ExecuteReader(..)

---

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

---

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

---

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

}

---

I don't know which library you use for Oracle access... but usually it is possible to declare the cursor out and the param out both as Parameters and use ExecuteNonQuery with an anoynmous PL/SQL-block (in which you call the Stored Procedure)... for example with the Devart dotconnect components this is possible... (not affilliated, just a happy customer)

---

One could consider an alternative to the repeated query in your procedure. For example:

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;

In this way you don't have to query the table twice, you have the count in each row of your resultset. You can get rid of your parameters dealing with the max rowcount as well, and check the count value in your calling routine by fetching one row.

Just an alternative thought...