Discussion :

[Ikebukuro]

Hello amis DBA,

Pour mes tests j'ai créée une table avec un nom de colonne contenant des caractères spéciaux.
Problème : impossible de référencer la colonne.
Plus fort : les colonnes semblent être traitée différemment que les tables car une table avec le même nom peut être interrogée.

La création de la colonne avec des guillemets est OK.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> CREATE TABLE test02 ("a$^ù*$" VARCHAR2(10));
Table created.
 
 
SQL> desc  test02
Name			Null?	Type
------------------------------------
a$^??*$					VARCHAR2(10)
 
 
SQL> SELECT * FROM test02;
a$^??*$
----------
TEST
 
 
SQL> SELECT COLUMN_NAME from dba_tab_cols where TABLE_NAME = 'TEST02' and owner = 'HR'; 
COLUMN_NAME
----------------------------------------------------------------------------------------------------
a$^??*$

Interrogation KO.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SQL> select a$^??*$ from test02;
select a$^??*$ from test02
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
 
 
SQL> select "a$^??*$" from test02;
select "a$^??*$" from test02
       *
ERROR at line 1:
ORA-00904: "a$^??*$": invalid identifier

En PL/SQL c'est pas mieux...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> DECLARE
	v_col_name VARCHAR2(128);
	v_select VARCHAR2(128);
BEGIN
	SELECT COLUMN_NAME into v_col_name from dba_tab_cols where TABLE_NAME = 'TEST02' and owner = 'HR';
	v_select := 'SELECT "' || v_col_name || '" FROM test02';
	dbms_output.put_line(v_select);
	EXECUTE IMMEDIATE 'SELECT ' || v_col_name || ' FROM test02';
END;
/  
 
SELECT "a$^??*$" FROM test02
DECLARE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at line 8

En revanche, pour une table de même nom, pas de problème pour utiliser les guillemets.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SQL> CREATE TABLE "a$^??*$" ( ID NUMBER);
Table created.
 
 
SQL> desc "a$^??*$"
Name						       Null?	Type
----------------------------------------------------- -------- ------------------------------------
ID								NUMBER
 
 
SQL> select * from "a$^??*$";
no rows selected

Une aide serait bienvenue

[McM]

Euh, chez moi ça fonctionne.. mais tu changes de nom de colonne entre tes tests.. j'ai l'impression que tu as merdouillé dans tes tests

CREATE TABLE test02 ("a$^ù*$" VARCHAR2(10));
Table created.

SQL> desc test02
Name Null? Type
------------------------------------
a$^??*$ VARCHAR2(10)

[Ikebukuro]

Oh putaing... j'ai du faire un mauvais copier/coller...
Toutes mes excuses

Ah non, c'est le ù qui pose problème : il a été transformé en ??

[McM]

C'est marrant, moi j'ai pas eu ce problème ! Mais bon, j'ai pas testé avec sqlplus

[Ikebukuro]

Quelque chose me dit que je suis tombé sur le classique du classique : pb de character set entre le client et la base de données

Finalement c'est OK si je saisi ù au lieu de ??

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SQL> select "a$^ù*$" from test02;
a$^??*$
----------
TEST

Maintenant cela signifie que si je fais un SELECT dans DBA_TAB_COLS, le nom de colonne stocké est faux... houlalalalalala, ça va loin tout ça.

[Pomalaix]

Comme ça on en revient au bon vieux conseil depuis au moins 30 ans :
n'utiliser que les lettres de A à Z, sans accent bien sûr, pour nommer les objets Oracle.

[SQLpro]

Comme ça on en revient au bon vieux conseil depuis au moins 30 ans :
n'utiliser que les lettres de A à Z, sans accent bien sûr, pour nommer les objets Oracle.

Mais aussi les chiffres de 0 à 9 et le blanc souligné, et aussi ne jamais commencer par un chiffre (norme SQL)

Enfin, éviter les noms de colonnes qui sont un mot clef de SQL comme :
TYPE,
DATE,
PERCENT,
BEGIN,
END,
CASE,
CREATE,
CUBE
...

