Home » SQL & PL/SQL » SQL & PL/SQL » Error in updating column (10g, CENT OS)
Error in updating column [message #404990] Tue, 26 May 2009 00:29 Go to next message
v_zaitsev
Messages: 12
Registered: May 2009
Location: Manila
Junior Member
I was experiencing a weird problem with regards to updating two identical tables from 2 different databases. The columns are VARCHAR2(65) data type. The update statement has a length of 65 characters however one of the character is a special character which is a symbol (degrees symbol i think) when I ran the update statement from one of the database it ran successfully however when i ran it to the other database it returned an error of maximum length of characters exceeded. I'm thinking that the special is a dual byte but how come it cannot ran the statement successfully to the other database. What should i check here? database configurations? cofig files? I really don't know where to check. Any form of help would be greatly appreciated. Thanks.

[Updated on: Tue, 26 May 2009 00:35]

Report message to a moderator

Re: Error in updating column [message #404993 is a reply to message #404990] Tue, 26 May 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without any information about both Oracle versions (with 4 decimals), data, nls parameters, table defintions and so on how could we know what happened?

Copy and paste all these informations as well as your SQL*Plus session (don't use other thing).
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Error in updating column [message #405007 is a reply to message #404990] Tue, 26 May 2009 01:13 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@v_zaitsev,

If I had to take a guess i would say one database might have the column datatype as VARCHAR2(65 Byte) and the other one as VARCHAR2(65 CHAR). The one with VARCHAR2(65 Byte) will be the one that is giving you problems with your scenario i guess.

Check the column definition in both the databases.

Regards,
Jo
Re: Error in updating column [message #405009 is a reply to message #404990] Tue, 26 May 2009 01:14 Go to previous messageGo to next message
v_zaitsev
Messages: 12
Registered: May 2009
Location: Manila
Junior Member
Sorry about that. Below are the details:

DB where insert is error:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

DB where insert is successfull:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Update Statement:

UPDATE CI_PER_NAME set ENTITY_NAME = 'CONDOMÍNIO DO PRÉDIO SITO NA AV. ANTÓNIO AUGUSTO AGUIAR Nº 29'
WHERE PER_ID = '3212513423';


Error:
*
ERROR at line 1:
ORA-12899: value too large for column "CISADM"."CI_PER_NAME"."ENTITY_NAME"
(actual: 69, maximum: 64)


Does it have something to do with
NLS_CHARACTERSET?

[Updated on: Tue, 26 May 2009 01:16]

Report message to a moderator

Re: Error in updating column [message #405011 is a reply to message #405009] Tue, 26 May 2009 01:19 Go to previous messageGo to next message
v_zaitsev
Messages: 12
Registered: May 2009
Location: Manila
Junior Member
@joicejohn

It was VARCHAR2(64) sorry for the confusion. Both columns were declared as VARCHAR2(64). Checked it many times already.
Re: Error in updating column [message #405015 is a reply to message #405009] Tue, 26 May 2009 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't follow the guidelines I pointed you.
You didn't post what I asked.
First do this then have my help.

Regards
Michel
Re: Error in updating column [message #405018 is a reply to message #405011] Tue, 26 May 2009 01:37 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@v_zaitsev,

Well that's my question VARCHAR2(64 Bytes) or VARCHAR2(64 Char) Which one?

Hope the following illustration helps:
SQL> DROP TABLE TEST_TAB;
DROP TABLE TEST_TAB
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE test_tab
  2  (var_1 VARCHAR2(64 BYTE)
  3  )
  4  /

Table created.

SQL> INSERT INTO test_tab
  2              (var_1
  3              )
  4       VALUES ('CONDOMÍNIO DO PRÉDIO SITO NA AV. ANTÓNIO AUGUSTO AGUIAR Nº 29
'
  5              );
     VALUES ('CONDOMÍNIO DO PRÉDIO SITO NA AV. ANTÓNIO AUGUSTO AGUIAR Nº 29'
             *
ERROR at line 4:
ORA-12899: value too large for column "SCOTT"."TEST_TAB"."VAR_1" (actual: 65,
maximum: 64)

SQL> DROP TABLE TEST_TAB;

Table dropped.

SQL> CREATE TABLE test_tab
  2  (var_1 VARCHAR2(64 CHAR)
  3  )
  4  /

Table created.

SQL> INSERT INTO test_tab
  2              (var_1
  3              )
  4       VALUES ('CONDOMÍNIO DO PRÉDIO SITO NA AV. ANTÓNIO AUGUSTO AGUIAR Nº 29
'
  5              )
  6  /

1 row created.

SQL>


Regards,
Jo
Re: Error in updating column [message #405023 is a reply to message #404990] Tue, 26 May 2009 01:48 Go to previous message
v_zaitsev
Messages: 12
Registered: May 2009
Location: Manila
Junior Member
@michael

sorry next time i'll post i will follow rules already

@joicejohn

Thank you very much. I've tried what you posted now I know the difference. I think the NLS_CHARACTERSET also affects the data being updated. Have to do some minor adjustments. Thanks for the help. Very Happy
Previous Topic: External tables unable to load special chars from csv file
Next Topic: how to avoid maximum open cursors exceeded
Goto Forum:
  


Current Time: Sun Dec 11 07:53:35 CST 2016

Total time taken to generate the page: 0.06437 seconds