A query that works in SquirrelSLQL fails in C# [message #682534] |
Tue, 27 October 2020 15:45 |
|
Darth Waiter
Messages: 78 Registered: October 2020
|
Member |
|
|
My c# code connects to Oracle DB using Oracle ManagedDataAccess nuget package v.19.9.0. It is throwing an exception at the last line:
using (var cmd = new OracleCommand("SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_NAME = 'TBLINSERT' AND OBJECT_TYPE = 'PROCEDURE';", conn))
{
if (Convert.ToString(cmd.ExecuteScalar()).Equals("TBLINSERT"))
The exception is:
{"ORA-00933: SQL command not properly ended"}
But if I copy the command text from an inspector, paste it into SquirrelSQL or SQL Plus that is connected to the same instance with the same user ID and password, and run it, then it works and returns the stored procedure name that I am looking for. I tried running it from c# with and without the trailing semicolon in the naive belief that it might be the reason.
I even tried to trick the error by adding " FETCH FIRST 1 ROW ONLY" at the end. It still worked fine in SQL Plus etc, but the error in C# stayed the same.
Since I cannot reproduce the problem in a SQL client, I am puzzled. It is probably going to be something obvious for you, Oracle pros, but it stumps me.
Now that I am running more tests, more queries are failing with the same error. For example, this query failed in C# but worked in SQL Plus:
var cmd = new OracleCommand("select sys_context('USERENV','SERVER_HOST') as server from dual;", conn);
var servername = (string)cmd.ExecuteScalar();
But this query worked fine in C# and returned 0 since there are no records in the table yet:
using (var cmd = new OracleCommand("SELECT COUNT(1) FROM LOGGING.LOG", conn))
{
var recs = (decimal)cmd.ExecuteScalar();
Thank you,
Al
[Updated on: Tue, 27 October 2020 16:13] Report message to a moderator
|
|
|
|
|
|
Re: A query that works in SquirrelSLQL fails in C# [message #682550 is a reply to message #682549] |
Wed, 28 October 2020 08:13 |
|
Darth Waiter
Messages: 78 Registered: October 2020
|
Member |
|
|
I have parameterized the first failing query according to https://docs.oracle.com/database/121/ODPNT/OracleParameterClass.htm#ODPNT1771 and tried it with and w/o the trailing semicolon:
var cmd = new OracleCommand("select sys_context(:1, :2) as server from dual;", conn);
cmd.Parameters.Add("userEnv", OracleDbType.Varchar2, "USERENV", ParameterDirection.Input);
cmd.Parameters.Add("serverHost", OracleDbType.Varchar2, "SERVER_HOST", ParameterDirection.Input);
var servername = (string)cmd.ExecuteScalar();
With the semicolon, I got the old error. Without it the query worked!
Thank you, flyboy!
|
|
|