I'm having trouble with OracleParameter, I could not find the problem, because I can not see the sql after the assignment of the parameters ... follow the code:
private DataTable GetSearch()
{
string sql_work = "select w.login , w.data, sum(w.work_time) work_time";
sql_work += " from ergonomia_work w";
sql_work += " where w.login LIKE '%' || nvl(:nome, w.login) || '%'";
sql_work += " and trunc(w.data) = nvl(:dt, w.data)";
sql_work += " group by w.login, w.data";
sql_work += " order by w.login, w.data; ";
string Nome = txt_nome.Text;
string dts = "";
if (pick_dt.Checked)
{
string pick = pick_dt.Text;
DateTime conv_date = Convert.ToDateTime(pick);
dts = conv_date.ToString("dd/MM/yyyy");
}
OracleParameter usuario = new OracleParameter("nome", Nome);
usuario.OracleType = OracleType.VarChar;
OracleParameter work = new OracleParameter("dt", dts);
work.OracleType = OracleType.DateTime;
DataTable dt = new DataTable();
List<OracleParameter> list = new List<OracleParameter>();
list.Add(usuario);
list.Add(work);
Connect con = Connect.getDB;
dt = con.getRegistro(sql_work, list);
return dt;
}
public DataTable getRegistro(string sql, List<OracleParameter> list)
{
DataTable dt = new DataTable();
try
{
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
foreach (var item in list)
{
cmd.Parameters.Add(item);
}
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(dt);
return dt;
}
catch (OracleException ex)
{
throw ex;
}
}
When I run da.Fill, I get the following error:
ORA-00911: invalid character.
Is there any way I can see exactly what SQL is running with the parameters already replaced?