2 Replies Latest reply: Aug 3, 2011 7:45 AM by 879409 RSS

    call stored procedure with OUT VARCHAR2 parameter fails

    879409
      Dear all,

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

      create or replace procedure TestOut(RESULT_CODE OUT VARCHAR2) is
      begin
      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);
      Conn.Open();
      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.Parameters.Add(Param);

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

      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