Home » SQL & PL/SQL » SQL & PL/SQL » A query that works in SquirrelSLQL fails in C# (Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production 0 Windows)
A query that works in SquirrelSLQL fails in C# [message #682534] Tue, 27 October 2020 15:45 Go to next message
Darth Waiter
Messages: 29
Registered: October 2020
Junior 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 #682541 is a reply to message #682534] Tue, 27 October 2020 22:00 Go to previous messageGo to next message
flyboy
Messages: 1895
Registered: November 2006
Senior Member
At the first look, the "failing" queries contain trailing semicolon (;). It shall be removed.
However, at least in "native" Oracle dynamic SQL, such statement fails with ORA-00911: invalid character instead: https://stackoverflow.com/questions/39655386/dynamic-sql-error-ora-00911

The other difference is passing string literals (e.g. 'TBLINSERT') in "failing" queries. I do not know nuget at all, however I found this framework (it calls ExecuteReader, but it should be applicable on ExecuteScalar) from their pages: https://docs.microsoft.com/en-us/dotnet/api/system.data.oracleclient.oraclecommand.parameters?view=netframework-4.8
So, if removal of semicolon will not suffice, I would follow with passing string literals as parameters.
Re: A query that works in SquirrelSLQL fails in C# [message #682542 is a reply to message #682541] Wed, 28 October 2020 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also, if you want to count the number of rows use COUNT(*) not COUNT(1) which means you want to count some "1" (and why 1 and not 2, 3 or 'foo'?).

Re: A query that works in SquirrelSLQL fails in C# [message #682549 is a reply to message #682542] Wed, 28 October 2020 07:59 Go to previous messageGo to next message
EdStevens
Messages: 1282
Registered: September 2013
Senior Member
I don't know c# or squirrel, but it appears that your problem stems from a misunderstanding about the place of ';' in writing sql. It (the semi-colon) is not actually part of the sql statement, but rather a directive to the process that is receiving the sql statement, to be passed to the database's internal sql processor. It tells that process 'here ends the sql'. Thus, in some contexts, like creating dynamic sql inside a pl/sql procedure, it is not needed. In fact, in those contexts, it's presence just confuses the processor.
Re: A query that works in SquirrelSLQL fails in C# [message #682550 is a reply to message #682549] Wed, 28 October 2020 08:13 Go to previous message
Darth Waiter
Messages: 29
Registered: October 2020
Junior 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!
Previous Topic: ORA-1403 when trying to select after Insert
Next Topic: What is the proper termination for a stored procedure created from C# code?
Goto Forum:
  


Current Time: Sun Nov 29 19:34:08 CST 2020