Discussion :

[davy.g]

Bonjour,

J'ai une procédure stockée dans laquelle j'utilise Execute immediate :

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
27
28
29
30
 
lv_sql_stmt := 'SELECT nsiret FROM dpa_marche_hist_acompte WHERE idenos = :1 ';
IF Ora_nomht1 IS NULL THEN
   lv_where1 := ' AND nomht1 IS NULL';
ELSE
   lv_where1 := ' AND nomht1 = :2';
END IF;
IF Ora_nomhl1 IS NULL THEN
   lv_where2 := ' AND nomhl1 IS NULL';
ELSE
  lv_where2 := ' AND nomhl1 = :3';
END IF;
IF Ora_nstra IS NULL THEN
  lv_where3 := ' AND nstra IS NULL';
ELSE
  lv_where3 := ' AND nstra = :4';
END IF;
IF Ora_eaetex IS NULL THEN
 lv_where4 := ' AND eaetex IS NULL';
ELSE
 lv_where4 := ' AND eaetex = :5';
END IF;
IF Ora_dexemm IS NULL THEN
 lv_where5 := ' AND dexemm IS NULL;';
ELSE
  lv_where5 := ' AND dexemm = :6;';
END IF;
lv_sql_stmt := lv_sql_stmt||lv_where1||lv_where2||lv_where3||lv_where4||lv_where5;
 
EXECUTE IMMEDIATE lv_sql_stmt USING Ora_idenos,Ora_nomht1,Ora_nomhl1,Ora_nstra,Ora_eaetex,Ora_dexemm RETURNING lv_nsiret;

Je souhaite récupérer la valeur de nsiret dans ma variable lv_nsiret.
Est-ce que la syntaxe de mon execute immediate est correcte?
J'ai le message d'erreur suivant :

LINE/COL ERROR
-------- -------------------------------------------------------------
134/115 PLS-00103: Symbole "LV_NSIRET" rencontrÚ Ó la place d'un des
symboles suivants :
into
Symbole "into" a ÚtÚ substituÚ Ó "LV_NSIRET" pour continuer.

[NGasparotto]

Code :

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

1
2
3
4
5
IF Ora_dexemm IS NULL THEN
 lv_where5 := ' AND dexemm IS NULL;';
ELSE
  lv_where5 := ' AND dexemm = :6;';
END IF;

Le SQL dynamique ne supporte pas le point-virgule à la fin de l'instruction. Essaies de le supprimer des lignes présentées ci-dessus.
Ce qui doit donner :

Code :

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

1
2
3
4
5
IF Ora_dexemm IS NULL THEN
 lv_where5 := ' AND dexemm IS NULL';
ELSE
  lv_where5 := ' AND dexemm = :6';
END IF;

Nicolas.

[davy.g]

NON ! J'ai le même message d'erreur !
Merci quand même !

[NGasparotto]

NON ! J'ai le même message d'erreur !
Merci quand même !

Ok, mais il ne faux quand même pas le point-virgule.
De plus dans ta commande suivante :

Code :

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

EXECUTE IMMEDIATE lv_sql_stmt USING Ora_idenos,Ora_nomht1,Ora_nomhl1,Ora_nstra,Ora_eaetex,Ora_dexemm RETURNING lv_nsiret;

Essaies de remplacer RETURNING par INTO, ce doit être sous la forme :

Code :

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

EXECUTE IMMEDIATE statement INTO output_variables USING input_variables;

Nicolas.

[davy.g]

Merci !
C'était bien ma syntaxe qui était incorrecte !!!

Je n'ai plus de message d'erreurs !!!
Merci Nicolas !

[NGasparotto]

Cool !

Nicolas.

[davy.g]

C'est encore moi !

Je n'ai plus d'erreurs à la compil mais lors de l'exécution de ma proc j'ai le message suivant :