A +

[Ikebukuro]

Je relance le débat :-) car j'ai l'impression que Oracle ne gère pas de la même façon les noms des tables et des colonnes.
Et cette fois mes tests sont un peu plus simples que ceux précédents.

Mon PC est en français, avec WIndows 7.
Je passe par la virtual box d'Oracle avec un Linux Red Hat en anglais.
Tous mes tests sont faits sous SQL*Plus.

Ma base est en américain.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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.

Une table avec un nom avec accent est géré correctement, même si le nom de la table est ?? dans le dictionnaire de données. Pourtant ù est connu du character set AL16UTF16.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> CREATE TABLE ù (ID NUMBER);
Table created.
 
SQL> select table_name from user_tables order by 1;
TABLE_NAME
--------------
...
COUNTRIES
??
 
SQL> desc ù
Name        Null?    Type
-------------------------------
ID                    NUMBER
 
SQL> insert into ù values (1);
1 row created.
 
SQL> commit;
Commit complete.
 
SQL> select * from ù;
ID
----------
1

Mais si je mets un accent dans un nom de colonne, alors là c'est la catastrophe, impossible de référencer cette colonne!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> CREATE TABLE testaccent (ù VARCHAR2(10));
Table created.
 
SQL> desc testaccent
Name            Null?   Type
---------------- ---------------------
??                        VARCHAR2(10)	 
 
SQL> insert into testaccent values ('TEST');
1 row created.
 
SQL> commit;
Commit complete.
 
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

Je suis OK avec Pomalaix, qui veut jouer au plus fin finit comme un idiot : n'utilisez que des caractères américains pour les noms d'objets et les caractères spéciaux validés par Oracle.
Néanmoins cette histoire me turlupine, c'est pourquoi je demande votre aide : soit j'ai un pb de configuration (lequel?) soit Oracle gère différemment les noms des colonnes et des tables; et si oui, pourquoi?

Bon wekk-end à tous!

[Pomalaix]

Tout ça marche très bien, mais il faut prendre le soin de définir convenablement NLS_LANG avant de lancer SQL*Plus.
Par ailleurs, ne pas oublier que les noms d'objets/colonnes sont enregistrés en majuscules dans le dictionnaire, à moins que vous les ayez encadrés par des guillemets à la création.
Donc le ù sera transformé en Ù, ce qu'on voit d'ailleurs dans le DESC.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

set NLS_LANG=FRENCH_FRANCE.WE8PC850

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> CREATE TABLE ù (ID NUMBER);
Table créée.
 
SQL> select table_name from user_tables where table_name='Ù';
 
TABLE_NAME
------------------------------
Ù
 
 
SQL> desc ù
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 
 
SQL> insert into ù values (1);
 
1 ligne créée.
 
 
SQL> select * from ù;
 
        ID
----------
         1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> CREATE TABLE testaccent (ù VARCHAR2(10));
 
 
Table créée.
 
 
SQL> desc testaccent
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 Ù                                                  VARCHAR2(10)
 
 
SQL> insert into testaccent values ('TEST');
 
 
1 ligne créée.
 
 
SQL> select ù from testaccent;
 
 
Ù
----------
TEST

[Pomalaix]

Il est utile de préciser aussi que dans ce qui précède, j'ai tapé (ou copié/collé) les commandes interactivement dans SQL*Plus.

Maintenant, si on veut mettre tout ça dans un script et l'exécuter par @, c'est un cas différent.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
set echo on
spool c:\temp\test.log
 
drop table ù;
CREATE TABLE ù (ID NUMBER);
select table_name from user_tables where table_name='Ù';
desc ù
insert into ù values (1);
select * from ù;
 
spool off

Il faut définir NLS_LANG selon l'encodage du fichier, et non plus selon le jeu de caractères de la fenêtre SQL*Plus.
Dans mon cas, Notepad++ m'indique que j'ai un encodage UTF-8.

1. Si je garde NLS_LANG avec WE8PC850

