Discussion :

[Loyd1974]

Bonjour à toutes et à tous,

Je suis en 10gR2. Paramètre CURSOR_SHARING = EXACT.
Package PL/SQL

Et voici mon problème, j'ai un bout de code à optimiser qui est écrit en sql dynamique. Le problème avec ce code ce sont les lignes du clauses where qui sont écrites ainsi :

Code :

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

1
2
3
4
 
AND (:Parameter1 is null or Colonne1 = :Parameter1)
AND (:Parameter2 is null or Colonne2 = :Parameter2)
...

et ceci près de 10 fois, donc vous imaginez bien que dans la situation actuelle, les index, on peut leur dire adieu
J'ai donc modifié mon code rapidement pour écrire mon sql dynamique plus proprement en faisant :

Code :

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

1
2
3
4
5
6
7
8
 
IF parameter1 is not null then
   Where_Stmt := Where_Stmt || ' AND Colonne1 = ' || Parameter1;
End If
IF parameter2 is not null then
   Where_Stmt := Where_Stmt || ' And Colonne2 = ' || Parameter2;
End if;
...

Et donc, dès que l'utilisateur renseigne bien les valeurs de certains paramètres, l'amélioration est notable (bien entendu, s'il n'en renseigne aucun, le temps d'exécution et les consos restent les mêmes)

Donc, tout irai bien, sauf que ce code n'utilise pas du tout les binds variables et comme ce bout de code est appelée régulièrement, j'ai essayé de repasser par des binds variables en utilisant USING.

Le hic, c'est que je ne sais pas quelles seront les variables qui seront nécessaire au moment de l'exécution de ce SQL Dynamique (qui est fait au moyen d'un REF_CURSOR, comme suit : OPEN MyRefCur FOR MyStatement USING ...)

De mes recherches, il n'est pas possible de construire dynamiquement le USING (ce qui semble un peu normal, mais bon, j'ai tenté ), j'ai aussi essayé de faire comme ci-dessous :

Code :

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

1
2
3
4
5
6
7
8
 
IF Parameter1 is null THEN
   Where_Stmt := Where_Stmt || '/*:Parameter1*/';
ELSE
   ...
END IF;
et de l'utiliser comme cela
OPEN MyRefCur FOR MyStatement USING Parameter1;

Mais le code entre commentaire n'est pas analysée, et Oracle ne reconnait pas le arameter1 comme une variable (encore une fois, ca paraît très logique)

Donc, voilà ma question, sachant que je ne peux pas construire un OPEN pour chaque cas possible car le nombre de variable est trop grand (si mes souvenirs de proba sont bons, ca ferait 10! (factorielle) combinaison à coder (et j'ai mal aux doigts en ce moment)), connaitriez-vous une solution me permettant d'arriver à mes fins qui est de n'utiliser les paramètres que s'ils ne sont pas nul (en d'autre terme, s'ils ont bien été renseignés) et d'utiliser également les bind variables ?

[pifor]

Voir l'exemple détaillé de Tom Kyte basée sur l'utilisation d'une procédure stockée, d'un ref cursor et d'un objet contexte applicatif.

[Loyd1974]

Voir l'exemple détaillé de Tom Kyte basée sur l'utilisation d'une procédure stockée, d'un ref cursor et d'un objet contexte applicatif.

Je mérite des baffes, j'utilise les contextes avec forms et je les oublie quand je passe en PL/SQL
Merci beaucoup Pifor, c'est exactement ce que je cherchais