Re: Bind Variables Using ODP.NET

Hi

   Thanks for the Reply
    AS per your suggesion and my understanding i tried the following two options

   Option 1.

   from my understanding of implicite cursor i have tried to reuse the same command object

   i have take 3 command objects and prepared them once.    It works only for first call of each query ,    next time it give exception ORA-01036: illegal variable name/number

   code is as follows

//first SQL

  cmd1 = new OracleCommand(CreateSQLStatement(0),conn);   cmd1.CommandType=CommandType.Text;
  cmd1.Prepare();

//Second SQL

  cmd2 = new OracleCommand(CreateSQLStatement(1),conn);   cmd2.CommandType=CommandType.Text;
  cmd2.Prepare();

//Third SQL

  cmd3 = new OracleCommand(CreateSQLStatement(2),conn);   cmd3.CommandType=CommandType.Text;
  cmd3.Prepare();

//----------------------

  for(i=0;i<NoOfExecution;i++)
 {

	OracleParameter param1 = new OracleParameter();
	param1.ParameterName="param1";
	param1.OracleDbType=OracleDbType.Varchar2;

	OracleParameter param2 = new OracleParameter();
	param2.ParameterName="param2";
	param2.OracleDbType=OracleDbType.Int32;

	param1.Value=i.ToString();
	param2.Value=i.ToString();

	switch(i%3)
	{
	  case 0:
	   {
	     cmd1.Parameters.Add(param1);
	     cmd1.Parameters.Add(param2);
	     cmd1.ExecuteScalar();
	     break;
	   }
	  case 1:
	   {
	     cmd2.Parameters.Add(param1);
	     cmd2.Parameters.Add(param2);
	     cmd2.ExecuteScalar();
             break;
	   }
	 case 2:
	   {
	     cmd3.Parameters.Add(param1);
	     cmd3.Parameters.Add(param2);
	     cmd3.ExecuteScalar();
	     break;
	   }
	}

why the ODP OracleCOmmand doesn't allow me to reuse command object

Same code i have tried with NDP (.net Data provider for oracle) It works fine and SQL trace shows that each Query got Parsed only once and execute n times.

Suggest me if i am missing something in case of ODP.

  Option 2.

	I have increase the SESSION_CACHED_CURSORS size ,
        as it helps for soft parse,
        But the SQL TRACE shows it got parsed n times.

 	Here is the code

        cmd=new OracleCommand("ALTER SESSION SET
SESSION_CACHED_CURSORS=1000",conn);
        cmd.Prepare();
        cmd.ExecuteScalar();

	It dosn't hepls,  tell me the wright way to do it.