Discussion :

[sygale]

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

Code :

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

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 :

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

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 ?

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

[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 ?

[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

[sygale]

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

Ben tout pareil mais la preuve :

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

[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 !

[rbaraer]

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 :

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

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

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

[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 :

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

[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