Hello The Oracle Masters,
I have the following problem : impossible to use the lettre ù for a column's name but it is OK for a table's name.
Very important : I use Windows 7, in French, and the Oracle 12.1 virtual box with Linux. My principal client is SQL*Plus : Release 12.2.0.1.0.
Ah, one thing, SQL*Plus allow me to create and use a table called ù but not SQL Developer...
Here is the configuration of my database : language AMERICAN and unicode AL16UTF16.
SQL> select parameter, value from v$nls_parameters order by parameter;
PARAMETER VALUE
----------------------------------------------------------------
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
19 rows selected.
Here is the problem. I create a table with a column named ù BUT after that, it is impossible to use this name, even with "".
SQL> CREATE TABLE testaccent (ù VARCHAR2(10));
Table created.
SQL> desc testaccent
Name Null? Type
---------------- ---------------------
?? VARCHAR2(10)
SELECT COLUMN_NAME from user_tab_cols where TABLE_NAME = 'TESTACCENT';
COLUMN_NAME
------------
??
Same error with SQL*Plus and SQL Developer.
SQL> select ù from test02;
select ù from test02
*
ERROR at line 1:
ORA-00904: "??": invalid identifier
SQL> select "ù" from test02;
select "ù" from test02
*
ERROR at line 1:
ORA-00904: "??": invalid identifier
SQL> SELECT ?? FROM testaccent;
SELECT ?? FROM testaccent
*
ERROR at line 1:
ORA-00911: invalid character
SQL> SELECT "??" FROM testaccent;
ERROR at line 1:
ORA-00904: "??": invalid identifier
Well, I thought the character ù wasn't recognize until I create a table called ù.
SQL> CREATE TABLE ù (ID NUMBER);
Table created.The table's name is also ??, like before for the column's name.
SQL> select table_name from user_tables order by 1;
TABLE_NAME
--------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
TESTACCENT
??And, surprisingly, I can use the letter ù to identifie this table but only with SQL*Plus.
SQL*Plus
SQL> insert into ù values (1);
1 row created.
SQL Developer
SQL> insert into ù values (1);
Error starting at line : 1 in command -
insert into ù values (1)
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action
SQL> commit;
Commit complete.
SQL*Plus : OK
SQL> select * from ù;
ID
----------
1
SQL Developer : KO.
SQL> select * from ù;
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 1 Column: 15
Can you explain us why there is this difference between a table's name and a column's name?
Thank you for your answer and have a very good day,
David D.
[UPDATE 17/08/2018]
Hi Sergiusz,
I have done all the required test in SQL Developer and the results are OK.
BUT, I am very confusing, I had two days ago a problem with my database and I had to drop it and recreate it. And now I have no problem with SQL*Plus. So, I think it was a configuration problem with my last database.
Test with SQL Developer.
create table ù (ù varchar2(10));
Table Ù created.
select ù from ù;
no rows selected
drop table ù purge.
Table dropped.
Test with SQL*Plus.
create table ù (ù varchar2(10));
Table Ù created.
select ù from ù;
no rows selected
drop table ù purge.
Table dropped.
Best regards,
David
Generally, if you test with SQL Developer and an AL32UTF8 Unicode database, then everything will work correctly, because SQL Developer works internally in Unicode and no special configuration is usually needed for most languages (some font configuration may be needed for more "exotic" languages).
SQL*Plus, on the other hand, is problematic. On Windows, it is written to Win32 ANSI API. When your run it in a Command Prompt window, this window works in the ANSI or OEM code page that you can check with the CHCP command. On French Windows, it will be the OEM code page 850 by default (Oracle name: WE8PC850). You have to let SQL*Plus know this code page by setting the NLS_LANG environment variable appropriately. By default, SQL*Plus sees the NLS_LANG setting from Registry, which is usually .WE8MSWIN1252 (ANSI code page 1252). WE8MSWIN1252 is not the same as WE8PC850, and this is why you see the problems -- 'ù' is 0xF9 in WE8MSWIN1252 but 0x97 in WE8PC850. Do not get deceived by the fact that you see the letter correctly on the screen. The database may see it as a different character. For example, if the code 0x97 received for 'ù' from the Command Prompt by SQL*Plus is interpreted in WE8MSWIN1252 (as dictated by the Registry setting), the database will think it is the em-dash ('—') character and not accept it as an unquoted identifier.
You have to make sure the code page of Command Prompt agrees with the NLS_LANG value seen by SQL*Plus. You can do this either by setting NLS_LANG before running SQL*Plus:
C:\>chcp
Active code page: 850
C:\>set NLS_LANG=FRENCH_FRANCE.WE8PC850
C:\>sqlplus /nolog
or by setting the Command Prompt code page as set in Registry:
C:\>sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 1 00:19:36 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_AMERICA.WE8MSWIN1252"
SQL> exit
C:\> chcp 1252
Active code page: 1252
C:\>sqlplus /nolog
Do not modify Registry to .WE8PC850 or set the environment variable in system properties permanently, because these setting are used by GUI applications as well and Windows GUI runs in code page 1252, not 850. Preferably, start SQL*Plus via a batch file that sets the NLS_LANG environment variable temporarily. Or, just use SQL Developer.