How to get the rowid back from an ExecuteOracleNonQuery in C#

Question

I'm using an ExecuteOracleNonQuery in C# to INSERT a record into my Oracle database using a stored procedure but can't seem to get the ROWID to return.

In C# ...

using (OracleConnection oc= 
    new OracleConnection(AppConfiguration.ConnectionString))
{
    OracleCommand myCommand = new OracleCommand("PKG_TEST.INSERT", oc);
    myCommand.CommandType = CommandType.StoredProcedure;
    myCommand.Parameters.AddWithValue("p_Id", allAssets.Id);
    myCommand.Parameters.AddWithValue("p_Description", allAssets.Description);
    OracleString rowId;
    try
    {
        myConnection.Open();
        result = myCommand.ExecuteOracleNonQuery(out rowId);
        allAssets.RowId = rowId.ToString();
    }
    catch(System.Exception ex)
    {
        Console.WriteLine(ex.StackTrace);
    }
    finally
    {
        myConnection.Close();
    }
}
return result;

Procedure in Oracle package ...

PROCEDURE insert (
  p_id               IN   ALL_ASSETS.id%TYPE,
  p_description      IN   ALL_ASSETS.description%TYPE
)
IS
BEGIN
  INSERT INTO ALL_ASSETS
              (id, description)
       VALUES (p_id, p_description);
END insert;

Can anyone shed any light on how the ROWID is returned, please?

EDIT - I've now changed the code to be as above but still no ROWID return. Also, only one record is being inserted.

Thanks.

Answer 1

Hmmm, do you actually need to specify the rowid as an out parameter in your stored procedure?? I thought that ExecuteOracleNonQuery automatically returned it as a c# out variable.

Answer 2

Why would you expect it to return a rowid ? No where in the DB end have you asked it to, and procedure isn't going to return a ROWID and wouldn't know which one to return anyway.

Automatically returning the ROWID of affected rows can work for INSERT/UPDATE/MERGE and maybe DELETE (though I'm not sure of the point in that case).

You can use INSERT INTO ALL_ASSETS (id, description) VALUES (p_id, p_description) RETURNING ROWID INTO v_rowid;

v_rowid would be a variable in the PL/SQL, and you could return it from the database as an additional out parameter, or convert the procedure into a function.