ORA-00911: invalid Character

Question

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 running da. Fill I get the following error:

ORA-00911: invalid Character.

Is there any way I can see exactly which SQL is running with the parameters already replaced?

Answer 1

According to what’s written here(GUJ) the problem is probably the semicolon at the end of the sql.

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; "; // <--- Você deve retirar ";"