Showing results for 
Search instead for 
Did you mean: 

CHR function & Oracle10g & ORA-01401

Ankit Grover
Frequent Advisor

CHR function & Oracle10g & ORA-01401

Hi all:

We have a application running with Oracle9i. There is something typical done for creating the tables in our database. Any column not part of primary key is assigned a default value(which is actually null) using:

DEFAULT (LPAD(CHR(0),008,CHR(0))),

But if I try to create a similar table in Oracle10g database using the same script, it throws this error:

ORA-01401: inserted value too large for column

Is there some change in the syntax in 10g or the way in which it parses/interprets the SQL command different from 9i?

Wim Rombauts
Honored Contributor

Re: CHR function & Oracle10g & ORA-01401

My answer is a little beside your question why the above does not work, but why not simply set DEFAULT(NULL) ?
Honored Contributor

Re: CHR function & Oracle10g & ORA-01401

Hi Ankit,

This has to do with NLS_LENGTH_SEMANTICS paramter which must have been set in your 10g environment.

For more information, please see Metalink Note:144808.1 - Subject: Examples and limits of BYTE and CHAR semantics usage

Below a simple quote from this doc:

The default sizing of character data types (CHAR, VARCHAR2, and LONG) is in BYTES. CHAR(10) in a table definition means 10 bytes not 10


hope this helps!
kind regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ankit Grover
Frequent Advisor

Re: CHR function & Oracle10g & ORA-01401

Sorry guys,
I had mixed the differnet problemd I was facing regarding databases while creating the thread. The error is not coming for Oracle10g but in Oracle9i with UTF8 character set.

It means CHR(0) , ie, null, should be taking more than 1 byte for storage. Is it true?
Ankit Grover
Frequent Advisor

Re: CHR function & Oracle10g & ORA-01401

Hi Wim,
The concept behind using CHR(0) in default definition has something to do with the retrieval of data. Our application is designed in such a way that the returned data should always be in fixed in the length. Thats why no varchar datatype has been used in our case.
I have been told that CHR(0) is different from NULL in the sense that it will return the column with fixed length even if the value is NULL in it.

Though I am not very sure how is it happening. I inserted values into only 2 columns out of 4 columns giving Oracle the power to insert default values in rest of 2 columns. But on fetching data through SQL*Plus, I am getting NULL in those 2 columns.

I'll confirm how application is handliong such cases and will get back with the progress.