SQL> SET serveroutput on size 40000;
SQL> BEGIN
2 PUMA.P_ALIM_HIST_ACOMPTE;
3 END;
4 /
Ora_idenos : 210632003T201
Ora_nomht1 : TF
Ora_nomhl1 :
Ora_nstra :
Ora_eaetex : 2004
Ora_dexemm : 02
BEGIN
*
ERREUR Ó la ligne 1 :
ORA-01006: La variable de lien n'existe pas
ORA-06512: Ó "PUMA.P_ALIM_HIST_ACOMPTE", ligne 134
ORA-06512: Ó ligne 2

La ligne 134 correspond à la ligne de mon Execute immediate.

[NGasparotto]

En fait, tu as un problème avec ta clause USING.
Dans certain cas, si une de ces variables est nulle tu n'utilises pas la bind variable dans la construction de ton select, or ton EXECUTE IMMEDIATE mentionne la clause USING avec cette variable.

Nicolas.

[davy.g]

Ok Merci Beaucoup !!!

[davy.g]

Je comprends le problème.
Mais, j'utilise Execute immediate pour pouvoir construire un SELECT avec des clauses WHERE de type 'col_a IS NULL' si la valeur de ma variable que je passe à col_a est NULL.
Par contre, si ma variable est NULL je ne dois pas lui passer dans la clause USING.

Comment faire pour construire mon Execute immediate ?

[NGasparotto]

Tu peux essayer :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
lv_sql_stmt := 'SELECT nsiret FROM dpa_marche_hist_acompte WHERE idenos = :1 ';
lv_where1 := ' AND (nomht1 = :2 OR ( nomht1 IS NULL AND :2 IS NULL))';
lv_where2 := ' AND (nomhl1 = :3 OR ( nomhl1 IS NULL AND :3 IS NULL))';
lv_where3 := ' AND (nstra  = :4 OR ( nstra  IS NULL AND :4 IS NULL))';
lv_where4 := ' AND (eaetex = :5 OR ( eaetex IS NULL AND :5 IS NULL))';
lv_where5 := ' AND (dexemm = :6 OR ( dexemm IS NULL AND :6 IS NULL))';
lv_sql_stmt := lv_sql_stmt||lv_where1||lv_where2||lv_where3||lv_where4||lv_where5;
 
EXECUTE IMMEDIATE lv_sql_stmt 
INTO lv_nsiret
USING Ora_idenos,Ora_nomht1,Ora_nomhl1,Ora_nstra,Ora_eaetex,Ora_dexemm ;

Nicolas.

[davy.g]

J'aimerais reformuler mon problème :

Je suis dans une proc. stockée.
J'ai un curseur qui me ramène des valeurs que je stocke dans des variables.
Ces variables me servent ensuite à construire un SELECT du genre :

SELECT *
FROM table1
Where col1 = var1
And col2 = var2
And col3 = var3
And col4 = var4
And col5 = var5;

Or si une de mes variables est NULL je dois modifier mon SELECT :
Par exemple, si la variable var1 est NULL, cela donnera :
SELECT *
FROM table1
Where col1 IS NULL
And col2 = var2
And col3 = var3
And col4 = var4
And col5 = var5;

Je pensais pouvoir résoudre mon problème avec EXECUTE IMMEDIATE mais dans la clause USING je dois spécifier uniquement les variables non NULL.

Avez-vous une idée pour contourner mon problème ?
Merci.

[NGasparotto]

...mais dans la clause USING je dois spécifier uniquement les variables non NULL.

Attention, ce n'est pas ce que j'ai dit. Mais tu as bati ta requête en ne mettant pas les bind variables lorque celle-ci sont nulles, or dans ton execute immediate tu mentionne toutes les bind variables, mêmes quand celles-ci sont nulles, et donc suivant ta construction, ne sont pas dans ta requête, d'où peut-être la dernière erreur que tu as eu.

Ces variables me servent ensuite à construire un SELECT du genre :

SELECT *
FROM table1
Where col1 = var1
And col2 = var2
And col3 = var3
And col4 = var4
And col5 = var5;

