Discussion :

[jrman]

Bonjour,

Oracle 9.2.0
Soit une table et une valeur :

Code :

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

1
2
create table ma_table (col varchar2(5));
insert into ma_table values ('toto');

et 2 requetes :

Code :

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

select * from ma_table where col = 'toto'

qui marche.

Code :

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

select * from ma_table where col = 'toto '

<= avec un espace !
qui ne marche pas evidemment avec une configuration Oracle par defaut.

Existe t'il un mecanisme ou un parametrage de la base qui permettrait de faire fonctionner ma seconde requete de facon transparente pour elle ?

J'ai lu ce qu'explique SQLPro concernant les collations, je me disais que ce mecanisme pourrais peut etre etre une solution

Merci,
JR

[SheikYerbouti]

select * from ma_table where col LIKE 'toto%'

ou

select * from ma_table where TRIM(col) = 'toto'

[jrman]

Oui si je modifie ma requete j'ai plein de solution.
Je connais le sql, moins le fichier init.ora
Y a t il un moyen de faire fonctionner ma requete sans la changer via une configuration particuliere de mon instance ?

Merci,
JR

[Pomalaix]

Bonjour

Merci de penser à utiliser les balises adaptées pour formater votre code, conformément à la tradition du forum !

Je vois 3 solutions pour résoudre votre problème.

1) Nettoyer les données en amont.
Est-il normal que vous ayez des espaces dans vos données ?
Si c'est non, alors il faudrait éliminer les espaces lors de l'insertion ou de la modification des données, grâce à un déclencheur par exemple.
Pour les données existantes, les fonctions TRIM/RTRIM/LTRIM vous seront utiles.

2) Utiliser des CHAR au lieu des VARCHAR2.
Cette solution convient idéalement à des chaînes qui n'auraient des espaces qu'à droite, et qui seraient toutes plus ou moins de la même longueur (pour ne pas perdre trop d'espace).
En effet, dans un CHAR, les espaces de droite sont ignorés quand on fait un WHERE =.

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE TEST(a CHAR(10));
INSERT INTO TEST VALUES('toto');
INSERT INTO TEST VALUES('toto ');
 
SELECT * FROM TEST WHERE a='toto';
--> 
toto
toto

3) Si les espaces doivent être conservés (et que vous êtes au moins en 8i), vous pouvez créer un index fonctionnel sur la colonne VARCHAR2.

Code :

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

CREATE INDEX F_RTRIM ON TEST(rtrim(a));

Ensuite, la recherche ne se fait plus par

Code :

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

SELECT * FROM TEST WHERE a='toto';

mais par

Code :

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

SELECT * FROM TEST WHERE rtrim(a)='toto';

L'avantage de l'index fonctionnel, c'est que cette recherche sera aussi rapide que si la donnée avait été stockée sans les espaces.

[jrman]

J'ai corrige mon post

L'index fonctionnel me semble tres interessant dans la mesure ou d'apres les ecrits de SQLPro le mecanisme de collation est tres couteux, cela dit je crois qu'il ne pourrait pas repondre a mon probleme

Ces espaces proviennent du Pro*C.
Les valeurs sont recuperes dans des types char UNIX.
Migrer ces types vers du varchar + rajouter la gestion du \0 de fin de chaine serait trop de travail.
Donc les chaines ne seront pas nettoyees, a priori ..., (bien que le surplus d'espace occupe par la transmission de ces espaces risque de s'averer embettant et que cette position sera peut etre revue).

Donc l'index pourrait repondre a mon besoin.
J'ai une question qui me vient suite a cela ...

J'ai essaye de faire moi meme le test mais je n'ai pas assez de privilege. . Cela me surprend que je ne puisse pas creer un index sur une colonne d'une table m'appartenent mais bon admettons

Est ce que la mise a jour de la colonne met a jour en meme temps l'index ou dois je le reconstruire ?
Cet a dire que si j'update 'toto ' en 'titi ' mon index sera remis a jour ?
et que des lors

select * from ma_table where col = 'titi '

marchera :

Merci,
JR

[SheikYerbouti]

heureusement que votre index est mis à jour en même temps que la table (en admettant que la colonne soit effectivement indexée) !

sinon, à quoi servirait-il :

[Pomalaix]

Donc les chaines ne seront pas nettoyees, a priori ..., (bien que le surplus d'espace occupe par la transmission de ces espaces risque de s'averer embettant et que cette position sera peut etre revue).

Si les espaces ne servent à rien, pourquoi refuser de les supprimer ??
Comme je disais plus haut, une mise à jour globale pour l'existant

