Home » SQL & PL/SQL » SQL & PL/SQL » Error inserting special characters to Oracle (merged)
Error inserting special characters to Oracle (merged) [message #391179] Wed, 11 March 2009 06:41 Go to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Hi All,

I am facing some strange problem while inserting data to oracle table.

i am inserting data from one database to other database using dblink, while for particular string it is giving following error.

Quote:
ORA-12899: value too large for column "Schema_Name"."Table_Name" (actual: 92, maximum: 90)


In source table the length defined as Varchar2(90) and the same in
target table i have taken the same length.

While checking the size of the string which is giving 90 only...

SELECT LENGTH('KOORDINIERUNGSSTELLE FÃ?R VERWALTUNGSAUTOMATION DER FACHHOCHSCHULEN DES LANDES BADEN-WÃ?RT') LEN FROM DUAL


but not able to insert the same, i tried searching for the same error in previous threads but i am not able to find out.

Please help me out by giving some ideas.

Thanks in advance..



Re: Error inserting special characters to Oracle [message #391183 is a reply to message #391179] Wed, 11 March 2009 06:59 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SQL> create table test(n varchar2(1));

Table created.

SQL> insert into test values ('a');

1 row created.

SQL> insert into test values ('aa');
insert into test values ('aa')
*
ERROR at line 1:
ORA-12899: value too large for column "ME"."TEST"."N" (actual: 2, maximum: 1)



It can happen that the character you are trying to insert looks like there should be no problem:


SQL> desc test
Name Null? Type
----------------------------------------- -------- -----------
N VARCHAR2(1)

SQL> insert into test values ('?');
insert into test values ('?')
*
ERROR at line 1:
ORA-12899: value too large for column "ME"."TEST"."N" (actual: 3, maximum: 1)



This can happen due to the characterset the database is running in.

In a AL32UTF8 (unicode) database, some characters take more then 1 byte.

If you do not specify at create time what unit the size for a column is in, default it means the amount of bytes.

In our example, the ? will take 3 bytes due to the fact we are in a Unicode database and will cause the error.


SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8


We can fix this by telling that the size is in CHAR's instead of BYTE's:


SQL> alter table test modify(n varchar2(1 char));

Table altered.

SQL> desc test
Name Null? Type
----------------------------------------- -------- -----------------
N VARCHAR2(1 CHAR)

SQL> insert into test values ('?');

1 row created.
Re: Error inserting special characters to Oracle [message #391185 is a reply to message #391179] Wed, 11 March 2009 07:10 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Hi Rajasekhar,

Thanks for the quick reply,,,

i have changed my data type from Varchar2(90 Byte) to Varchar2(90 CHAR)..

Now i can able to insert the data now...

Thanks a lot ,,

Regards,
Maruthi Narra
Re: Error inserting special characters to Oracle [message #391241 is a reply to message #391183] Wed, 11 March 2009 09:33 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@rajasekhar857,

Appreciate you helping the OP with answer. But in future please don't fake the outputs of your query. Your example did make me pause my work for a moment though and made me think I had done some huge mistakes in my codings including '?' in varchar2(1) columns.

SQL> SELECT VALUE
  2    FROM nls_database_parameters
  3   WHERE parameter = 'NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8

SQL> drop table test_tab;

Table dropped.

SQL>  CREATE TABLE test_tab
  2   (col_a VARCHAR2(1 BYTE));

Table created.

SQL> INSERT INTO TEST_TAB VALUES('?');

1 row created.



Your reasoning was correct though but if you see OP checked the length and found it was 90 in both source and target. Thats where he was wrong. It was varchar2(90 CHAR) in Source and must have been Varchar2(90 BYTE) in the target as you rightfully pointed out.

Quote:

While checking the size of the string which is giving 90 only...
SELECT LENGTH('KOORDINIERUNGSSTELLE FÃ?R VERWALTUNGSAUTOMATION DER FACHHOCHSCHULEN DES LANDES BADEN-WÃ?RT') LEN FROM DUAL




Now, hope the following queries help you understand what OP misunderstood.
SQL> SELECT LENGTH
  2            ('KOORDINIERUNGSSTELLE FÃ?R VERWALTUNGSAUTOMATION DER FACHHOCHSCHULEN DES LANDES BADE
N-WÃ?RT'
  3            ) len
  4    FROM DUAL;

       LEN
----------
        90

SQL> SELECT LENGTHB
  2            ('KOORDINIERUNGSSTELLE FÃ?R VERWALTUNGSAUTOMATION DER FACHHOCHSCHULEN DES LANDES BADE
N-WÃ?RT'
  3            ) len
  4    FROM DUAL;

       LEN
----------
        92

SQL> SELECT LENGTH('?') FROM Dual;

LENGTH('?')
-----------
          1

SQL> SELECT LENGTHB('?') FROM Dual;

LENGTHB('?')
------------
           1


SQL>  SELECT LENGTH('Ã') FROM Dual;

LENGTH('Ã')
-----------
          1

SQL> SELECT LENGTHB('Ã') FROM Dual;

LENGTHB('Ã')
------------
           2

SQL>  INSERT INTO TEST_TAB VALUES('Ã');
 INSERT INTO TEST_TAB VALUES('Ã')
                             *
ERROR at line 1:
ORA-12899: value too large for column "FLABS_SAFETYMART25"."TEST_TAB"."COL_A"
(actual: 2, maximum: 1)



Hope you get the idea. Apologies if I misread anything in your post.

Regards,
Jo

[Updated on: Wed, 11 March 2009 09:36]

Report message to a moderator

Previous Topic: How to call Tibco webservice in PL/SQL
Next Topic: To print the column value and a string
Goto Forum:
  


Current Time: Tue Dec 06 00:31:39 CST 2016

Total time taken to generate the page: 0.14770 seconds