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

Question

I have a line of code that throws

Oracle Exception - ORA-00911: Invalid Character

when trying to execute the following C # code:

double tempDateTimeObj = Convert.ToDouble(someClass.GetTime(tempObjID, objStartTime, todayTime).Rows[0][0]); 

GetTime is a function that executes an SQL call that takes the variables you see above, and the SQL call displays the Oracle number type, and then the C # GetTime function returns a DataTableCollection Tables object from 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 am debugging, take the sql string with the values ​​for the variables and drop it into Oracle SQL Developer, it works fine and returns the number in the SQL Dev console. However, when I debug and run into this line, C # code throws an exception 00911. Since the sql line was tested in Oracle SQL Dev, the syntax must be valid. Given valid syntax, why does VS2010 throw this error / exception?

EDIT: Here is an example of a line that is built in C # and sent to the database:

 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 

The existence of a half-column and the absence of a half-column in a C # line was tested and led to the same Oracle exception, despite the fact that they work in Oracle SQL Dev

Answer 1

At a minimum, you don't need a semicolon in the SQL statement sent from C #.

I would advocate strong> because you are using bind variables rather than concatenating a string with an SQL statement. It will be more efficient, it will prevent errors related to the common pool, it will make your database administrator much happier and protect you from SQL injection attacks.

Answer 2

If the same problem, if someone else struggles with this problem, try the following:

Remove the ";" from sqlSentence string in Visual Studio. ";" is just part of the Oracle DBmanager for separating sentences, it does not work (it is recognized as an invalid character)

Answer 3

It was complicated. The following "," character caused an error while executing from embedded SQL while debugging a C # application. However, it worked great with SQL Developer. Debug finally worked when "," was replaced with a more common comma, i.e. ",".