Menu
I have a code line that's throwing an
Oracle Exception - ORA-00911: invalid character
when trying the following C# code:
double tempDateTimeObj = Convert.ToDouble(someClass.GetTime(tempObjID, objStartTime, todayTime).Rows[0][0]);
GetTime is a function that makes an SQL call that takes in the variables you see above, and the SQL call OUTs a Oracle number type and then the GetTime C# function returns a DataTableCollection Tables object of one row each time.
public static DataTable GetTime(string tempObjID, DateTime objStartTime, DateTime todayTime)
{
string sql = "select some_pkg.get_time('" + tempObjID + "', to_date('" + objStartTime + "', 'mm/dd/yyyy hh:mi:ss am'), to_date('" + todayTime + "', 'mm/dd/yyyy hh:mi:ss am')) from dual;";
return <connection object>.getDS(sql).Tables[0];
}
If I debug, grab the sql string having values for the variables, and throw it into Oracle SQL Developer, it works just fine and returns a number in the SQL Dev console. However when I debug and come across that line, the C# code throws the 00911 exception. Since the string sql has been tested in Oracle SQL Dev, the syntax should be valid. Given valid syntax, why is VS2010 throwing this error/exception?
EDIT: Here is a sample string of what's being built in C# and sent to the DB:
select some_pkg.get_time('23569245', to_date('11/8/2012 1:21:06 PM', 'mm/dd/yyyy hh:mi:ss am'), to_date('12/31/2012 12:52:18 AM', 'mm/dd/yyyy hh:mi:ss am')) from dual
Having a semi-colon and not having semi-colon in the C# string have been tried and resulted in the same Oracle exception despite both working in Oracle SQL Dev
967
The next step in resolving ORA-00900 is to restart the database. If you want to instead alter the parameter in the session, you can try: SQL> alter session set NLS_DATE_FORMAT = "DD-MON-YYYY"; The reason ORA-00900 is thrown is that the single quotes become incorrectly read and so the results lose validity.
Aug 27, 2020 991. If you are seeing an error message when trying to log into the catalog that says your "Password contains invalid characters" this means that you have a special character in your password. Passwords can only have letters and numbers.
You may be seeing the Ora-00942 error because you are referencing a table or view in a schema which you did not create but one that is in another schema. To correctly execute the query from another schema, you must reference the table by the schema name.
To correct this issue, simply go back to the end of the phrase and remove the ORDER BY clause. Be sure to go back to the line prior to the ORDER BY clause and re-insert the statement-ending semi-colon. Another case where the ORA-00933 can occur is when attempting to include an ORDER BY clause with a DELETE statement.
At a minimum, you don't want the trailing semicolon in the SQL statement you send from C#.
I would strongly advocate as well that you use bind variables rather than concatenating together a string with your SQL statement. That will be more efficient, it will prevent shared pool related errors, it will make your DBA much happier, and it will protect you against SQL injection attacks.
79
Had the same problem, if anyone else is struggling with this issue try the following:
Remove the ";" from the sqlSentence String in Visual Studio. The ";" is just part of the Oracle DBmanager to separate sentences, it doesn't work out of there (it gets recognized as an invalid character)
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
