Home » SQL & PL/SQL » SQL & PL/SQL » update column problem
update column problem [message #264456] Mon, 03 September 2007 07:27 Go to next message
nageswara
Messages: 9
Registered: August 2007
Junior Member
Hi,

I am having hard time in figuring out why a column defined as varchar(100) is not allowing me to update with a length of the string more than 25. I am able to update the same column with a string of 25 chars. I have checked for any contraints which may restrict the update for more than 25 charecter but no such constraint exists.

So is it possible to restrict the size of the string in some other way in database?

Following is the error message that i am getting trying update the with more than 25 char char string.

ORA01401: inserted value too large for column

Note: when checked the system table: all_tab_columns.char_length has value 25. Does this effect the update statement I am not sure.

oracle version used: 9.2.0.6.0


Thanks in advance

Regards
Nageswara
Re: update column problem [message #264460 is a reply to message #264456] Mon, 03 September 2007 07:35 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

I am having hard time in figuring out why a column defined as varchar(100) is not allowing me to update with a length of the string more than 25. I am able to update the same column with a string of 25 chars. I have checked for any contraints which may restrict the update for more than 25 charecter but no such constraint exists.



Use varchar2(100) instead of varchar(100)
Re: update column problem [message #264461 is a reply to message #264456] Mon, 03 September 2007 07:38 Go to previous messageGo to next message
nageswara
Messages: 9
Registered: August 2007
Junior Member
Sorry I stand corrected the column was defined as varchar2(100) only.

Regards
Nageswara.
Re: update column problem [message #264462 is a reply to message #264456] Mon, 03 September 2007 07:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And when you do a DESCribe of your table it shows a length of 100?

SQL> select char_length, column_name from all_tab_columns
  2  where table_name = 'EMPLOYEES' and owner = 'HR';

CHAR_LENGTH COLUMN_NAME
----------- ------------------------------
          0 EMPLOYEE_ID
         20 FIRST_NAME
         25 LAST_NAME
         25 EMAIL
         20 PHONE_NUMBER
          0 HIRE_DATE
         10 JOB_ID
          0 SALARY
          0 COMMISSION_PCT
          0 MANAGER_ID
          0 DEPARTMENT_ID

11 rows selected.

SQL> desc hr.employees
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------

 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

SQL>
It seems pretty consistent to me. You might have got a trigger that fires on insert?

MHE

[Updated on: Mon, 03 September 2007 07:38]

Report message to a moderator

Re: update column problem [message #264475 is a reply to message #264462] Mon, 03 September 2007 08:25 Go to previous messageGo to next message
nageswara
Messages: 9
Registered: August 2007
Junior Member
Following is the SQL exec where the
DATA_TYPE :varchar2
DATA_LENGTH:100
CHAR_LENGTH:25 (what is its significance?)

SQL> select DATA_TYPE,DATA_LENGTH,CHAR_LENGTH from all_tab_columns where table_name like'FWT' and column_name like 'TOPIC' and owner like 'NAGESWARA';

DATA_TYPE DATA_LENGTH CHAR_LENGTH
---------------------------------------------------------------------------------------------------------- ----------- -----------
VARCHAR2 100 25
Regards
Nageswara
Re: update column problem [message #264476 is a reply to message #264475] Mon, 03 September 2007 08:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
DATA_LENGTH is the length IN BYTES. What character set are you using?

MHE
Re: update column problem [message #264477 is a reply to message #264456] Mon, 03 September 2007 08:36 Go to previous messageGo to next message
nageswara
Messages: 9
Registered: August 2007
Junior Member
The all_tab_columns.charecter_set_name is CHAR_CS

Regards
Nageswara
Re: update column problem [message #264501 is a reply to message #264477] Mon, 03 September 2007 10:04 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another suggestion: do you have some sort of auditing on the table, storing changes in another table?
Previous Topic: Lock
Next Topic: Comparing values of two columns in same table
Goto Forum:
  


Current Time: Fri Dec 09 19:44:08 CST 2016

Total time taken to generate the page: 0.17975 seconds