Oracle客户端是否需要在列名前后加上括号? [英] Oracle client requires parentheses around column names?

问题描述

最近有人要求我将MSSQL数据库迁移到Oracle数据库.

I was recently asked to migrate our MSSQL database to an Oracle one.

我使用的是传统方法来执行sql查询.

I'm using the old-traditional way to execute sql queries.

出于某种原因(我不知道)，Oracle要求我在列名两边加上括号(为什么?) 有解决方法吗?

for some reason, unknown to me, Oracle requires me to put parentheses around column names (why?) Is there a workaround for this?

以下代码由于括号(在MSSQL下正常工作)而将失败

The following code will fail because of the parentheses (used to work well under MSSQL)

using (var msq = new OracleConnection(sConnectionString))
{
    msq.Open();
    OracleCommand msc = msq.CreateCommand();
    msc.CommandText = @"SELECT level_1,element_id FROM tnuot_menu_tree 
                       WHERE level_1 IN 
                           (SELECT mt.level_1 FROM tnuot_menu_tree mt 
                               WHERE mt.element_id IN
                               (SELECT element_tree_id FROM tnuot_menu_elements 
                                WHERE UPPER(element_link) LIKE :url)) 
                       AND level_2 = 0 AND level_3 = 0";

    msc.Parameters.Add("url", SqlDbType.VarChar);
    msc.Parameters["url"].Value = "%" + sName.ToUpper();
    OracleDataReader mrdr = msc.ExecuteReader();

    while (mrdr.Read())
    {
        sResult.arDirectResult.Add(mrdr[0].ToString());
        sResult.arDirectResult.Add(mrdr[1].ToString());
        break;
    }

    msc.Dispose();
    mrdr.Dispose();
    msq.Close();
}

相反，在VS服务器资源管理器中，最后一个查询被翻译"为

Instead, in the VS server explorer, the last query gets 'translated' to

SELECT "level_1", "element_id"
FROM "tnuot_menu_tree"
WHERE ("level_1" IN
    (SELECT "level_1" FROM "tnuot_menu_tree" mt
     WHERE ("element_id" IN
         (SELECT "element_tree_id" FROM "tnuot_menu_elements"
          WHERE (UPPER("element_link") LIKE '%DEFAULT.ASPX'))))) 
AND ("level_2" = 0) AND ("level_3" = 0)

哪个效果很好.

关于如何摆脱这一令人讨厌的任务的任何想法?

Any ideas on how to get rid of this nasty task?

推荐答案

可能的是，括号不是必需的；这是双引号.这与Oracle等效于SQLServer使用方括号-在这里可能有必要，因为表是用小写名称创建的，但是没有双引号，Oracle会自动将名称转换为大写.

Possibly, it isn't the brackets that are necessary; it's the double quotes. This is Oracle's equivalent of SQLServer's use of square brackets - it may be necessary here because the tables have been created with lower-case names, but without the double quotes Oracle automatically converts names to upper-case.

这篇关于Oracle客户端是否需要在列名前后加上括号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！