Code :

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

UPDATE latable SET lacolonne=TRIM(lacolonne);

et un déclencheur pour l'avenir, et le tour est joué.

Donc l'index pourrait repondre a mon besoin.
J'ai une question qui me vient suite a cela ...

Est ce que la mise a jour de la colonne met a jour en meme temps l'index ou dois je le reconstruire ?
Cet a dire que si j'update 'toto ' en 'titi ' mon index sera remis a jour ?

Oui !

et que des lors

select * from ma_table where col = 'titi '

marchera :

Non !
Comme j'ai essayé de l'expliquer plus haut, l'index fonctionnel vous assurera des performances améliorées, mais ne vous permettra pas de faire "where col='titi'. Il faudra faire "where rtrim(col)='titi'.

Sur les conditions d'utilisation des index fonctionnels, voir mon message en fin de ce sujet.

[jrman]

Si les espaces ne servent à rien, pourquoi refuser de les supprimer ??
Comme je disais plus haut, une mise à jour globale pour l'existant

Excusez moi, je m'apercois que pour la solution 3 on s'ecarte de mon probleme.
Mon probleme d'espace n'est pas sur la colonne de la table mais sur la valeur de recherche.

La solution 2 est envisageable mais risque d'entrainer un surplus de stockage cela dit je crois que c'est encore la mieux sans changer le code.
Par contre c'est pas tres propre, je crains que cela ne passe pas bien chez mes decideurs

Pour la 1
Ma contrainte principal est que je ne peux pas modifie ma requete, c'est pour cela que quant vous avez ecrit que des lors la recherche se fait par

SELECT * FROM TEST WHERE rtrim(a)='toto';

J'ai cru que vous parliez de ce que faisait Oracle de facon induite du a la presence de l'index et non en changeant ma requete.
Je ne sais pas si j'ai ete clair mais bon en fait j'avais pas bien compris du fait qu'on ne parlait plus de mon probleme.

Je parle de milliers de ligne de code SQL a retoucher donc c'est pour cela que je ne voudrais pas modifier mes requetes.

Donc sans changer mon code je ne m'en sors pas de facon raisonnable en terme de cout de stockage : et d'un stockage qui plus est pas joli a regarder ...

Merci,
JR

[Pomalaix]

Mon probleme d'espace n'est pas sur la colonne de la table mais sur la valeur de recherche.

Oups, j'avais bien sûr compris l'inverse !!!

La solution 2 est envisageable mais risque d'entrainer un surplus de stockage cela dit je crois que c'est encore la mieux sans changer le code.
Par contre c'est pas tres propre, je crains que cela ne passe pas bien chez mes decideurs

Effectivement, ça peut marcher. Le plus marrant, c'est que si j'avais compris votre question dès le départ (il suffisait de lire bêtement ce qui était écrit ), je n'aurais pas pensé à cette solution...

[jrman]

Je vous remercie tous les deux d'avoir passe du temps sur mon soucis
Je ne sais pas quelle solution va etre adoptee mais je crains qu'il n'y ait du remaniement de code dans l'air ca va refroidir du monde ...
Car prendre le type Oracle char au lieu de varchar2 je le sens pas, enfin ce sera discute ...

Pour info le char Unisys est egal au varchar UNIX + la gestion du \0 juste apres la valeur significative.

En fait le embedded SQL Unisys travaille plus que le Embedded SQL Oracle
du coup va falloir adapter le code avec ce qu'il manque je le crains ...

Merci,
JR

[jrman]

Bonjour,

Mon probleme a ete resolu par la modification du makefile de compilation Pro*C.

En effet il suffit de modifier le comportement de pro*C vis a vis des host variables et de lui demander de ne pas completer par des espaces.
Cela se passe dans la variable de makefile:

PROCFLAGS= CODE=ANSI_C CHAR_MAP=STRING SQLCHECK= blablabla

Soit la ligne de code suivante:

EXEC SQL BEGIN DECLARE SECTION;
char variable1[10];
EXEC SQL END DECLARE SECTION;
...
EXEC SQL select colonne1 into :variable1 from toto;

Si la colonne1 de type Oracle char(10) contient 'abcde.....' ou varchar2(10) contient 'abcde'

