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](https://stackoverflow.com/questions/42856909/stored-procedure-output-varchar2-value-truncated-using-12c-client) applies. — Alex K., Jan 24 '19 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, Jan 24 '19 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, Jan 24 '19 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, Jan 24 '19 at 14:39
@WernfriedDomscheit I'm working on virtual machines, and it will take some time to the company to install ODAC on the golden image of the virtual machine — Ice Ax, Jan 24 '19 at 15:11
You may consider the ODP.NET **Managed** Driver, then you just have to provide the single `Oracle.ManagedDataAccess.dll` file with your application without any further dependencies. Just copy the DLL to your application directory - that's it. — Wernfried Domscheit, Jan 24 '19 at 15:27
Didn't know about that, sounds good for my situation ! Thank you ! I'll chek this out. — Ice Ax, Jan 24 '19 at 16:29

score 2 · Accepted Answer · answered Jan 24 '19 at 14:42

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.

score 2 · Answer 2 · answered Jun 17 '21 at 17:29

2

This solution worked correctly. The tip is:

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

answered Jun 17 '21 at 17:29

Maphiosa Silva

21
2

This has the issue that you need to know the length though. char always fills up to its full length. – Nyerguds Apr 20 '22 at 09:10

score 1 · Answer 3 · answered Dec 01 '20 at 11:19

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

score 0 · Answer 4 · answered Jan 24 '19 at 14:42

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 '19 at 14:42

Wernfried Domscheit

54,457
9
76
110

`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 – Ice Ax Jan 24 '19 at 14:52
Then good luck, because in Oracle 18c (or newer) it will not work anymore. – Wernfried Domscheit Jul 07 '20 at 06:15
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. – Nyerguds Apr 20 '22 at 09:18
@Nyerguds - Not necessarily, see https://stackoverflow.com/questions/44818069/how-to-load-specific-version-of-assembly-from-gac 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. – Wernfried Domscheit Apr 20 '22 at 16:35

C# and Oracle : Output parameter is getting truncated

4 Answers4