CODEWITHSUNDEEP
c#oracle-database
Ice Ax
Ice Ax

Reputation: 182

C# and Oracle : Output parameter is getting truncated

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..

Upvotes: 4

Views: 6184

Answers (4)

Maphiosa Silva
Maphiosa Silva

Reputation: 21

This solution worked correctly. The tip is:

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

Upvotes: 2

Björn
Björn

Reputation: 3418

I ran into this bug too. My setup was as follows:

  • Oracle Express 18c on Windows Server 2019 (2019 isn't explicitly supported by 18c but it seems to work).
  • Oracle Client 19 on Windows Server 2019 (not the same server as above).

I got the exact same problem, the returned string was truncated / cut in half. Although, my problem was with a UPDATE table ... RETURNING char_col INTO :out_param kind of query. (My app is using System.Data.OracleClient)

My solution was to uninstall the 19 client and install 11gR2 client instead. Again, 11gR2 is not explicitly supported on Win2019 but it seems to work. (Run the installation of the 11 client "as administrator".)
On a side note: When connecting from the 11 client to a 18 database I had to update the sqlnet.ora on the database server with: SQLNET.ALLOWED_LOGON_VERSION_SERVER= 11. Otherwise I got this error: "ORA-28040: No matching authentication protocol error".

I also found that this solution also worked (by copying the "old" dlls into the executable's folder): https://stackoverflow.com/a/23161879/1037864

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

For OUT parameters you should/must set property DbType and OracleDbType

Try to add 

out_param.OracleDbType = OracleDbType.Varchar2;

to your code before you execute it.

A shorter version of your code would be this one:

oCommand.Parameters.Add("return_code", OracleDbType.Varchar2, 300, null, ParameterDirection.Output);
oCommand.Parameters("return_code").DbType = DbType.String;

Perhaps you have to use String.Empty rather than null

Upvotes: 0

Ice Ax
Ice Ax

Reputation: 182

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.

Upvotes: 3

Related Questions