EXECUTE IMMEDIATE + Oracle 8

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour,

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

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 :

Citation:

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.

Citation:
Envoyé par davy.g
Code:

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:

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.

27/02/2007, 10h03
davy.g

NON ! J'ai le même message d'erreur !
Merci quand même !
27/02/2007, 10h06
NGasparotto

Citation:

Envoyé par davy.g

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:

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:

EXECUTE IMMEDIATE statement INTO output_variables USING input_variables;

Nicolas.
27/02/2007, 10h14
davy.g

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

Je n'ai plus de message d'erreurs !!!
Merci Nicolas ! ;)
27/02/2007, 10h16
NGasparotto

Cool !

Nicolas.
27/02/2007, 10h20
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 :

Citation:

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.
27/02/2007, 10h33
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.
27/02/2007, 10h48
davy.g

Ok Merci Beaucoup !!!
;)
27/02/2007, 11h04
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 ?

Tu peux essayer :

Code:

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.

27/02/2007, 11h32
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.

Citation:

Envoyé par davy.g

...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.

Citation:

Envoyé par davy.g

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:

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.

27/02/2007, 11h44
davy.g

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

Citation:

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
27/02/2007, 11h46
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 !!:oops: :oops:

Merci pour ton aide !! :king:

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

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 ?

27/02/2007, 13h50
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:

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.

J'ai ce SELECT :
Code:

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 :

Citation:

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

Par exemple :

Code:

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...

Citation:

Envoyé par davy.g

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:

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.

Voir 40 message(s) de cette discussion en une page