Discussion :

[Nanie57180]

BOnjour à tous,

j'aurais besoin de votre aide.
je dois ecrire une fonction qui fait un select sur une table avec une where clause contenant plusieurs attributs pouvant être null. d'ou le besoin de construire une query dynamique.
Puis je utiliser un execute immediate et retourner le resultat dans un curseur?
pour le moment je n'y arrive pas. Avez vous une autre solution?
Merci pour votre aide
Nanie

[skuatamad]

Il faut utiliser OPEN FOR.
Regarde Making a genric search sql Query pour une approche optimum.
Le lien vers le pdf pointe vers le magazine optimizes away binds and promotes more-selective queries

[Nanie57180]

j'ai cela pour le moment

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
   PROCEDURE get_abonnement (
      pCodeCatalogue IN VARCHAR2,
            pIddemandeur IN VARCHAR2,
            pUserCreation IN VARCHAR2,
            pCentreAna IN VARCHAR2,
            pEtatAbonnement IN VARCHAR2,
            vret In OUT SYS_REFCURSOR
   )
 
   AS
 
      sqlquery     VARCHAR2(500);
      paramIddemandeur VARCHAR2(500);
 
 
   BEGIN
 
 
 
      IF  ( pIddemandeur is not null) then 
      paramIddemandeur := ' ID_DEMANDEUR = ' || pIddemandeur || ';';
      end if;
       sqlquery:= 'SELECT * from ABONNEMENT WHERE ' || paramIddemandeur;
       dbms_output.put_line(sqlquery);
 
  OPEN vret FOR sqlquery;
 
 
 
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
      END get_abonnement;

Mais à l’exécution j'ai un message de ce style:

ORA-06550: Ligne 19, colonne 4 :
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

; <identificateur> <identificateur entre guillemets>
The symbol ";" was substituted for "end-of-file" to continue.

je vais regarder ton lien.
Merci

[skuatamad]

Quelques erreurs :

• when others then raise ne sert qu'à masquer la ligne en erreur, c'est donc inutile (mais au moins il y a le raise) .

• ' || pIddemandeur || ' : il faut utiliser une variable de liaison (bind variable).

• ';' c'est ce ; en trop qui génère l'erreur

En reprenant les exemples fournis dans les liens ci-dessus, je ferais quelque chose comme ça :

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
PROCEDURE get_abonnement (
  pCodeCatalogue IN VARCHAR2,
  pIddemandeur IN VARCHAR2,
  pUserCreation IN VARCHAR2,
  pCentreAna IN VARCHAR2,
  pEtatAbonnement IN VARCHAR2,
  vret In OUT SYS_REFCURSOR
) AS
  l_sqlquery VARCHAR2(500);
  l_paramIddemandeur VARCHAR2(500) := '';
BEGIN
  l_sqlquery:= 'SELECT * from ABONNEMENT WHERE 1 = 1 ';
 
  IF ( pIddemandeur is not null) then
    l_paramIddemandeur := l_paramIddemandeur || ' and ID_DEMANDEUR = :pIddemandeur';
  else
    l_paramIddemandeur := l_paramIddemandeur || ' and (1 = 1 or :pIddemandeur is null)'
  end if;
  --
  /*les autres IF ...*/
  l_sqlquery:= l_sqlquery || paramIddemandeur;
  dbms_output.put_line(sqlquery);
  --
  OPEN vret FOR sqlquery using pIddemandeur /*, les autres paramètres*/;
END get_abonnement;

[Nanie57180]

Merci beaucoup pour ton aide.
Par contre à l’exécution:

Error at line 1
ORA-01006: la variable attachée (bind variable) n'existe pas
ORA-06512: à "REPELECPMSLUX.PCK_REPORTING_TEST", ligne 31
ORA-06512: à ligne 17

voici mon code (ou plutôt le tien :-))

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
 PROCEDURE get_abonnement (
  pCodeCatalogue IN VARCHAR2,
  pIddemandeur IN VARCHAR2,
  pUserCreation IN VARCHAR2,
  pCentreAna IN VARCHAR2,
  pEtatAbonnement IN VARCHAR2,
  vret IN OUT SYS_REFCURSOR
) AS
  l_sqlquery VARCHAR2(500);
  l_paramIddemandeur VARCHAR2(500) := '';
BEGIN
  l_sqlquery:= 'SELECT * from ABONNEMENT WHERE 1 = 1 ';
 
  IF ( pIddemandeur IS NOT NULL) then
    l_paramIddemandeur := l_paramIddemandeur || ' and ID_DEMANDEUR = :pIddemandeur';
  else
    l_paramIddemandeur := l_paramIddemandeur || ' and (1 = 1 or :pIddemandeur is null)';
  end IF;
  --
  /*les autres IF ...*/
 /* l_sqlquery:= l_sqlquery || paramIddemandeur;*/
  dbms_output.put_line(l_sqlquery);
  --
  OPEN vret FOR l_sqlquery USING pIddemandeur /*, les autres paramètres*/;
END get_abonnement;

[ojo77]

A vue de nez :

Code :

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

1
2
3
4
5
IF ( pIddemandeur IS NOT NULL) then
l_paramIddemandeur := l_paramIddemandeur || ' and ID_DEMANDEUR = :pIddemandeur';
else
l_paramIddemandeur := l_paramIddemandeur || ' and (1 = 1 or :pIddemandeur is null)';
end IF;

Gagnerais à être écrit ainsi

Code :

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

1
2
3
IF ( pIddemandeur IS NOT NULL) then
l_paramIddemandeur := l_paramIddemandeur || ' and ID_DEMANDEUR = :pIddemandeur';
end IF;

En effet comme 1=1 est toujours vrais 1=1 et n'importe quoi vaut toujours vrai et donc la clause n'a pas lieu d'être.