Solved

Oracle Pound Sign Puzzle

Posted on 2004-08-19
11
1,497 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?
0
Comment
Question by:CuthbertDibbleGrub
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 9

Expert Comment

by:pratikroy
ID: 11842173
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 ?
0
 

Expert Comment

by:skuriakose
ID: 11842183
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;
0
 

Author Comment

by:CuthbertDibbleGrub
ID: 11842326
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!
0
 

Author Comment

by:CuthbertDibbleGrub
ID: 11842417
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!).                                                              
0
 
LVL 9

Accepted Solution

by:
pratikroy earned 75 total points
ID: 11842628
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11846355

Did you try using NVARCHAR2 instead of VARCHAR2?
0
 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 50 total points
ID: 11849644
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, '€');
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 11849682
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?
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11851258
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.
0
 

Author Comment

by:CuthbertDibbleGrub
ID: 11851797
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!
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 11867885
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...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now