Calling an Oracle stored procedure in C# using "Oracle.DataAccess" (with a parameter)

Question

So I'm trying to call an Oracle stored procedure from my C# .NET application. Most online references I can find suggest "using System.Data.OracleClient;", but .Net 3.5 doesn't recognize that namespace so I'm using "Oracle.DataAccess.Client" instead.

Here's some paraphrasing of my code below, with a previously setup and tested OracleConnection called 'myConn' already filled with parameter ':arg_myArg' (it's a number, if that matters):

command.Connection = myConn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "exec mySchema.myProc(:arg_myArg)"
command.ExecuteNonQuery();

The trick is that the procedure returns nothing by design, it simply populates a different table which I pull from. However, when I try to run the code above, I get a 'OracleException' on the final line and gives this error:

ORA-06550: line 1, column 13:
PLS-00103: Encountered the symbol "MYSCHEMA" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "MYSCHEMA" to continue.

Removing the "exec" from the command gives this error instead:

ORA-06550: line 1, column 8:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

Any ideas? I'd be happy to clarify anything

This is my first time posting on stackoverflow.com and my last week at this job, so I appreciate your understanding and relative haste with figuring this out

I'd urge you to use the Microsoft client. Make sure you add a reference to System.Data.OracleClient.dll — pilotcam
– pilotcam, Commented Mar 21, 2011 at 14:08
why @pilotcam? Microsoft has depreciated the MS Oracle client (System.Data.OracleClient) and recommends using ODP (Oracle.DataAccess.Client). blogs.msdn.com/b/adonet/archive/2009/06/15/… — Mike Ohlsen
– Mike Ohlsen, Commented Mar 21, 2011 at 14:18

Greg · Accepted Answer · 2011-03-21 14:07:20Z

7

I think you need to something like this

command.Connection = myConn;
command.CommandType = CommandType.StoredProcedure;  
command.CommandText = "mySchema.myProc";  // the proc name   
command.Parameters.Add(/* TODO: Add parameter here */); 
command.ExecuteNonQuery();

answered Mar 21, 2011 at 14:07

community wiki

Greg

Sign up to request clarification or add additional context in comments.

1 Comment

KeithA45 Over a year ago

That seems to have fixed it. FOR FUTURE INFORMATION SEEKERS: Turns out to run it, I had to NOT include "exec" in the command, NOT include the parameter in the procedure call, and keep the oracle parameter saved in the command, NOT its connection.

Collectives™ on Stack Overflow

Calling an Oracle stored procedure in C# using "Oracle.DataAccess" (with a parameter)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related