CODEWITHSUNDEEP
c#oracle-databasestored-proceduresodp.net
AlexZ
AlexZ

Reputation: 229

How to know if a stored procedure was executed correctly?

I am calling a stored procedure that inserts records in a DB, but I want to know how I can tell that the stored procedure was executed properly? In order to know how many inserts were made, that can be verified from the web?

I add the code that I am using to call the stored procedure:

public void EjecutaSP(string NomSP, ParametroOracle[] Parameter, string OracleCon)
{
    OracleCon = Rijndael.Desencriptar(strOracleCon);
    OracleConnection oracleCon = new OracleConnection(OracleCon);

    try
    {
        OracleCommand cmd = new OracleCommand(NomSP, oracleCon);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("DateProcess", "OracleType.VarChar").Value = "'2019-05-29'";
        cmd.Parameters.Add("EmployeeGroup", "OracleType.Number").Value = 555501;
        cmd.Parameters.Add("IdEmployee", "OracleType.Number").Value = 555501;

        cmd.Parameters.Add("NomEmployee", "OracleType.VarChar").Value = "'Espanol'";

        OracleDataAdapter da = new OracleDataAdapter(cmd);
        DataTable dtTable = new DataTable();

        da.Fill(dtTable);
    }
    catch (Exception ex)
    {
        string strSP = NomSP;
        int intPar = Parameter.Length;

        for (int i = 0; i < intPar; i++)
            strSP += " ," + Parameter[i].Valor;

        Exception _ex = new Exception("Error en SP: " + strSP, ex.InnerException);
        throw _ex;
    }
    finally
    {
        oracleCon.Close();
        oracleCon.Dispose();
    }
}

The process that runs the stored procedure already works, I would just like to know if there is any sentence that helps to know if the Store procedure was executed correctly, as special data the stored procedure is not returning anything.

Upvotes: 0

Views: 1177

Answers (1)

T.S.
T.S.

Reputation: 19340

  1. you need to clean up your question. Are you inserting or selecting data? "I am calling a Store Procedure that inserts records" . . .da.Fill(dtTable); ????

  2. If you select data, your SP must have RefCursor out parameter

create or replace procedure X (pDataout OUT SYS_REFCURSOR)....

And hence you need to add corresponding parameter

cmd.Parameters.Add(pdata, OracleDbType.RefCursor) // NOTE correct usage
  1. To your question... Stored procedure does not return number of records affected, lets say, using ExecuteNonQuery. If you are simply looking to know if SP successful, if you have no errors - it is. If you have no handling of errors in your SP, and something fails, everything will be rolled back. It is all or nothing. You can catch exception in c# and work with it. Avoid handling errors inside SP.
create or replace procedure X (...)
AS
 . .  .
Begin
    insert into . . . 
End;

If you have multiple DML in SP and you would like to know how many rows affected, you need to build your SP for it

create or replace procedure X (pAffected out number)
AS
 . .  .
Begin
    insert into A. . . 
    pAffected := SQL%ROWCOUNT;

    insert into B. . . 
    pAffected := pAffected + SQL%ROWCOUNT;
End;

And in c# just get the value

 cmd.Parameters.Add("pAffected", OracleDbType.Decimal, ParameterDirection.Output)
 . . .  . . . 
 int count = ((OracleDecimal)cmd.Parameters["pAffected"].Value).ToInt32();
  1. Code construction
using (var conn = new OracleConnection(.......))
using (var cmd = new OracleCommand(.......))
{
  // . . . . .  CODE HERE ........   
  using (var adp = new OracleDataAdapter(....) // if you need to fill table
  {
      // fill table
  }
}
// NOTE: no need close/dispose (automatic with using). Transaction committed internally. 
// If SP errors out, everything is rolled back. Wrap this into `Try/Catch(OracleException ex)`

Upvotes: 1

Related Questions