ORA-12899 in CHAR field (1), but I only send 'C'

Question

My Oracle database returns an error:

ORA-12899 - value too large for TIT.ESTADO_CIVIL column (actual: 2, maximum: 1)

But I am very sure that the sended value is unique char 'C'.

Does anyone know why this is happening?

(I am using C # with ODP.NET)

Answer 1

"C # char has 16 bits, googling says that Oracle char types are 8 bits."

There are several ways to deal with this. The best solution would be to fix the database so that it uses character semantics.

alter system set nls_length_semantics = char / 

Since this has major consequences, you must be sure that it solves your problem. Modify the table to use character semantics and see if ORA-12899 removes exceptions.

 SQL> create table t69 (col1 char(1 byte)) 2 / Table created. SQL> desc t69 Name Null? Type ------------- -------- ---------------- COL1 CHAR(1) SQL> alter table t69 modify col1 char(1 char) 2 / Table altered. SQL> desc t69 Name Null? Type ------------- -------- ---------------- COL1 CHAR(1 CHAR) SQL> 

The documentation has a lot of useful information about globalization and character sets. You do not say which version of the database you are using, so here is a link to 9i documents by the length of the semantics .

Answer 2

Committing at the database level seems like the best idea according to the accepted answer - as it avoids any amazing behavior.

However, if you do not want to fix (or cannot access) at the database level, this worked for me in two ways. I’m not sure about the second, but I wanted to share it if someone finds it useful / can explain it.

My problem

• The Oracle stored procedure accepts an input parameter of type char (1 byte) , which is used to set the value of the char (1 byte) column char (1 byte)

• I am using System.Data.OracleClient.OracleCommand

   IDbDataParameter parm = command.CreateParameter(); parm.ParameterName = "myname"; parm.ParameterValue = 'A'; // a C# (16 bit) char 

This causes a value too large for column error that the original poster mentions.

Solution 1

Manually override OracleType after setting the value:

 ((OracleParameter)parm).OracleType = OracleType.Char; 

Decision 2

A little more dodgy - just use the line (I don't understand why this works, so it would be wary to rely on it):

 parm.ParameterValue = "A"; // "A" instead of 'A'