Or si une de mes variables est NULL je dois modifier mon SELECT :
Par exemple, si la variable var1 est NULL, cela donnera :
SELECT *
FROM table1
Where col1 IS NULL
And col2 = var2
And col3 = var3
And col4 = var4
And col5 = var5;

Tu n'as pas besoin d'utiliser de SQL dynamique pour ceci, sur la base de mon dernier post précédent, tu peux faire :

Code :

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

1
2
3
4
5
6
7
SELECT *
FROM table1
Where (col1 = var1 OR (col1 IS NULL AND var1 is NULL))
And   (col2 = var2 OR (col2 IS NULL AND var2 is NULL))
And   (col3 = var3 OR (col3 IS NULL AND var3 is NULL))
And   (col4 = var4 OR (col4 IS NULL AND var4 is NULL))
And   (col5 = var5 OR (col5 IS NULL AND var5 is NULL));

Nicolas.

[davy.g]

J'ai testé ton code et j'ai à nouveau le message d'erreur suivant :

SQL> BEGIN
2 PUMA.P_ALIM_HIST_ACOMPTE;
3 END;
4 /
Ora_idenos : 210632003T201
Ora_nomht1 : TF
Ora_nomhl1 :
Ora_nstra :
Ora_eaetex : 2004
Ora_dexemm : 02
BEGIN
*
ERREUR Ó la ligne 1 :
ORA-01008: Toutes les variables ne sont pas liÚes
ORA-06512: Ó "PUMA.P_ALIM_HIST_ACOMPTE", ligne 144
ORA-06512: Ó ligne 2

[davy.g]

OK !
Désolé pour cette incompréhension !!

Je vais utiliser ta requête sans SQL dynamique !
Ca me semble beaucoup + simple !
C'est vrai que j'aurais pu y penser... ca semble très facile comme ca...
Je me suis pris la tête pour rien !!

Merci pour ton aide !!

[davy.g]

J'ai encore des soucis !
J'ai mis en place cette solution :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT  DISTINCT SUBSTR(nsiret,1,8)
   INTO  lv_nsiret
  FROM  dpa_marche_hist_acompte
WHERE  idenos = Ora_idenos
   AND (nomht1 = Ora_nomht1 OR ( nomht1 IS NULL AND Ora_nomht1 IS NULL))
   AND (nomhl1 = Ora_nomhl1 OR ( nomhl1 IS NULL AND Ora_nomhl1 IS NULL))
   AND (nstra  = Ora_nstra OR ( nstra  IS NULL AND Ora_nstra IS NULL))
   AND (eaetex = Ora_eaetex OR ( eaetex IS NULL AND Ora_eaetex IS NULL))
   AND (dexemm = Ora_dexemm OR ( dexemm IS NULL AND Ora_dexemm IS NULL));

Imaginons que pour les même idenos,nomht1,nomhl1,nstra,eaetex j'ai les mêmes valeurs et que que pour la colonne dexemm, j'ai 2 valeurs : 'TOTO' et NULL.
Donc, ce SELECT me ramène 2 lignes.
Comment faire donc pour récupérer que la ligne concernée ?

[NGasparotto]

Ta variable Ora_dexemm n'aura qu'une valeur elle (ou TOTO ou NULL ou toute autre valeur), donc tu ne ramènera qu'une des deux lignes (ou aucune) que tu mentionnes suivant la valeur de cette variable.
Prenons le test :

Code :

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

AND (dexemm = Ora_dexemm OR ( dexemm IS NULL AND Ora_dexemm IS NULL))

Si Ora_dexemm vaut TOTO, alors la première partie du test est vérifié.
Si Ora_dexemm vaut NULL, alors la deuxième partie du test est vérifiée avec l'autre ligne.
Si Ora_dexemm vaut TATA, aucune des parties du test ne sont vérifiées, aucune ligne ne sera retournée.

Nicolas.

[davy.g]

J'ai ce SELECT :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT  DISTINCT txtva
   INTO  lv_txtva
  FROM  dpa_marche_hist_acompte
