1 Reply Latest reply: Apr 16, 2012 9:52 AM by 874747 RSS

    42000 Error ODBC driver for Oracle Syntax error or access violation

    874747
      Hi,

      I have a simple Oracle stored procedure:

      CREATE OR REPLACE procedure DEV.SL_CLOB_TEST(numId1 IN PLS_INTEGER,id IN PLS_INTEGER, strText IN CLOB)
      as
      begin
      insert into test_table
      values (numId1,id, strText, sysdate, user);
      end;
      /

      And I have a client .net code to use ODBC.NET

      class Program
      {
      static void Main(string[] args)
      {
      string connectionString = "Driver={Microsoft ODBC for Oracle};Server=servername;Uid=myusername;Pwd=mypassword";
      var connection = new OdbcConnection(connectionString);
      connection.Open();

      IDbCommand command = connection.CreateCommand();

      command.CommandText = "{call SL_CLOB_TEST(?,?,?)}";
      command.CommandType = CommandType.StoredProcedure;

      OdbcParameter parameter1 = new OdbcParameter("NUMID1", OdbcType.Int);
      parameter1.Value = 123;
      parameter1.Direction = ParameterDirection.Input;
      command.Parameters.Add(parameter1);

      OdbcParameter parameter2 = new OdbcParameter("ID", OdbcType.Int);
      parameter2.Value = 234;
      parameter2.Direction = ParameterDirection.Input;
      command.Parameters.Add(parameter2);

      OdbcParameter parameter3 = new OdbcParameter("STRTEXT", OdbcType.VarChar);
      parameter3.Value = getClob();
      parameter3.Direction = ParameterDirection.Input;
      command.Parameters.Add(parameter3);

      command.ExecuteNonQuery();
      }

      private static string getClob()
      {
      return new string('a', 10);
      }
      }

      When I ran it I got ERROR [42000] [Microsoft][ODBC driver for Oracle]Syntax error or access violation, the whole call stack is

      System.Data.Odbc.OdbcException was unhandled
      Message=ERROR [42000] [Microsoft][ODBC driver for Oracle]Syntax error or access violation
      Source=msorcl32.dll
      ErrorCode=-2146232009
      StackTrace:
      at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
      at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
      at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
      at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
      at ConsoleApplication1.Program.Main(String[] args) in D:\Temp\ConsoleApplication1\ConsoleApplication1\Program.cs:line 32
      at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
      at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
      at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
      at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
      at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
      at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
      at System.Threading.ThreadHelper.ThreadStart()
      InnerException:

      Anybody could point out what is wrong with my .Net code?