1

When i try to access and excute a store procedure through following C# code Here is my C# code

   OracleParameter op = null;
   OracleDataReader dr = null;

   OracleCommand cmd = new OracleCommand();

   cmd.CommandText = "pkg_prov_index.getNextPanel";
   cmd.CommandType = CommandType.StoredProcedure;


  op = new OracleParameter("pCurrentPanelId", OracleDbType.Int32);
  op.Direction = ParameterDirection.Input;
  op.Value = masterProviderIndex.CurrentPanelId;
  cmd.Parameters.Add(op);
  op = new OracleParameter("pRefCursor", OracleDbType.RefCursor);
  op.Direction = ParameterDirection.inputOutput;
  cmd.Parameters.Add(op);
  dr =cmd.ExecuteReader(); Here , it gives me an error that says ORA-06550: line 1,   column 37: PLS-00201: identifier 'XYZ' must be declared at 
  if(dr.HasRows)
  {while(dr.Read())
     {
     }
  }

I do have the permissions to access and execute the stored procedure so no issues with that. And when I opened and saw the Stored Procedure in Sql Developer it has an input and output cursor(input cursor is there for reading so many columns as SP is just nothing but a select statement along with a where clause on a View which is written involving 5 tables together and then output cursor is for writing the output result of that SP).Thats what my understanding of cursor is ..since I'm a .Net guy not an oracle expert.Now coming back to the most important point. If you guys look in to my code for input/output cursor all I get is oracledbtype.refcursor in C# enumeration for oracledbtype but the oracle SP is using an input/ouput cursor, is this the root of my error, because I'm sending an input/output parameter as refcursor whereas oracle SP has input/output cursor not a refcursor, but refcursor is all I get when writing code using C#. Your help will be much appreciated. Thanks.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
user2913184
  • 590
  • 10
  • 33
  • What is the signature of the stored procedure ? does it need more than the 2 parameters which you've given ? if we could see that to compare with your code that would help – Angst Apr 12 '14 at 15:50
  • You show the wrong code.It must be the SP code. – Hamlet Hakobyan Apr 12 '14 at 15:50
  • @Hamlet I can't put the code for SP here coz of some company policy. – user2913184 Apr 12 '14 at 15:52
  • @Angst ya there are 2 parameters, one input id as an integer and other input/output cursor – user2913184 Apr 12 '14 at 15:53
  • If you look in the SP code, what is the 'XYZ' object ? (guess you may have changed the name for security reasons when posting here) – Angst Apr 12 '14 at 16:03
  • Yes its just for security reason and its nothing but name of my SP – user2913184 Apr 12 '14 at 16:31
  • @angst what do u think of a cursor in oracle and only a refcursor in c# enumeration, isn't thats what the root cause of the exception.Where by its not possible for oracle to get a cursor from c# code as input/output rather its just getting a refcursor. – user2913184 Apr 12 '14 at 16:37
  • I'm familiar with Oracle but not calling it from c# - is there an Oracle connection in your code ? see the examples here http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx and http://stackoverflow.com/questions/3940587/calling-oracle-stored-procedure-from-c-net – Angst Apr 12 '14 at 17:12
  • what I mean is - maybe either you don't have a session to oracle, or a session with different user id which doesn't have the right privilege to run the proc. – Angst Apr 12 '14 at 17:30
  • If I wouldn't have an oracle session then my code wouldn't have gone inside the database to look up for the SP and tried running it. And user Id that I'm using in my connection string has exclusive privilege to run the proc. – user2913184 Apr 12 '14 at 20:16

1 Answers1

0

Before the CommandText You should try giving a name to the procedure like this:

OracleCommand cmd = new OracleCommand("getNextPanel", _conn);

The parameter OracleParameter must match exactly the parameter name(not only the type) when you call it by CommandType.StoredProcedure

Why is there cmd.Parameters.Add(op); going twice? Also, op.Direction = ParameterDirection.inputOutput; the "i" letter must be upper cased (probably typo).

The steps given is taken by a working example which I developed, following these steps: Calling Oracle stored procedure from C#?

lat94
  • 511
  • 1
  • 5
  • 18