c# - ORA-00936 : missing expression when filling dataset to OracleDataAdapter

Question

我正在为 MySQL、SQL Server 和 Oracle 使用这种代码。对于 MySQL 和 SQL Server，它工作得很好。对于 Oracle，在执行 adapter.Fill(dataset); 时抛出“ORA-00936”异常。有人知道为什么会这样吗？

错误信息是

ORA-00936: missing expression when filling dataset to OracleDataAdapter

我的 SQL 查询是:

sqlQuery: "SELECT * FROM \"DWSYS\" WHERE NOT FCVERSION=@Version"

C#代码是:

public class OutputOracleQuery : IWriteSQLQuery
{
    private static readonly ILogger logger = LoggerFactory.Create(typeof(OutputOracleQuery));
    HelperEnvironmentVariable GetEnvironmentVariable = new HelperEnvironmentVariable();

    public DataSet GetData(string DBUser, string DBUserPassword, string sqlQuery, string databaseName, Version version)
    {
        using (OracleConnection connection = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + GetEnvironmentVariable.MachineName + ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))); User Id=" + DBUser + ";Password=" + DBUserPassword + ";"))
        {
            try
            {
                using (var command = new OracleCommand(sqlQuery, connection))
                {
                    connection.Open();
                    command.Parameters.Add("@Version", version.ToString());

                    OracleDataAdapter adapter = new OracleDataAdapter(command);                     
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);

                    return dataset;
                }
            }
            catch (OracleException ex)
            {
                logger.Debug("Demanded Database action for the database " + "\"" + databaseName + "\"" + " could not be executed", ex);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

Answer 1

根据 OracleDataAdapter ，您可以使用以下代码。

在你的 SqlQuery 而不是 @ 中，替换为 :

所以 SqlQuery 将是

SELECT * FROM \"DWSYS\" WHERE NOT FCVERSION= :Version

并且参数中不需要@

 command.Parameters.Add("@Version", version.ToString());