WHERE  idenos = Ora_idenos
 AND (nomht1 = Ora_nomht1 OR ( nomht1 IS NULL AND Ora_nomht1 IS NULL))
 AND (nomhl1 = Ora_nomhl1 OR ( nomhl1 IS NULL AND Ora_nomhl1 IS NULL))
 AND (nstra  = Ora_nstra OR ( nstra  IS NULL AND Ora_nstra IS NULL))
 AND (eaetex = Ora_eaetex OR ( eaetex IS NULL AND Ora_eaetex IS NULL))
 AND (dexemm = Ora_dexemm OR ( dexemm IS NULL AND Ora_dexemm IS NULL));

Lors de l'exécution de ma proc, j'ai le message d'erreur suivant :

Ora_idenos : 210642006A081
Ora_nomht1 : TF
Ora_nomhl1 :
Ora_nstra :
Ora_eaetex : 2006
Ora_dexemm : 05
BEGIN
*
ERREUR Ó la ligne 1 :
ORA-01422: l'extraction exacte ramÞne plus que le nombre de lignes demandÚ
ORA-06512: Ó "PUMA.P_ALIM_HIST_ACOMPTE", ligne 166
ORA-06512: Ó ligne 2

Dans ma table j'ai les valeurs suivantes :
Sur la 2eme ligne en gras nstra est NULL et dans les 2 cas nomhl1 est NULL:

210642006A081 TF 01 2006 06 14504 0
210642006A081 TF 01 2006 07 14678,01 0
210642006A081 TF 01 2006 08 11989,12 0
210642006A081 TF 02 2006 05 5000 0
210642006A081 TF 02 2006 07 3000 0
210642006A081 TF 02 2006 08 6184,7 0
210642006A081 TF 2006 05 16500,6 0
210642006A081 TF 2006 06 24232,97 0
210642006A081 TF 2006 07 46306,81 0
210642006A081 TF 2006 08 76171,79 0

[davy.g]

Par exemple :

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
 
SQL> create table table1(col1 VARCHAR2(10), col2 VARCHAR2(10));
 
Table crÚÚe.
 
SQL> insert into table1 values('a1','a2');
 
1 ligne crÚÚe.
 
SQL> insert into table1 values('a1',NULL);
 
1 ligne crÚÚe.
 
 
SQL> select * from table1
  2  where (col1 = 'a1' OR (col1 IS NULL));
 
COL1       COL2
---------- ----------
a1         a2
a1
 
SQL>

J'ai bien 2 lignes...

[NGasparotto]

J'ai bien 2 lignes...

Ben forcément, il te manques une condition.
Si je remet dans le contexte de ton cas précédent, j'obtiendrais plutôt :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SQL> CREATE TABLE table1(col1 VARCHAR2(10), col2 VARCHAR2(10));
 
Table created.
 
SQL> INSERT INTO table1 VALUES('a1','a2');
 
1 row created.
 
SQL> INSERT INTO table1 VALUES('a1',NULL);
 
1 row created.
 
SQL> var myvar1 varchar2(2);
SQL> var myvar2 varchar2(2);
SQL> exec :myvar1:='a1';
 
PL/SQL procedure successfully completed.
 
SQL> exec :myvar2:='a2';
 
PL/SQL procedure successfully completed.
 
SQL> SELECT * FROM table1
  2  WHERE (col1 = :myvar1 OR (col1 IS NULL AND :myvar1 IS NULL))
  3  AND   (col2 = :myvar2 OR (col2 IS NULL AND :myvar2 IS NULL));
 
COL1       COL2
---------- ----------
a1         a2
 
SQL> exec :myvar1:='a1';
 
PL/SQL procedure successfully completed.
 
SQL> exec :myvar2:=null;
 
PL/SQL procedure successfully completed.
 
SQL> SELECT * FROM table1
  2  WHERE (col1 = :myvar1 OR (col1 IS NULL AND :myvar1 IS NULL))
  3  AND   (col2 = :myvar2 OR (col2 IS NULL AND :myvar2 IS NULL));
 
COL1       COL2
---------- ----------
a1
 
SQL>

Nicolas.