C# and Oracle : Output parameter is getting truncated

Question

I'm having another issue with C# and Oracle DB. So I execute a stored procedure and get back the out parameter, everything works fine but at the end, my output data is troncated by half.

create or replace PROCEDURE TEST(MyVar IN VARCHAR2, return_var OUT VARCHAR2) AS 
BEGIN
  DECLARE newId number;
  BEGIN
     SELECT (NVL(MAX(ID),0) + 1) 
       INTO newId 
       FROM MY_TABLE;

     INSERT INTO MY_TABLE
     VALUES (newId, sysdate, 'BEL', '3' , MyVar, 'var2', 'AZ', 'TR', 'FG', 'QW', 'XC', 'IO', '1', '0', sysdate, 'TT', 'BB', 'OO', '8', '9', sysdate, '5', '6');
  END;

  return_var := 'TESTRETURN';
END TEST;

Here is the C# code :

OracleParameter out_param = oCommand.CreateParameter();
out_param.ParameterName = "return_code";
out_param.Direction = ParameterDirection.Output;
out_param.DbType = DbType.String;
out_param.Size = 300;
oCommand.Parameters.Add(out_param);
oCommand.ExecuteNonQuery();
Results = out_param.Value.ToString();

And I get this : TESTR instead of TESTRETURN

If I remplace in Oracle procedure TESTRETURN with something bigger like "THISCHAINHAVE20CARSX" I'v got "THISCHAINH"

And if I replace with just two letters, I'v got just one..

Answer 1

Works when replace VARCHAR2 by CHAR

CREATE OR REPLACE PROCEDURE TEST(var1 IN VARCHAR2, return_code OUT VARCHAR2)

by

CREATE OR REPLACE PROCEDURE TEST(var1 IN VARCHAR2, return_code OUT CHAR)

So the bug from this topic applies to the System.Data.OracleClient.dll assembly too.

Answer 2

This solution worked correctly. The tip is:

• The return parameter is always CHAR and never VARCHAR or VARCHAR2.