ORA-01036 illegal Variable issue

[Jasmine21]

Iam running Oracle query in .NET 2005, using C#. Iam getting error for a simple update statement. details are as follows:

Message: ORA-01036: illegal variable name/number
Source: System.Data.OracleClient

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
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()

Error occured while loading into TESTDB, cmd CommandText = UPDATE TEST.DETAILS SET TEST_APP = 'Y', INSURANCE = 'Y', SECURITY = 'Y', CONTACT = '' WHERE ID = 7865


Iam not using parameterized command, just appending the strings to the query.LIKE:

cmd.CoomandText = "UPDATE TEST.DETAILS SET TEST_APP = " + "'Y'";
cmd.CoomandText += ", INSURANCE = " + "'Y'";

....and so on Is this a problem?
Please Help!!!!

 

[Thargy]

Jasmine,

can you post the query that you are attempting to run? You appear to have no obvious illegal names.

I suggest that you start a sqlplus session, and run the query directly against oracle (thereby taking C# out of the way) and see what happens.

Please post the results of what occurs when you do this.

Regards

Tharg

Grinding away at things Oracular

 

[Jasmine21]

Thanks for the reply.
This is my Query:

cmd CommandText = UPDATE TEST.DETAILS SET TEST_APP = 'Y', INSURANCE = 'Y', SECURITY = 'Y', CONTACT = '' WHERE ID = 7865

When i run this query in Sql plus or Toad, i have no problems. The row gets updated.I see this error only when iam running it thru c# program.

We are using Oracle 9.

 

[Turkbear]

Hi,
This cmd :

cmd CommandText = UPDATE TEST.DETAILS SET TEST_APP = 'Y', INSURANCE = 'Y', SECURITY = 'Y', CONTACT = '' WHERE ID = 7865

May be 'choking on the empty string assignment (CONTACT = '' - Try
CONTACT = NULL, instead..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thargy]

Jasmine,

thanks for the response. Obviously the problem is not to do with Oracle (otherwise it wouldn't run correctly in sqlplus). The error must lie either in C#, or more likely the software that it's using to connect to Oracle and run sql.

As an aside, I only ever accept bug fixes, where the person raising the bug can demonstrate it using sqlplus, not even TOAD is definitive (although it's excellent). By adopting this approach, several so-called 'bugs' have simply disappeared.

In accord with Turkbears suggestion above, check that the apparent double quote really is two single quotes. If that reveals nothing, then I suggest that you do the followign:-

run the command

cmd CommandText = UPDATE TEST.DETAILS SET TEST_APP = 'Y' WHERE ID = 7865

see if this errors, if it doesn't, rollback and then try

cmd CommandText = UPDATE TEST.DETAILS SET TEST_APP = 'Y', INSURANCE = 'Y' WHERE ID = 7865

Then keep on going until the offending item reveals itself.
Regards

Tharg




Grinding away at things Oracular

 

[Jasmine21]

Thanks guys!

I checked the log, iam getting this error when all the columns have values ie no '' for any column. one more thing i forgot to mention is this error occurs when i run first time, but when i run second time, This record updated without any issues. weird huh!

I will try incremental update idea, lets see what happens.