ORA-12899 error in CHAR(1) field, but I'm sending only a 'C'

Question

My Oracle database returns the error:

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

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

Anyone knows why this is happening?

(I'm using C# with ODP.NET)

Answer 1

"The C# char has 16 bits; googling tells me that Oracle CHAR types are 8 bits."

There are a couple of ways of dealing with this. The best solution would be to fix the database so it uses character semantics.

alter system set nls_length_semantics = char
/ 

As this has major ramifications you need to be sure that it solves your problem. Modify your table to use character semantics and see whether it removes the ORA-12899 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 helpful information on globalization and character sets. You don't say which version of the database you're using, so here's a link to the 9i docs on Length Semantics.

Answer 2

Fixing at the database level seems like the best idea, as per the accepted answer - as it avoids any surprising behaviour.

However, if you don't want to fix (or can't access) at the database level, two ways worked for me to get this working. I'm not sure about the second one, but wanted to share it in case someone found it useful / could explain it.

My issue

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

• I'm using System.Data.OracleClient.OracleCommand

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

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

Solution 1

Override the OracleType by hand after setting the value:

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

Solution 2

Slightly more dodgy - just use a string (I don't understand why this works, so would be wary of relying on it):

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