ORA-00911: invalid character in C#, but not Oracle SQL Developer

Question

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

Worsley

Kurt why not create a stored procedure and use parameterized query – Complect

"The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement. INSERT...EXEC statements can be written using table-valued functions." msdn.microsoft.com/en-us/library/ms187650.aspx Since the function does not use intermediate results, it is better staying as a function rather than a stored procedure. – Worsley

Answer 1

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.

Breeding

Removed the ';' from the SQL statement sent by C# and still have the same exception message – Worsley

@KurtWagner - Then the problem is in something that you're using to build up the string (this is one reason that using bind variables is so helpful). If you want to avoid using bind variables, you'll need to tell us exactly what string you are building. Can you print the SQL statement before executing it? – Breeding

I've appended a sample string that's being sent using a real case. – Worsley

I had a similar issue, still not sure why this fixed it but I opened the file in notepad++ and converted to UTF-8 from UTF-8 BOM and now it works... – Tortola

Answer 2

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)

Leotaleotard

Thanks, that helped me! By the way, that's very stupid design decision. – Elisabetta

Answer 3

This was a tricky one. The following character "，" produced the error when executed from inline SQL while debugging a C# application. It worked fine from SQL Developer though. Debug finally worked when "，" was replaced with the more conventional comma, i.e ",".

Hannon

Answer 4

I have faced this error before, when calling the stored procedure from C# it gives "Invalid character" error at owner.MyPackageName.StoredPrcedureName. After digging deep and trying for hours to identify where is the problem exactly it turns out that there are some hidden and/or special characters like \n. The solution was to re-write the query. So, if someone out there is struggling with this problem, lesson to be learned

1. Don't write your queries somewhere else and copy-past them to your DB editor/management tool, especially Toad.
2. Before calling the stored procedure from C# or any other language, make sure to test the stored procedure locally on your editor/management tool of choice, in case of any error in the query you could easily figure it out.

Hopefully this will save someone else's time.

Convertiplane