Utilisation des "bin variable"

Version imprimable

Bonjour dans un SQL dynamique j'utilise le code suivant qui ne fonctionne pas :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
wardynqr :=
        ' select floor(rank()over(order by num_f)/:wval_tranche)tranche,c.* '||
        ' from table1 c '||
        ' where num_soc =  :wvaleur '||
        ' and cpt_typ in (select cod_tuple1 '||
        '                   from pfaccau_fct '||
        '                   where cod_tab    = :C_PRUCRE '||
        '                   and cod_tuple2 = :C_AR) '||
        ' order by NUM_F, CPT_NUM_SEQ' ||
        ' )'
        execute immediate wardynqr
        bulk collect into col_cpt using wvaleur, C_PRUCRE, C_AR,wval_tranche;

cependant en remplacant wval_tranche par sa valeur cela fonctionne :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
wardynqr :=
        ' select floor(rank()over(order by num_f)/'&wval_tranche&')tranche,c.* '||
        ' from table1 c '||
        ' where num_soc =  :wvaleur '||
        ' and cpt_typ in (select cod_tuple1 '||
        '                   from pfaccau_fct '||
        '                   where cod_tab    = :C_PRUCRE '||
        '                   and cod_tuple2 = :C_AR) '||
        ' order by NUM_F, CPT_NUM_SEQ' ||
        ' )'
        execute immediate wardynqr
        bulk collect into col_cpt using wvaleur, C_PRUCRE, C_AR,wval_tranche;

Les Bind variables ne peuvent être utilisées que pour des conditions apres un signe = != >< etc... On ne peut pas les utilisées pour le calcul d'un champs ?

20/09/2006, 17h05
SheikYerbouti

Une bind variable ne peut pas référencer un nom de table ou de colonne.
Dans ce cas, générez voter string par concaténation.
20/09/2006, 17h07
sygale

Ca oui pas de nom de champs ou de table ! à faire avec des concaténation

mais je ne savais pas qu'elle ne pouvait pas entrer dans le calcul d'un champs calculé dans une requête tu confirmes ?
20/09/2006, 17h18
SheikYerbouti

Humpf... j'ai lu trop vite.
Une bind variable est finalement une valeur transmise via une variable.
Franchement, je ne vois pas pourquoi cela ne pourrait faire parti d'un calcul 8O

Citation:

Envoyé par SheikYerbouti

Franchement, je ne vois pas pourquoi cela ne pourrait faire parti d'un calcul 8O

Ben tout pareil mais la preuve :

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
SQL>set serveroutput on
SQL>declare
  2  strSQL   varchar2(400);
  3  wvaleur  varchar2(50):='X';
  4  wcpt pls_integer:=0;
  5  begin
  6  strSQL :='select count(*) from dual where dummy = :wvaleur';
  7  execute immediate strSQL into wcpt using wvaleur;
  8  dbms_output.put_line(wcpt);
  9  exception
 10  when others then
 11  dbms_output.put_line(sqlerrm);
 12  end;
 13  /
1
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :00.00
SQL>declare
  2  strSQL   varchar2(400);
  3  wvaleur  varchar2(50):='X';
  4  wcpt pls_integer:=0;
  5  waj pls_integer:=1;
  6  begin
  7  strSQL :='select count(*)+ :waj from dual where dummy = :wvaleur';
  8  execute immediate strSQL into wcpt using wvaleur,waj;
  9  dbms_output.put_line(wcpt);
 10  exception
 11  when others then
 12  dbms_output.put_line(sqlerrm);
 13  end;
 14  /
ORA-01722: Nombre non valide
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :00.00
SQL>

20/09/2006, 22h01
SheikYerbouti

C'est tout à fait normal puisque vwvaleur est un varchar2() qui vaut 'X'.

Je crois que vous vous mélangez les pinceaux. Dans la string, utilisez plutôt :1, :2, etc...

puis bindez avec des variables correctes. Vous ne pouvez pas additionner 'X' à un nombre !

