Query dynamique + retour données dans un curseur

**Nanie57180** · 15/07/2013, 10h48

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** · 15/07/2013, 11h18

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** · 15/07/2013, 11h32

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** · 15/07/2013, 11h56

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** · 15/07/2013, 12h47

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** · 15/07/2013, 12h57

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.

**Nanie57180** · 15/07/2013, 13h11

Je suis en effet d'accord que le else ne sert à rien...
Par contre j'ai encore besoin de vos lumières car j'ai toujours une erreur à l'éxé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

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_bis (
  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_bis;

**skuatamad** · 15/07/2013, 13h29

Envoyé par ojo77

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.

Envoyé par Nanie57180

Je suis en effet d'accord que le else ne sert à rien...

Lisez l'article, le USING n'est pas paramétrable il faut donc que la variable de liaison soit présente qu'elle soit renseignée ou non !

Que donne l'affichage dbms_output de la requête ?

**Nico02** · 15/07/2013, 14h35

J'espère que dans ton code tu as bien dé-commenté la ligne

Code :

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

 /* l_sqlquery:= l_sqlquery || paramIddemandeur;*/

Sinon c'est un peu normal qu'il ne trouve rien a binder

**Nanie57180** · 15/07/2013, 14h47

alors voici mon code:

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
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
      DBMS_OUTPUT.put_line ('début');
      --
      l_sqlquery := 'SELECT ID_ABONNEMENT 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 || l_paramiddemandeur;
      DBMS_OUTPUT.put_line (l_sqlquery);
 
      --
      OPEN vret FOR l_sqlquery USING piddemandeur;
   /*, les autres paramètres*/
   END get_abonnement;

il me retourne cette erreur:

Code :

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

1
2
3
ORA-01006: la variable attachée (bind variable) n'existe pas
ORA-06512: à "REPELECPMSLUX.PCK_REPORTING_ELECTRONIQUE", ligne 388
ORA-06512: à ligne 16

Par contre le DBMS Output me sort bien:

début

Code :

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

SELECT ID_ABONNEMENT from ABONNEMENT WHERE 1 = 1  and ID_DEMANDEUR = :pIddemandeur

**Nanie57180** · 15/07/2013, 14h51

a premiere vue il construit bien la requete:

mais c'est à l'éxécution ci-dessous que ca pose problème...

Code :

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

1
2
OPEN vret FOR l_sqlquery
      USING piddemandeur, pusercreation, pcentreana, petatabonnement;

**skuatamad** · 15/07/2013, 15h12

Il faut dans ce cas placer tous les if (avec les ELSE), relis l'article, relis mon post au dessus...
La liste dans USING doit être présente dans la requête et dans le bon ordre.

Ci-dessous un exemple qui n'apporte pas grand chose par rapport au code de tom kyte :

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SQL> create or replace procedure get_emp (
  2    p_empid number default null,
  3    p_salary number default null,
  4    c out sys_refcursor
  5  ) as
  6    l_query varchar2(1000);
  7  begin
  8    l_query := 'select empno, ename, sal from emp where 1 = 1 ';
  9    if (p_empid is not null) then
 10      l_query := l_query || ' and empno = :p_empid ';
 11    else
 12      l_query := l_query || ' and (1 = 1 or :p_empid is null) ';
 13    end if;
 14    if (p_salary is not null) then
 15      l_query := l_query || ' and sal >= :p_salary ';
 16    else
 17      l_query := l_query || ' and (1 = 1 or :p_salary is null) ';
 18    end if;
 19    dbms_output.put_line(l_query);
 20    open c for l_query using p_empid, p_salary;
 21  end;
 22  /
 
Procedure created.
 
SQL> set serverout on
SQL> set autoprint on
SQL> var cur refcursor
SQL> exec get_emp(p_empid=>7369,c=> :cur);
select empno, ename, sal from emp where 1 = 1  and empno = :p_empid  and (1 = 1
or :p_salary is null)
 
PL/SQL procedure successfully completed.
 
 
     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
 
SQL> exec get_emp(p_salary=>950, c=> :cur);
select empno, ename, sal from emp where 1 = 1  and (1 = 1 or :p_empid is null)
and sal >= :p_salary
 
PL/SQL procedure successfully completed.
 
 
     EMPNO ENAME             SAL
---------- ---------- ----------
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7839 KING             5000
      7844 TURNER           1500
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
 
11 rows selected.
 
SQL>

**Nanie57180** · 15/07/2013, 16h18

MErci beaucoup.
J'y suis enfin arrivé.

Query dynamique + retour données dans un curseur

PL/SQL Oracle

Discussions similaires

Partager

Partager