Difference between ORA-12899 and ORA-01480

user_840184 0
Viewed 61

I would like to understand the difference between ORA-12899 and ORA-01480

ORA-12899: value too large for column

ORA-01480: trailing null missing from STR bind value

Based on my understanding, I know about ORA-12899 and how it can come. Lets say if datatype for some column is VARCHAR2(100 BYTE) and I am trying to insert more than 100 BYTE in the column then I am getting ORA-12899.

What about ORA-01480 ? I searchthe ed on internet and and the similar explanation like ORA-12899

From google :

ORA-01480: trailing null missing from STR bind value

Cause: A bind variable of type 5 (null-terminated string) does not contain the terminating null in its buffer.

Maybe you're trying to insert a string in a column that is bigger than the column length. So, the terminating character is not being
inserted at the end of the string.

Both ORA-12899 and ORA-01480 look similar. Can someone please explain the exact difference with an example?

oracle19c oracle sql
1 Answers

The error:

ORA-01480: trailing null missing from STR bind value

Is for languages such as C or C++ (among many others) which allow low level manipulation of string-like objects.

If you have a VARCHAR2(100) column and you try to pass, to a bind variable, the byte array:

['h','e','l','l','o']

Then you may get the ORA-01480 error because a NUL terminated string is expected and it should have been:

['h','e','l','l','o','\0']

And the NUL character is expected as it allows the SQL engine to determine the length of the VARCHAR2 variable length string.

An example is using Pro*C:

char s[2] = "ab";
EXEC SQL SELECT :s into :s2 FROM dual;

Outputs: ORA-01480: trailing null missing from STR bind value

char s[3] = "ab\0";
EXEC SQL SELECT :s into :s2 FROM dual;

Works.

user_15092640
Related