title: How to call a Oracle stored procedure (PL/SQL) from .NET?
body: <p>I am new to Oracle. There is a requirement in our firm for a project using Oracle and .Net. So I was trying to run a demo application. I am using Oracle 10g XE as DB and VS2010.</p>
  
  <p>I wrote a procedure with a simple select query, which got compiled (got this procedure format by googling).</p>
  
  <p><img src="https://i.stack.imgur.com/3pWNs.png" alt="The procedure, which got compiled"></p>
  
  <p>I ran the stored procedure from the SQL command prompt from XE Dashboard itself. This was the result:</p>
  
  <p><img src="https://i.stack.imgur.com/ZseAG.png" alt="enter image description here"></p>
  
  <p>Now I wrote code in C# to call that stored procedure:</p>
  
  <pre><code>string sqlCon = "Data Source=xe;Persist Security Info=True;User     ID=sa;Password=password;Unicode=True;Provider=OraOLEDB.Oracle;";
  OleDbConnection Con = new OleDbConnection(sqlCon);
  OleDbCommand cmd = new OleDbCommand();
  
  DataSet ds = null;
  OleDbDataAdapter adapter;
  
  try
  {
      Con.Open();
      ////Stored procedure calling. It is already in sample db.
      cmd.CommandText = "TESTPROC";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Connection = Con;
  
      ds = new DataSet();
      adapter = new OleDbDataAdapter(cmd);
      adapter.Fill(ds, "Users");
  
      return ds.Tables[0];
   }
   catch (Exception ex)
   {
      throw new Exception(ex.Message);
   }
  </code></pre>
  
  <p>This code block didn't throw any exceptions. It got executed but what I received was an empty Dataset. But when I tried to fetch data using the query directly I got the result.</p>
  
  <p>So, is the way I am trying to access the stored procedure correct? or Is there any mistake in my stored procedure? Can anyone point out the error or the best way to do this?</p>
  
  <p>Thanks in advance.</p>