Solved

Oracle Pound Sign Puzzle

Posted on 2004-08-19

Oracle Database

2 solutions

1,532 Views

Last Modified: 2011-10-03

Hi,

We have some string data which gets inserted into another table as VARCHAR2(24). This is RPAD'ed to 24 characters and concatonated to another field, defined as VARCHAR2(5), and an attempt is then made to insert it into a final table defined as VARCHAR2(29). All's well until multi-byte characters such as '£' enter the equation, whereby the statement fails with "ORA-01401: inserted value too large for column"

I'd quite like to leave the program alone and ask our DBA to set up the NLS (?) environment to treat £'s as single-byte characters. I've tried setting NLS_LANG on the command line in UNIX but this seems to have no affect. Try inserting a '£' into a VARCHAR2(1) field as a test.

Any ideas?

Comment

Question by:CuthbertDibbleGrub

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

11 Comments

LVL 9

Expert Comment

by:pratikroy

ID: 118421732004-08-19

I dont see a problem :

SQL> create table tab_char (fld1 varchar2(1));

Table created.

SQL> insert into tab_char values ('£');

1 row created.

SQL> select * from tab_char;

F
-
£

What problem do you get when you repeat the above test ? Do you get ORA-01401: inserted value too large for column ?

Expert Comment

by:skuriakose

ID: 118421832004-08-19

Try setting the NLS_CHARACTERSET to some multibyte charset ( UTF-8). By default, the NLS_CHARACTERSET will be single byte and NLS_NCHAR_CHARACTERSET will be multibyte. Find out using

select * from V$nls_parameters;

Author Comment

by:CuthbertDibbleGrub

ID: 118423262004-08-19

Pratikoy: indeed I do get the ORACLE error when inserting with both your and my examples. You must have a different NLS set up than me.

Skuriakose, I will interrogate my V$NLS_PARAMETERS values tomorrow - a useful view - many thanks, hopefully will lead to a solution!

Author Comment

by:CuthbertDibbleGrub

ID: 118424172004-08-19

Here's the NLS parameters...

NLS_LANGUAGE: AMERICAN
NLS_TERRITORY: AMERICA
NLS_CURRENCY: $
NLS_ISO_CURRENCY: AMERICA
NLS_NUMERIC_CHARACTERS: .,
NLS_CALENDAR: GREGORIAN
NLS_DATE_FORMAT: DD-MON-YY
NLS_DATE_LANGUAGE: AMERICAN
NLS_CHARACTERSET: UTF8
NLS_SORT: BINARY
NLS_TIME_FORMAT: HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT: DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT: HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT: DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY: $
NLS_NCHAR_CHARACTERSET: UTF8
NLS_COMP: BINARY

We've had problems changing these as it affects Pro*COBOL / Microfocus COBOL (which will hopefull be surplus to requirements soon!).

LVL 9

Accepted Solution

by:pratikroy

pratikroy earned 75 total points

ID: 118426282004-08-19

These are my NLS parameters, if that helps !

SQL> select * from V$nls_parameters;

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY £
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY ¿
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

19 rows selected.

LVL 29

Expert Comment

by:MikeOM_DBA

ID: 118463552004-08-19

Did you try using NVARCHAR2 instead of VARCHAR2?

LVL 7

Assisted Solution

by:grim_toaster

grim_toaster earned 50 total points

ID: 118496442004-08-19

If you create you table specifying the number of characters, instead of bytes (the default), you can get around some of these issues (there are other problems if dealing with larger columns), and shouldn't affect any of your legacy apps like changing the nls parameters would.

-- The BYTE parameter is not needed, as it is the default, but included for clarity...
CREATE TABLE t1 (byte_varchar VARCHAR2(1 BYTE), char_varchar VARCHAR2(1 CHAR));

-- insert the euro sign into the byte-sized column... This one will fail with your error
INSERT INTO t1 VALUES ('€', NULL);

-- insert the euro sign into the char-sized column... This one will suceed
INSERT INTO t1 VALUES (NULL, '€');

LVL 7

Expert Comment

by:grim_toaster

ID: 118496822004-08-19

The above will only actually work in 9i, but going by your parameter list, you don't have NLS_LENGTH_SEMANTICS, which would indicate that you're not using 9i are you?

LVL 9

Expert Comment

by:pratikroy

ID: 118512582004-08-20

SQL> CREATE TABLE t1 (byte_varchar VARCHAR2(1 BYTE), char_varchar VARCHAR2(1 CHAR));

Table created.

SQL> INSERT INTO t1 VALUES ('.', NULL);

1 row created.

SQL> INSERT INTO t1 VALUES (NULL, '.');

1 row created.

SQL> INSERT INTO t1 VALUES ('£', NULL);

1 row created.

SQL> INSERT INTO t1 VALUES (NULL, '£');

1 row created.

SQL> INSERT INTO t1 VALUES ('$', NULL);

1 row created.

SQL> INSERT INTO t1 VALUES (NULL, '$');

1 row created.

SQL> select * from t1;

B C
- -
.
.
£
£
$
$

6 rows selected.

Author Comment

by:CuthbertDibbleGrub

ID: 118517972004-08-20

Thanks for the great info to aid me on my chosen path - I'm going for a NLS solution as I'm not going to change all the potential scripts and tables affected once 9i arrives. I'll use the provided parameters as a guide and test this out on our replica server before taking the plunge! Points awarded as I'm convinced that I'l succeed eventually, and am adamant that our UK'based RDBMS should be rightfully configured to use GBP by default, instead of it's out of the box state!

LVL 7

Expert Comment

by:grim_toaster

ID: 118678852004-08-23

pratikroy, the reason it all works fine for you is that you are using WE8ISO8859P1 as your character set, whereas CuthbertDibbleGrub has UTF8 set as the character set. The reason I used the euro character is that I have recently had issues with it (being that on a UTF8 database it is 3 bytes), but I had run all of the tests on a UTF8 database (as well as a WE8ISO8859P1 database).

To find the length in bytes of a particular character perform the SQL "SELECT LENGTHB('£') FROM dual;"

On a UTF8 database, you will get
LENGTHB('£')
------------
2

And on a WE8ISO8859P1 database, you will get:
LENGTHB('£')
------------
1

So, it will work fine for you on a WE8ISO8859P1 database, but not on a UTF8...

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to use Oracle (simple) table compression

Article by: Christoffer

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…

Oracle Database

Some Useful Settings in JasperReports Server

Article by: Doug

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.

Copy Users and their Permissions between Databases

Video by: Steve

This video shows how to copy a database user from one database to another user DBMS_METADATA. It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.