Citation:

Envoyé par SheikYerbouti

Je crois que vous vous mélangez les pinceaux. Dans la string, utilisez plutôt :1, :2, etc...

puis bindez avec des variables correctes. Vous ne pouvez pas additionner 'X' à un nombre !

Pas forcément, les noms importent peu en fait, c'est un binding positionnel qui est effectué, donc on peut réutiliser les mêmes noms que les variables avec ":" devant dans la requête, ou bien utiliser des numéros... peu importe mais il faut ABSOLUMENT que dans la clause USING les variables apparaissent dans le MEME ordre que leur emplacement dans la requête.

Par exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 rbaraer@Ora10g> declare 2 strSQL varchar2(400); 3 wvaleur varchar2(50):='X'; 4 wcpt pls_integer:=0; 5 waj pls_integer:=1; 6 begin 7 strSQL :='select count(*)+ :waj from dual where dummy = :wvaleur'; 8 execute immediate strSQL into wcpt using waj, wvaleur; 9 dbms_output.put_line(wcpt); 10 exception 11 when others then 12 dbms_output.put_line(sqlerrm); 13 end; 14 / 2 PL/SQL procedure successfully completed. rbaraer@Ora10g>
Soit dans la requête originale :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 wardynqr := ' select floor(rank()over(order by num_f)/:wval_tranche)tranche,c.* '|| ' from table1 c '|| ' where num_soc = :wvaleur '|| ' and cpt_typ in (select cod_tuple1 '|| ' from pfaccau_fct '|| ' where cod_tab = :C_PRUCRE '|| ' and cod_tuple2 = :C_AR) '|| ' order by NUM_F, CPT_NUM_SEQ' || ' )' execute immediate wardynqr bulk collect into col_cpt using wval_tranche, wvaleur, C_PRUCRE, C_AR;
Cordialement,

rbaraer

21/09/2006, 08h25
rbaraer

Petit addendum : si votre requête est toujours la même avec seulement les variables liées qui changent de valeur, vous n'avez pas besoin, en PLSQL, d'utiliser du SQL synamique. C'est même très déconseillé car les requêtes SQL dynamiques, contrairement aux requêtes SQL "statiques", n'utilisent pas le cache de session PLSQL. De ce fait, une requête SQL dynamique en PLSQL se traduit toujours par un accès au Shared Pool, qui se révèle certes fructueux dès la 2ème exécution, mais le shared pool étant une zone partagée par toutes les sessions, donc fortement concurrentielle, il est préférable de ne pas y accéder, d'où l'intérêt d'avoir la requête dans son cache de session :).

Voici une citation de Tom Kyte à ce sujet, extraite de ce thread sur asktom :

Citation:

binding does not eliminate parsing, only programmers can eliminate parsing.
binding changes the type of parse from HARD (really bad) to SOFT (just bad).

The only good parse is *no parse* -- and you would have to do that (or use plsql
of course, which will automagically cache static sql so it gets parsed once per
session instead of once per execution)

En fait, à partir de la 2ème exécution, le SQL dynamique résulte en un "soft parse" et le SQL statique en un "no parse". Dans le même thread, cherchez la première occurence de "I'm using EXECUTE IMMEDIATE" et la réponse de Tom Kyte, qui explicite la différence entre SQL statique et dynamique.

Par contre, si le nombre de variables de lien change selon certaines conditions, il peut être intéressant d'utiliser du SQL dynamique. Cependant, c'est surtout utile pour faire ce que l'on ne peut pas en SQL statique : changer la table que l'on lit par exemple...

En espérant que ça vous aide :),

Cordialement,

rbaraer
21/09/2006, 11h48
sygale

Merci beaucoup pour toutes ces précisions.
Effectivement, ce n'est qu'un bout de la requete, mais je vais voir à faire un SQL tout de même.

Merci pour la solutions :
Respecter l'ordre des binds variables :D