J'ai des erreurs d'exécution à cause d'une mauvaise conversion, et les caractères spéciaux ne sont pas lisibles

Sortie à l'écran

set NLS_LANG=FRENCH_FRANCE.WE8PC850

sqlplus scott/tiger @c:\temp\test.sql

SQL> spool c:\temp\test.log
SQL>
SQL> drop table ├╣;
drop table ├╣
*
ERREUR à la ligne 1 :
ORA-00911: caractère non valide

SQL> CREATE TABLE ├╣ (ID NUMBER);
CREATE TABLE ├╣ (ID NUMBER)
*
ERREUR à la ligne 1 :
ORA-00911: caractère non valide

SQL> select table_name from user_tables where table_name='Ù';

aucune ligne sélectionnée

SQL> desc ├╣
SP2-0565: Identificateur erroné.
SQL> insert into ├╣ values (1);
insert into ├╣ values (1)
*
ERREUR à la ligne 1 :
ORA-00911: caractère non valide

SQL> select * from ├╣;
select * from ├╣
*
ERREUR à la ligne 1 :
ORA-00911: caractère non valide

SQL>
SQL> spool off

Contenu du spool :

SQL>
SQL> drop table ù;
drop table ù
*
ERREUR … la ligne 1 :
ORA-00911: caractŠre non valide

SQL> CREATE TABLE ù (ID NUMBER);
CREATE TABLE ù (ID NUMBER)
*
ERREUR … la ligne 1 :
ORA-00911: caractŠre non valide

SQL> select table_name from user_tables where table_name='Ù';

aucune ligne s‚lectionn‚e

SQL> desc ù
SP2-0565: Identificateur erron‚.
SQL> insert into ù values (1);
insert into ù values (1)
*
ERREUR … la ligne 1 :
ORA-00911: caractŠre non valide

SQL> select * from ù;
select * from ù
*
ERREUR … la ligne 1 :
ORA-00911: caractŠre non valide

SQL>
SQL> spool off

Si je mets NLS_LANG à UTF8

Il n'y a plus d'erreur d'exécution bien que les caractères spéciaux soient illisibles à l'écran

set NLS_LANG=FRENCH_FRANCE.UTF8
sqlplus scott/tiger @c:\temp\test.sql


SQL> spool c:\temp\test.log
SQL>
SQL> drop table ├╣;
Table supprim├®e.


SQL> CREATE TABLE ├╣ (ID NUMBER);
Table cr├®├®e.
SQL> select table_name from user_tables where table_name='Ù';
TABLE_NAME
--------------------------------------------------------------------------------
Ù
SQL> desc ├╣
Nom NULL ? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> insert into ├╣ values (1);
1 ligne cr├®├®e.
SQL> select * from ├╣;
ID
----------
1
SQL>
SQL> spool off

En revanche, le SPOOL est parfaitement propre

SQL> drop table ù;
Table supprimée.


SQL> CREATE TABLE ù (ID NUMBER);
Table créée.


SQL> select table_name from user_tables where table_name='Ù';
TABLE_NAME
--------------------------------------------------------------------------------
Ù


SQL> desc ù
Nom NULL ? Type
----------------------------------------- -------- ----------------------------
ID NUMBER


SQL> insert into ù values (1);
1 ligne créée.
SQL> select * from ù;


ID
----------
1


SQL>
SQL> spool off

[Ikebukuro]

Merci pour tes réponses Pomalaix, ces histoires de NLS ne sont pas simples

[SQLpro]

ça date pas d’aujourd’hui l'imbitabilité du NLS....
Heureusement Oracle s'est enfin conformé à la norme SQL depuis la version 12c release 2 en adoptant les collations, après avoir faire croire pendant plus de 30 ans, que NLS était bien mieux que le principe des collations !!!!!

Encore une fois tu auras aussi d'autres difficultés avec les programmes clients qui peuvent travailler avec d'autres jeux de caractères et donc te retrouver coincé pour porter tes tables dans les applications !

CONCLUSION : respecter la norme SQL !

A +