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

If two people run this concurrently they will both get the same newId? If you want a unique value use an identity column/sequence. Also see if Stored procedure OUTPUT VARCHAR2 value truncated using 12c client applies. — Alex K.
– Alex K., Commented Jan 24, 2019 at 14:15
Not sure if it applies, I'm using the System.Data.OracleClient.dll assembly and not the client from Oracle. This is because of I can't install third software on the machine. I'll check it out. — Ice Ax
– Ice Ax, Commented Jan 24, 2019 at 14:34
Hmm, might be a problem with unicode vs nonunicode strings or the size being interpreted as size in bytes rather than characters. Does it work if you double the size in the C# code? Does the procedure work as intended when directly executed in an SQL client? — sticky bit
– sticky bit, Commented Jan 24, 2019 at 14:35
Yeah I tried by changing the DbType and Size but still the same result. BUT ! I've change the return_code OUT VARCHAR2 to return_code CHAR and it works fine. Seems that the bug from the topic @AlexK. posted applies also to the System.Data.OracleClient.dll assembly. — Ice Ax
– Ice Ax, Commented Jan 24, 2019 at 14:39

Ice Ax · Accepted Answer · 2019-01-24 14:42:02Z

3

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.

answered Jan 24, 2019 at 14:42

Ice Ax

1821 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maphiosa Silva · Accepted Answer · 2021-06-17 17:29:51Z

2

This solution worked correctly. The tip is:

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

answered Jun 17, 2021 at 17:29

Maphiosa Silva

212 bronze badges

1 Comment

Nyerguds Over a year ago

This has the issue that you need to know the length though. char always fills up to its full length.

Björn · Accepted Answer · 2020-12-01 11:19:23Z

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

Wernfried Domscheit · Accepted Answer · 2019-01-24 14:42:54Z

0

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

answered Jan 24, 2019 at 14:42

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

4 Comments

Ice Ax Over a year ago

OracleDbType proprety don't exist in the System.Data.OracleClient.dll assembly. It's part of the Oracle's ODAC and this one it's not installed on my machine. I'm forced to use the old and obsolete assembly

Wernfried Domscheit Over a year ago

Then good luck, because in Oracle 18c (or newer) it will not work anymore.

Nyerguds Over a year ago

Problem with using Oracle.DataAccess is that you need to compile your project for one specific version of the Oracle.DataAccess dll, and it won't work on any other versions. We have too many customers with different systems, so that's completely implausible.

Wernfried Domscheit Over a year ago

@Nyerguds - Not necessarily, see stackoverflow.com/questions/44818069/… Compile it with the least supported version of Oracle.DataAccess, then the GAC policy file should redirect to the version which is installed on your customers machine.

Collectives™ on Stack Overflow

C# and Oracle : Output parameter is getting truncated

4 Answers 4

Comments

1 Comment

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related