Troubleshooting the "ORA-01036: illegal variable name/number" Error When Calling an Oracle Stored Procedure from .NET

-
Yesterday I had written some code in C# to call a stored procedure in an Oracle database, and when I began testing this code today, I was surprised to get the following error whenever the code would attempt to execute the OracleCommand:

ORA-01036: illegal variable name/number

I was scratching my head. Why wasn't this working? I've done this before!

From previous experience, I knew that the names of the procedure parameters must match exactly when you add them to the OracleCommand object. In other words, if your stored procedure is expecting two parameters, named email and phonenumber, your code should look something like this:

cmd.Parameters.Add("email", OracleType.VarChar);
cmd.Parameters.Add("phonenumber", OracleType.VarChar);
cmd.Parameters["email"].Value = psEmail;
cmd.Parameters["phonenumber"].Value = psPhone;

I checked the parameter names and I was already using the correct ones. What else was I missing? Then it dawned on me: when I instantiated the OracleCommand object, I forgot to specify that it was a stored procedure. When I added the following line of code, everything worked as intended:

cmd.CommandType = System.Data.CommandType.StoredProcedure;

I hope this helps anyone else who was going through the same frustration as I was! :)

Comments

Hank said…
Thank you!!! Two hours of ripping my beard and rebooting the oracle server and client... DOH! Thanks!
March 18, 2009 at 4:28 PM
Anonymous said…
Thanks!!. My issue was a misspelling on one of the parameters name.

in my WebService (notice the spaces after "P_status

OracleParameter P_STATUS = CredUtility.CreateParam("P_STATUS ", OracleType.VarChar, ParameterDirection.Input, Status);

in the stored procedure: the parameter name was "P_STATUS"
April 13, 2012 at 10:28 AM
Anonymous said…
Thank you, Alan. I was invoking a complex stored procedure with all sorts of parameters and forgot to set the Command Type! You saved my hours!
July 5, 2012 at 11:04 AM
Anonymous said…
+1 or more for this...
Lost a few hours there :(
Thanks a lot
August 2, 2012 at 4:35 AM
Player said…
It is really helped me.I found space behind my passing param in my .net code. Thanks for the post owner and Anonymous.
September 6, 2013 at 1:31 AM

Post a Comment

Popular Posts

Resolving the "n timer(s) still in the queue" Error In Angular Unit Tests

-
A few months ago one of the developers I work with ran into the below error in an Angular unit test: 1 timer(s) still in the queue The fix was to specify a particular amount of time in the tick() call as shown below: it( 'should set accountConfirmationDetails when getAccountConfirmationDetails is called' , fakeAsync(() => {          // Arrange          let  spyGetAccountConfirmationDetails = spyOn(accountService,  'getAccountConfirmationDetails' )         .and         .returnValue(Promise.resolve(AccountMockData.mockAccountConfirmationDetails));                    // Act         fixture.detectChanges();   ...
Read more

How to Get Norton Security Suite Firewall to Allow Remote Desktop Connections in Windows

-
Image
I ran into an issue recently with RDP connections being blocked by the firewall in Norton Security Suite. I checked the traffic rules under Settings > Firewall > Traffic Rules  and found that it was enabled by default (and couldn't be edited): But during further research, I found  this page  on Norton's support site which discusses the different trust levels of networks and mentions that, for the Public trust level, "This setting also blocks remote desktop connections by default.". I checked my network's trust level, and sure enough, it was set to Public (I'm not sure how/why). (Sidebar: Notice in the screenshot above that allowing Remote Desktop for Public Networks is defaulted to Allow , though that rule is not active). Here's what my network trust level looks like now after changing the setting to Private (accessible under Settings > Firewall > General Settings > Network Trust ): After changing the network trust level t...
Read more

How to Determine if a Column Exists in a DataReader

-
I recently had to work on something which required me to query a database, open the results in a DataReader, and check for the existence of a particular column. While the DataReader doesn't have a method to do this, with just a few steps I was able to accomplish this task. Here's how. First, the DataReader has a method named GetSchemaTable() , which returns a DataTable consisting of information regarding the data contained in the DataReader. The first column in this DataTable is named ColumnName , and its value is (obviously) the name of the column. Knowing this, we can loop through the rows in the DataTable, checking the ColumnName column for a value that equals the name of the column we're looking for. To this end, I wrote a static method in one of my classes which takes the DataReader and the name of the column being sought: (C#) public static bool HasColumn(DbDataReader Reader, string ColumnName) {    foreach (DataRow row in Reader.GetSchemaTable().Rows)  ...
Read more

Fixing the "Please add a @Pipe/@Directive/@Component annotation" Error In An Angular App After Upgrading to webpack 4

-
I'm currently in the process of upgrading an Angular application which does not  use the Angular CLI from webpack 2 to webpack 4. The incremental upgrade from webpack 2 to 3 was easy, but once I made the jump from 3 to 4, I encountered a lot of problems. I went through them one by one, but the one that had me stumped was the following: Unhandled Promise rejection: Unexpected value 'MakePositivePipe' declared by the module 'SharedModule'. Please add a @Pipe/@Directive/@Component annotation. ; Zone: <root> ; Task: null ; Value: Error: Unexpected value 'MakePositivePipe' declared by the module 'SharedModule'. Please add a @Pipe/@Directive/@Component annotation. The code was transpiling/compiling fine, but this error was occurring when I'd navigate to a page containing this pipe. This code has worked fine under webpack 2 and 3. I made sure that the pipe was being set up as a declaration , which it was. What finally  corrected the issue for me w...
Read more

Silent Renew and the "login_required" Error When Using oidc-client

-
I recently implemented the Silent Renew functionality of oidc-client in an Angular application I work on, and was getting reports that the users' tokens were still expiring. This made no sense to me as I'd verified the code was working during development, but yesterday I found the problem and am hoping it will save someone some time. The error we were getting back was "Error from signinSilent: login_required". This indicates that the user's session is no longer active on IdentityServer and they must re-authenticate -- which, of course, is what we're trying to prevent by using Silent Renew. In our case, the problem wasn't with the Silent Renew functionality, but rather the user's session was expired. The reason why  it was expired was because, for some reason, we were setting the session length to 15 minutes instead of the default 10 hours . What was occurring was that Silent Renew was attempting to renew a token after 55 minutes (60 minutes expirat...
Read more