This discussion is archived
2 Replies Latest reply: Aug 3, 2011 5:45 AM by 879409 RSS

call stored procedure with OUT VARCHAR2 parameter fails

879409 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points