2 Replies Latest reply on Aug 3, 2011 12:45 PM by 879409

    call stored procedure with OUT VARCHAR2 parameter fails

      Dear all,

      I am using Oracle Provider for OLE DB, version I have a very simple stored procedure, containing one OUT VARCHAR2 parameter:

      create or replace procedure TestOut(RESULT_CODE OUT VARCHAR2) is
      RESULT_CODE := 'X';
      end TestOut;

      The following piece of C# code fails:

      string ConnStr = "Provider=OraOLEDB.Oracle;User ID=test;Password=test;Data Source=sandbox;";
      OleDbConnection Conn = new OleDbConnection(ConnStr);
      OleDbCommand Cmd = new OleDbCommand();
      Cmd.Connection = Conn;
      OleDbParameter Param = Cmd.CreateParameter();
      Param.ParameterName = "RESULT_CODE";
      Param.Direction = System.Data.ParameterDirection.Output;
      Param.OleDbType = OleDbType.BSTR;
      Param.Size = 1;

      Cmd.CommandType = System.Data.CommandType.Text;
      Cmd.CommandText = "{CALL TESTOUT(?)}";

      If the stored procedure is modified to contain a OUT NUMBER parameter, the same code works, after modifying the parameter type to the corresponding OleDBType (OleDbType.SmallInt).

      What am I doing wrong when handling OUT VARCHAR parameters? Is there any limitation regarding stored procedure parameter types using the Oracle OleDB provider?

      Edited by: user957565 on 1/Ago/2011 6:31