De base proc va inserer dans variable1 la valeur 'abcde.....\0'.
La etait mon probleme car si je reutilisais cette valeur dans une jointure, je ne trouvais rien du tout.
Avec l'option de compile (ci-dessus), proc va inserrer 'abcde\0'
Des lors plus d'espace en trop a la fin, la valeur recuperee peut etre reutilisee dans une jointure ou transmise a l'appelant sans vilains espaces.
C'est cool car y'a aucune ligne de code C ou SQL a changer.

a+
jrman

[isa06]

Bonjour
Nous avons mis en place la solution donnée ci-avant par jrman. Ca marche nickel pour les varchar2, mais maintenant on a des pbs avec les char, lorsqu'on utilise des host-variables.

Voila un petit résumé :

on a dans une table une colonne char(7) contenant 'TOTO' (on est d'accord, c'est padde donc il y a 'TOTO' suivi de 3 blancs )

1- sous sqlplus, select * from table where col='TOTO' renvoie la ligne

2- dans un programme C++, on génère une requete statique

select * from table where col='TOTO'

l'exécution marche : on récupère notre ligne

3- toujours en C++, on génère une requete avec host variable

select * from table where col=:hv1

ou l'on met 'TOTO' dans hv1

l'exécution renvoie "no rows selected" - la ligne n'est pas trouvée

Y a-t-il quelque chose de particulier avec les host-variables ?
NB : on traite de la meme facon les char et les varchar2...

Merci d'avance

Isa

[jrman]

Bonjour,
Je constate le meme comportement avec notre code Pro*C.
Je n'ai pas eu le temps de chercher le pourquoi du comment et le remede s'il existe.
Pour info, pourquoi utilisez vous des types char?
Si vous trouvez, merci de nous faire part de la solution qui permet de ne pas filler la valeur de recherche avec des espaces.
Je pense qu'il a un soucis avec la fin de la chaine, et du coup peut etre que les collations seraient une piste mais bon ...
a+
jrman

[isa06]

Bonjour

On n'a pas resolu le probleme, je crois que les developpeurs ont enlevé l'option de precomp.
En ce qui concerne l'utilisation de char, on a conservé en char toutes les colonnes clés ou servant de critere de recherche/jointure (avec les varchar, si on saisit des blancs par erreur a la fin d'une chaine, les clauses where ne fonctionnent plus ). Or nous sommes dans un projet de migration (DB2-->Oracle) avec modification minimum du soft et l'on n'a pas le temps de modifier toutes les clauses where portant sur des chaines de caracteres.
Mais bon d'un coté comme de l'autre on a des problemes...

Pour info, pourquoi utilisez vous des types char?

Ma foi, du point de vue de nos developpeurs, la question est plutot : "mais pourquoi faut-il utiliser des varchar ?"

[jrman]

Bonjour,
De notre coté je le rappelle nous migrons de Unisys/RDMS vers UNIX/Oracle.
En fait nous avons etudie le comportement du type char RDMS, et de son comportement dans un type C Unisys, puis nous avons reproduit le meme comportement coté Unix/Oracle.
Je pense que déjà, il faudrait faire cette étude. Ensuite le choix char/varchar2 s'imposera de lui meme.
Nous avons eu la chance de pouvoir le reproduire exactement grace a cette astuce de compilation, souhaitons que ce sera pareil de votre coté afin d'éviter toute modif de code.
a+
jrman

[LeoAnderson]

Attention cependant : il y a de fortes différences entre CHAR et VARCHAR, notamment au niveau des opérations de comparaisons mais surtout de la place disque occupée (sans oublier les différences de capacité)

A lire à ce sujet : CHAR versus VARCHAR2 Semantics

[orafrance]

CHAR existe toujours pour garantir la compatibilité ascendante mais VARCHAR2 doit être utilisé pour les chaînes de caractères (ou CLOB )

[jrman]

Bonjour,
Megaculpa, je retire ce que j'ai dit
Je ne constate pas le meme probleme que isa06.
En revanche j'ai les meme conclusions dans nos contextes de migration.
Pour ne pas avoir de probleme en recherche ou en resultat de select dans de l'embedded sql associe a du c/c++, le mieux est d'avoir des types char dans les programmes c/c++, et d'utiliser des types char dans les tables.
Du coup y'a des blancs partout mais tous les select fonctionnent dans les 2 sens.
Le type oracle/c/c++ varchar c'est une combinaison du type char c/c++ avec l'option de compile citee ci-avant. Car je le rappelle si tu select des varchar2 dans des char c/c++ tu te prends des blancs (sans l'option de compil).
Tandis que faire des select de varchar2 dans des types varchar oracle pour c/c++ te renvoit la chaine exacte (modulo le null et sans l'option).

a+
jrman