I need to call two system stored procedures in an Oracle database to refresh materialised views when a C# API executes a related process. My code attempts to do so get the following error:
ORA-06512: at "SYS.DBMS_UTILITY", line 236
ORA-06512: at "SYS.DBMS_UTILITY", line 256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3016
ORA-06512: at line 1
My last attempt was this:
string command= "dbms_snapshot.refresh";
string mv1 = "MV_00";
string mv2 = "MV_01";
string parameter = String.Concat(mv1, ", ", mv2);
//we pull the connection here from an EF datamodel
using (var cnx = (OracleConnection)new DataModel().Database.Connection)
using (var cmd = new OracleCommand(command, cnx))
{
cmd.Parameters.Add(parameter, OracleDbType.Varchar2);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
await cnx.OpenAsync();
await cmd.ExecuteNonQueryAsync();
cnx.Close();
}
The error text in c# indicates something is wrong with the parameter value. What am I doing wrong here? I understand how parameters work, that is not the question, simply what is wrong with my comma separated list that would throw the error back up the stack.
