5 Replies Latest reply: Mar 31, 2010 5:50 PM by gdarling - oracle RSS

    Error on calling Oracle Stored procedure in C#.net

    638084
      I am working in GIS system and have a package called Trace that contains several procedures such as Delete, Define, Setseed and Execute. I can execute all the procedures without any problem from SQLPLUS and in VB6 application but throws error on calling Trace.Define procedure in C# application. Trace.Delete works fine as expected in C# application. Below is the code:

      string connectionString = "provider=MSDAORA;data source=xxxxx;user id=xxxxx;password=xxxxx";
      OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);

      myOleDbConnection.Open();

      OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();

      //Delete existing trace
      myOleDbCommand.CommandText = "trace.delete";
      myOleDbCommand.CommandType = CommandType.StoredProcedure;

      myOleDbCommand.Parameters.Add("v_Name", OleDbType.VarChar, 255).Value = "C0112";
      myOleDbCommand.Parameters["v_Name"].Direction = ParameterDirection.Input;

      myOleDbCommand.ExecuteNonQuery();

      //Define a new trace
      myOleDbCommand.CommandText = "trace.define";
      myOleDbCommand.CommandType = CommandType.StoredProcedure;

      myOleDbCommand.Parameters.Add("v_Name", OleDbType.VarChar, 255).Value = "C0112";
      myOleDbCommand.Parameters["v_Name"].Direction = ParameterDirection.Input;

      myOleDbCommand.Parameters.Add("n_RNO", OleDbType.Numeric).Value = 1;
      myOleDbCommand.Parameters["n_RNO"].Direction = ParameterDirection.Input;

      myOleDbCommand.Parameters.Add("v_StopCriteria", OleDbType.VarChar, 512).Value = "(conn_s.NORMAL_STATUS=''OPEN'') OR ( (conn_s.g3e_fno = 300) AND (tr_s.G3E_TRACEORDER>1))";
      myOleDbCommand.Parameters["v_StopCriteria"].Direction = ParameterDirection.Input;

      myOleDbCommand.Parameters.Add("v_FilterCriteria", OleDbType.VarChar, 512).Value = "conn.circuit1=tr.user_col3 and (tr.user_col2=''A'' or tr.user_col2=''AC'' or tr.user_col2=''ABC'') and trim(lower(conn.state)) !=''removed'' and trim(lower(conn.state)) !=''abandoned''";
      myOleDbCommand.Parameters["v_FilterCriteria"].Direction = ParameterDirection.Input;

      myOleDbCommand.Parameters.Add("v_StepCost", OleDbType.VarChar, 512).Value = null;
      myOleDbCommand.Parameters["v_StepCost"].Direction = ParameterDirection.Input;

      myOleDbCommand.Parameters.Add("n_TraceId", OleDbType.Numeric).Value = 310;
      myOleDbCommand.Parameters["n_TraceId"].Direction = ParameterDirection.Input;

      myOleDbCommand.Parameters.Add("v_IterationProc", OleDbType.VarChar, 512).Value = null;
      myOleDbCommand.Parameters["v_IterationProc"].Direction = ParameterDirection.Input;

      myOleDbCommand.ExecuteNonQuery();
      myOleDbConnection.Close();


      It throws the following error on statement myOleDbCommand.ExecuteNonQuery(); for Trace.Define procedure. I have no idea where to look for the error.

      ORA-06550: line 1, column 58:
      PLS-00103: Encountered the symbol ")" when expecting one of the following:

      ( - + case mod new not null others <an identifier>
      <a double-quoted delimited-identifier> <a bind variable> avg
      count current exists max min prior sql stddev sum variance
      execute forall merge time timestamp interval date
      <a string literal with character set specification>
      <a number> <a single-quoted SQL string> pipe
      <an alternatively-quoted string literal with character set specification>
      <an alternatively-q


      Also below are SQL statements that I have executed from SQLPLUS without any problem.

      SQL> execute trace.delete('C0112');
      PL/SQL procedure successfully completed.

      SQL> execute trace.define('C0112', 1, '(conn_s.NORMAL_STATUS=''OPEN'') OR ( (conn_s.g3e_fno = 300) AND (tr_s.G3E_TRACEORDER>1))', 'conn.circuit1=tr.user_col3 and (tr.user_col2=''A'' or tr.user_col2=''AC'' or tr.user_col2=''ABC'') and trim(lower(conn.state)) !=''removed'' and trim(lower(conn.state)) !=''abandoned''', null, 310, null);

      PL/SQL procedure successfully completed.

      SQL> execute trace.setseed(304843);

      PL/SQL procedure successfully completed.

      SQL> execute trace.execute();

      PL/SQL procedure successfully completed.

      SQL> execute trace.setseed(304843);

      PL/SQL procedure successfully completed.

      SQL> execute trace.execute();

      PL/SQL procedure successfully completed.


      Your help will be appreciated.

      Thanks, Manish
        • 1. Re: Error on calling Oracle Stored procedure in C#.net
          gdarling - oracle
          Hi,

          You need to escape single ticks with double ticks when you're using literal values in sqlplus (which you're doing). You DON'T need to escape them when using a bind variable from OLEDB.

          Try this.

          ...
          myOleDbCommand.Parameters.Add("v_StopCriteria", OleDbType.VarChar, 512).Value = "(conn_s.NORMAL_STATUS='OPEN') OR ( (conn_s.g3e_fno = 300) AND (tr_s.G3E_TRACEORDER>1))";
          myOleDbCommand.Parameters["v_StopCriteria"].Direction = ParameterDirection.Input;

          myOleDbCommand.Parameters.Add("v_FilterCriteria", OleDbType.VarChar, 512).Value = "conn.circuit1=tr.user_col3 and (tr.user_col2='A' or tr.user_col2='AC' or tr.user_col2='ABC') and trim(lower(conn.state)) !='removed' and trim(lower(conn.state)) !='abandoned'";
          ...etc...

          Hope it helps,
          Greg
          • 2. Re: Error on calling Oracle Stored procedure in C#.net
            638084
            Hi Greg,

            I did try with single quote as you showed but it did not help. Getting the same error again.

            Thanks,
            Manish
            • 3. Re: Error on calling Oracle Stored procedure in C#.net
              gdarling - oracle
              Hrmmm.. I may have jumped to a conclusion there. Looking more closely at your values, it looks like you may be using them as part of a literal sql statement, perhaps via EXECUTE IMMEDIATE inside the procedure? In that case, you would indeed need to escape the single tick. It doesnt make sense that you'd be getting what looks to be a compile error if you're just executing a sql statement via execute immediate though. Are you constructing an anonymous block?

              Assuming you're getting the error from inside the procedure, what exact line of code is executing inside the procedure when you get the error? What is different about that same line of code when you do NOT see the error executing the procedure via sqlplus?

              It may be easiest if you open a SR with Support so we can help you get that sorted out more quickly.

              Greg
              • 4. Re: Error on calling Oracle Stored procedure in C#.net
                638084
                The procedures that I am calling are product procedures and they are compressed and I do not have access to it.
                • 5. Re: Error on calling Oracle Stored procedure in C#.net
                  gdarling - oracle
                  I dont any other good suggestions for you there as to anything obvious; nothing else jumps out at me.

                  You could enable a 10046 trace to try to figure out what's going on.

                  As another suggestion, you could just execute directly what you're executing in sqplus via an anonymous block and that should work..
                  cmd.ComandType = CommandType.Text;
                  cmd.CommandText = "begin trace.define('C0112', 1, '(conn_s.NORMAL_STATUS=''OPEN'') OR ( (conn_s.g3e_fno = 300) AND (tr_s.G3E_TRACEORDER>1))', 'conn.circuit1=tr.user_col3 and (tr.user_col2=''A'' or tr.user_col2=''AC'' or tr.user_col2=''ABC'') and trim(lower(conn.state)) !=''removed'' and trim(lower(conn.state)) !=''abandoned''', null, 310, null);end;";
                  cmd.ExecuteNonQuery();
                  Greg