3

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

Rupesh Yadav
  • 12,096
  • 4
  • 53
  • 70
KeithA45
  • 779
  • 2
  • 8
  • 11
  • I'd urge you to use the Microsoft client. Make sure you add a reference to System.Data.OracleClient.dll – pilotcam Mar 21 '11 at 14:08
  • why @pilotcam? Microsoft has depreciated the MS Oracle client (System.Data.OracleClient) and recommends using ODP (Oracle.DataAccess.Client). http://blogs.msdn.com/b/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx – Mike Ohlsen Mar 21 '11 at 14:18

1 Answers1

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();
Greg
  • 16,540
  • 9
  • 51
  • 97
  • 2
    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. – KeithA45 Mar 23 '11 at 14:41