Oracle ORA-00911: invalid character Delete Command

Question

I'm trying to connect to an Oracle database from C#.

My code:

EmailMessage emailMessage = JsonConvert.DeserializeObject<EmailMessage>(email.BodyText.Text);

OracleConnection con = new OracleConnection();
con.ConnectionString = Connection;

OracleCommand cmd = new OracleCommand();
string output = new string(emailMessage.Command.Where(c => !char.IsControl(c)).ToArray());

cmd.CommandText = output;
cmd.Connection = con;
ExecuteNonQuery();

I get the error on ExecuteNonQuery where is the invalid character

Error details

System.Data.OracleClient.OracleException (0x80131938): ORA-00911: invalid character

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at SysConfig.Service1.ReadEmails() in C:\Users\DELL\source\repos\SysConfig\SysConfig\Service1.cs:line 126
at SysConfig.Service1.OnStart(String[] args) in C:\Users\DELL\source\repos\SysConfig\SysConfig\Service1.cs:line 41
at System.ServiceProcess.ServiceBase.ServiceQueuedMainCallback(Object state)

Query is sent through a JSON object:

{
  "Title": "sql",
  "Connection": "",
  "Command": "delete from USERLOG where id=6",
  "Result": false
}

the log (json object and output )

My Question is not duplicate with this Post the post talks about a semi colon in the end i do not have a semi colon

Query on Toad

what is the value return by this code??? string output = new string(emailMessage.Command.Where(c => !char.IsControl(c)).ToArray()); — Avantha Siriwardana, Dec 29 '21 at 11:59
Seems like the string value being passed into the cmd.CommandText is not well formatted. — Avantha Siriwardana, Dec 29 '21 at 12:07
Is column `id` in the database declarated as integer or number? — Bartosz Olchowik, Dec 29 '21 at 12:12
are you sure that output variable is started with 'delete from' ? Maybe the query in the list is wrong? This query should be alright if id is numeric type, problem is probably in output variable. — Bartosz Olchowik, Dec 29 '21 at 12:16
t though of the same thing so i wrote string output = new string(emailMessage.Command.Where(c => !char.IsControl(c)).ToArray()); which should remove any invisible character — Maher Khalil, Dec 29 '21 at 12:17
Your string output should ONLY contain "delete from USERLOG where id=6". For the moment, comment those codes and pass plain text ("delete from USERLOG where id=6") to your cmd.CommandText and run the code and see. — Avantha Siriwardana, Dec 29 '21 at 12:18
the string come from json object so i wrote string output = new string(emailMessage.Command.Where(c => !char.IsControl(c)).ToArray()); it should remove any strange chars — Maher Khalil, Dec 29 '21 at 12:19
I'd love to know the use case for this: you are taking a JSON object from an email and forwarding it straight to an Oracle server with no checking?? — Charlieface, Dec 29 '21 at 12:37
there is checking 1- the email subject must include the word sql - email contents must be json object with the format on the post — Maher Khalil, Dec 29 '21 at 12:50
i used cmd.CommandText = "Delete from USERLOG where id=6;Commit;"; but still give the same error — Maher Khalil, Dec 29 '21 at 12:51
Does this answer your question? [ORA-00911: invalid character](https://stackoverflow.com/questions/12262145/ora-00911-invalid-character) — Andrew, Dec 29 '21 at 12:53
no my command does not include semi colon please see the image — Maher Khalil, Dec 29 '21 at 12:56
In your last comment it did. If you manually run `delete from USERLOG where id=6` on your Oracle DB, do you get that error too? Check if the string has a non-standard encoding or the characters are 2-byte size each. — Andrew, Dec 29 '21 at 13:24
string output = new string(emailMessage.Command.Where(c => !char.IsControl(c)).ToArray()); isn't this command remove non-standard encoding characters - command run on DB - see photo on post — Maher Khalil, Dec 29 '21 at 13:29
@MaherKhalil, I'm talking about a string which could be Unicode (2 bytes per char) or UTF32 (4 per char), that doesn't make every char a control character. The string might look ok but after each character you have null characters. If you copy the command that works on Toad and paste it here: `cmd.CommandText = "delete from userlog where id=6";`, does it fail? — Andrew, Dec 29 '21 at 14:16
Great, now all you have to do is compare the strings. I don't think the uppercase could be the issue, but check just in case (I don't have much experience with Oracle). — Andrew, Dec 29 '21 at 14:42
first the upper case is not a problem in oracle even if it is it should give error invalid table name or invalid column name , second it is the same string i think it is some html string which is not visible when extract the commend from email i think what i need is some code remove all non char from the string including html tags — Maher Khalil, Dec 29 '21 at 14:46
By debugging you should be able to easily find out the differences between the strings. You could play with `str.ToCharArray()` or `Encoding.Unicode.GetBytes(str)`. Or remove parts of both strings until they are equal, to find where the difference is. — Andrew, Dec 29 '21 at 15:33

Oracle ORA-00911: invalid character Delete Command

0 Answers0