title: Executing Oracle Explain Plan in C#
body: <p>Normally, executing an "explain plan" for an sql statement results in records inserted into plan_table. This works fine for me when executed via TOAD. My requirement is to execute this via my c# application (Oracle.DataAccess.Client lib). Unfortunately, the same statement that worked on TOAD is not resulting in any records inserted into plan_table when executed in C#.</p>
  
  <p>The peculiar thing is that I know for sure the plan_id sequence is being consumed when executed in C# although no records are inserted into the table. I can confirm this by executing a new explain plan in TOAD and notice that plan_id sequence has skipped based on the number of attempts i made via C#. <a href="https://i.stack.imgur.com/zpKCu.jpg" rel="nofollow">Notice how it went from 44 to 50.</a></p>
  
  <p>The reason I need explain plan specifically is that I want to fetch the list of tables and columns involved in a given query BEFORE execution. I considered syntax parsing but it felt like reinventing the wheel given that plan_table records contained exactly the data I wanted.</p>
  
  <p>For all intents and purposes, the same user is logged on TOAD and C# so I am not suspecting limited privileges. I tried with and without committing. I have also tried putting the explain plan in a stored procedure and calling that in C# with the same results. (works as expected on TOAD, but no records inserted on C#). For it to work in a stored procedure, I had to make it EXECUTE IMMEDIATE.</p>
  
  <pre><code>myoracon.Open();
  OracleCommand myoracom = myoracon.CreateCommand();
  myoracom = new OracleCommand("explain plan set statement_id = 'xd' for select * from employee", myoracon);
  OracleTransaction trx;
  trx = myoracom.Connection.BeginTransaction();
  myoracom.Transaction = trx;
  myoracom.ExecuteNonQuery();
  trx.Commit();
  myoracon.Close();
  </code></pre>
  
  <p>So, after all this,  my question is the following: Why is explain plan in C# not resulting in records inserted into plan_table and what can I do to get it to work?</p>
  
  <p>Thanks in advance!</p>