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#.
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#. Notice how it went from 44 to 50.
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.
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.
OracleCommand myoracom = myoracon.CreateCommand();
myoracom = new OracleCommand("explain plan set statement_id = 'xd' for select * from employee", myoracon);
trx = myoracom.Connection.BeginTransaction();
myoracom.Transaction = trx;
did you control the table :
TOAD_PLAN_TABLE in which user you run the script on C#
if it does not exists create it manuelly under your c# user :
CREATE TABLE **your_user**.TOAD_PLAN_TABLE ( STATEMENT_ID VARCHAR2(30 BYTE), PLAN_ID NUMBER, TIMESTAMP DATE, REMARKS VARCHAR2(4000 BYTE), OPERATION VARCHAR2(30 BYTE), OPTIONS VARCHAR2(255 BYTE), OBJECT_NODE VARCHAR2(128 BYTE), OBJECT_OWNER VARCHAR2(30 BYTE), OBJECT_NAME VARCHAR2(30 BYTE), OBJECT_ALIAS VARCHAR2(65 BYTE), OBJECT_INSTANCE INTEGER, OBJECT_TYPE VARCHAR2(30 BYTE), OPTIMIZER VARCHAR2(255 BYTE), SEARCH_COLUMNS NUMBER, ID INTEGER, PARENT_ID INTEGER, DEPTH INTEGER, POSITION INTEGER, COST INTEGER, CARDINALITY INTEGER, BYTES INTEGER, OTHER_TAG VARCHAR2(255 BYTE), PARTITION_START VARCHAR2(255 BYTE), PARTITION_STOP VARCHAR2(255 BYTE), PARTITION_ID INTEGER, OTHER LONG, DISTRIBUTION VARCHAR2(30 BYTE), CPU_COST INTEGER, IO_COST INTEGER, TEMP_SPACE INTEGER, ACCESS_PREDICATES VARCHAR2(4000 BYTE), FILTER_PREDICATES VARCHAR2(4000 BYTE), PROJECTION VARCHAR2(4000 BYTE), TIME INTEGER, QBLOCK_NAME VARCHAR2(30 BYTE), OTHER_XML CLOB )