Discussion :

[nmarotel]

Bonjour à tous

Je dois convertir certaines données et je tombe sur une erreur ORA-01722: Nombre non valide

Le soucis, c'est que dans ma table source, si je fait un to_number du champs en question (CODVALQUAL), je n'ai pas d'erreur.

En fait, c'est mon bloc PL/SQL qui provoque cette erreur, mais si je passe directement la requete en question en SQL, je n'ai aucune erreur

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
cursor c_qee_valqual_2
	is
		select distinct qee.CODPARSANDRE
		from QEE_VALQUAL_SUPPL qee;
 
BEGIN
 
for l_qee_valqual_2 in c_qee_valqual_2
	loop
 
 
	insert into QEE_VALPARAMQUAL (CODPARAM, CODVALQUAL, LIBVALQUAL, NUMORDRE)
		select tmp.CODPARSANDRE, tmp.CODVALQUAL, tmp.LIBVALQUAL, rownum 
		from (select qee.CODPARSANDRE, qee.CODVALQUAL, qee.LIBVALQUAL
		from QEE_VALQUAL_SUPPL qee where qee.CODPARSANDRE = l_qee_valqual_2.CODPARSANDRE order by to_number(replace(qee.CODVALQUAL,',','.')) asc) tmp ;
 
	end loop; 
 
END ;

j'ai 473 occurences de CODPARSANDRE et cela plante sur une dizaine....mais je ne sais absolument pas pourquoi....

[Waldar]

Quels sont les types des colonnes des tables QEE_VALPARAMQUAL et QEE_VALQUAL_SUPPL ?

[nmarotel]

Quels sont les types des colonnes des tables QEE_VALPARAMQUAL et QEE_VALQUAL_SUPPL ?

Toutes les colonnes des tables QEE_VALPARAMQUAL et QEE_VALQUAL_SUPPL sont de format VARCHAR2, sauf NUMORDRE qui est de type NUMBER(5).

J'ai beau retourner le problème dans tous les sens, je ne vois pas ce qui provoque cette erreur.
J'ai catché l'exception (sur une dizaine de valeurs de CODPARSANDRE comme '1010', '1011', etc) et si je copie-colle l'insert en remplacant le filtre par '1010' par exemple, ça ne plante pas et l'insert se fait)

[skuatamad]

Comment sont réalisées les connexions pour exécuter le code PL/SQL et pour exécuter le code SQL ?
Peut être que les sessions SQL et PL/SQL ne sont pas configurés de la même manière :

Code :

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

1
2
3
select * 
  from nls_session_parameters
 where parameter = 'NLS_NUMERIC_CHARACTERS'

Il est préférable de préciser le NLS_NUMERIC_CHARACTERS dans le TO_NUMBER plutôt que de passer par un replace.
Le replace peut cependant être quand même nécessaire si les les valeurs n'ont pas un séparateur homogène.
Mais je préciserais quand même le NLS_NUMERIC_CHARACTERS après conversion du séparateur.

[nmarotel]

Merci Skuatamad, ça doit venir de ça.
Sur ma session SQL, j'ai le '.' comme NLS_NUMERIC_CHARACTERS alors que sur la database c'est ','

le champs que je dois convertir pour effectuer un tri est toujours de la forme '1,1', '1' ou '1,11' par exemple.
Qu'est-ce que je dois utiliser comme syntaxe pour que mon code PL/SQL passe sans soucis ?

[skuatamad]

Vous pouvez le coder dans TO_NUMBER mais de ce fait le format devient obligatoire, ce qui n'est pas toujours très pratique :

Code :

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

TO_NUMBER('1267,4', '999999999D999', 'NLS_NUMERIC_CHARACTERS = '',.''')

Vous pouvez aussi fixer le séparateur dans votre procédure en changeant la session :

Code :

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

execute immediate 'alter session set NLS_NUMERIC_CHARACTERS='', ''';

Puis revenir au sépareur de base en fin de procédure si vous ne souhaitez modifier ce paramétrage que pour cette procédure.

Évidemment, idéalement vous devriez modifier le type de la colonne en NUMBER.

[nmarotel]

Merci beaucoup skuatamad !

j'ai inséré ce code dans mon script et tout passe sans soucis

Code :

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

execute immediate 'alter session set NLS_NUMERIC_CHARACTERS=''. ''';