Oracle client requires parentheses around column names?
I was recently prompted to migrate our MSSQL database to Oracle.
I am using the old traditional way of doing sql queries.
for some reason, unknown to me, Oracle requires me to put parentheses around the column names (why?) Is there a workaround for this?
The following code will not be executed due to parentheses (used to work in 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();
}
Instead, in VS Server Explorer, the last request is "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)
Which works well.
Any ideas on how to get rid of this annoying task?
source to share
It may not be parentheses that are needed; these are double quotes. This is the Oracle equivalent of using the SQLServer square brackets - it might be needed here because the tables were created with smaller names, but without the double quotes, Oracle automatically converts the names to uppercase.
source to share
The main difference between your first and second query is the quotes (not parentheses). No additional parentheses are needed. They seem to be a strange artifact of the VS Research Server.
Contrary to popular belief, Oracle is case sensitive. Column names level_1
and level_1
are different. If the column and table names are all uppercase, it doesn't matter, because Oracle converts all unquoted identifiers to SQL statements to uppercase.
But if the column and table names are in lowercase, you must put the column names in double quotes to keep the proper wrapping.
source to share