Discussion :

[Maglight]

Je souhaite faire une procédure en SQL Dynamique et j'ai un gros problème.

j'ai la procédure suivante

Code :

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

1
2
3
4
5
6
7
8
9
Procedure Test (F_Test out Type_Test,
                      pNom in TableClient.Nom%type)
is
begin
 Open F_Test for
 Select nom, prenom, adresse, codepostal, ville
 From TableClient
 Where nom = pNom;
end;

ou F_Test est déclaré comme suit :

Code :

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

1
2
3
4
5
6
Type Rec_test is record (nom TableClient.nom%type,
                                  prenom TableClient.prenom%type,
                                  adresse TableClient.adresse%type,
                                  codepostal TableClient.codepostal%type,
                                  ville TableClient.ville%type);
Type Type_Test is ref cursor return Rec_Test;

Lorsque j'appelle cette procédure cela marche bien.

Mon pb est lorsque je veux l'écrire sous la forme de sql dynamique.
J'ai le code suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Procedure Test (F_Test out Type_Test,
                      pNom in TableClient.Nom%type)
is
Str_chaine varchar2(1000);
Str_table varchar2(20);
begin
Str_table:='TableClient';
 Str_chaine :=' Open F_Test for '+
' Select nom, prenom, adresse, codepostal, ville '+
' From ' + Str_table +
' Where nom = pNom;';
EXECUTE IMMEDIATE str_chaine USING pnom;
end;

mais cela ne marche pas.
Help...

[Magnus]

Lisez ces 2 articles : vous confondez ce que vous pouvez faire avec un REF CURSOR et avec EXECUTE IMMEDIATE :

http://sheikyerbouti.developpez.com/...ord_set.htm#p4
http://sheikyerbouti.developpez.com/execute_immediate/

[Maglight]

J'ai essayé de m'inspirer des indications de Magnu mais je dois bien reconnaitre que je bloque.
J'ai maintenant le code suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Procedure Test (F_Test out Type_Test,
                      pNom IN TableClient.Nom%type)
IS
TYPE REFCUR1 IS REF CURSOR;
cur          REFCUR1;
Str_chaine VARCHAR2(10000);
BEGIN
Str_chaine :='SELECT nom, prenom, adresse, codepostal, ville FROM TableClient WHERE nom = pNom';
OPEN cur FOR Str_chaine ;
FETCH cur BULK COLLECT INTO f_vent_ligne ;
CLOSE cur;
END;

si je laisse ma déclaration comme suit:

Code :

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

1
2
3
4
5
6
Type Rec_test IS record (nom TableClient.nom%type,
                         prenom TableClient.prenom%type,
                         adresse TableClient.adresse%type,
                         codepostal TableClient.codepostal%type,
                         ville TableClient.ville%type);
Type Type_Test IS ref cursor RETURN Rec_Test;

j'ai le message suivant lors de la compilation : "PLS-00497 Impossible de mélanger des clauses mono et multi-ligne dans une liste into".
Sur internet cela me dit qu'il faut que je modifie mes variables dans le into de manière à ce qu'elles aient le bon type???

Si je fais l'essai avec

Code :

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

type type_Vent_Ligne is table of varchar2(100);

cela compile mais ne fonctionne pas ensuite si je met ce code à la place de celui du premier message.

Je suis vraiment perdu entre les notions de Ref Cursor et Execute Immediate.
Si j'ai compris (un minimum) Execute Immediate n'est utilisable que si je veux faire un Select, Update, Insert ou Delete sinon c'est Ref Cursor.

[Magnus]

Hum... sans être expert, je dirais que vous devez utiliser la clause BULK COLLECT avec EXECUTE IMMEDIATE ou alors l'un des 2 packages DBMS_SQL ou EXEC_SQL.
Ce dernier étant peut être spécifique à forms.
Les experts me corrigeront si je me trompe.

[Maglight]

Je suis vraiment désolé mais je sèche vraiment:
Dois-je avoir une syntaxe du type :

Code :

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

1
2
3
4
5
6
7
8
9
10
Procedure Test (F_Test out Type_Test,
                      pNom in TableClient.Nom%type)
is
Str_chaine varchar2(1000);
Str_table varchar2(20);
begin
Str_table:='TableClient';
EXECUTE IMMEDIATE  'Select nom, prenom, adresse, codepostal, ville Bulk Collect F_Test From ' + Str_table + ' Where nom = pNom'
USING pnom;
end;

car cela plante toujours.
Vraiment désolé mais je suis réellement pommé.

[Magnus]

Vous comprenez ce que vous tapez ?
Faîtes des tests : je n'avais jamais utilisé la clause BULK COLLECT avant de lire votre post et voilà ce que j'ai réussi à faire :

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
CREATE TABLE DVP (
  NOM VARCHAR2(50),
  PRENOM  VARCHAR2(80));
 
INSERT INTO DVP VALUES ('BOB', 'L''EPONGE');
 
INSERT INTO DVP VALUES ('PATRICK', 'L''ETOILE DE MER');
 
 
CREATE OR REPLACE PROCEDURE P_DUMMY(P_NOM VARCHAR2) IS
  TYPE T_DVP_NOM      IS TABLE OF DVP.NOM%TYPE;
  TYPE T_DVP_PRENOM   IS TABLE OF DVP.PRENOM%TYPE;
  T_NOM               T_DVP_NOM;
  T_PRENOM            T_DVP_PRENOM;
BEGIN
  EXECUTE IMMEDIATE 'SELECT NOM, PRENOM FROM DVP WHERE NOM = :P_NOM' BULK COLLECT INTO T_NOM, T_PRENOM USING P_NOM;
 
  FOR I IN T_NOM.FIRST..T_NOM.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('NOM = ' || T_NOM(I) || ', PRENOM = ' || T_PRENOM(I));
  END LOOP;
END;
/
 
EXEC P_DUMMY('BOB');

Résultat :

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
10g SOC10> CREATE TABLE DVP (
  2    NOM VARCHAR2(50),
  3    PRENOM  VARCHAR2(80));
 
Table créée.
 
10g SOC10> 
10g SOC10> INSERT INTO DVP VALUES ('BOB', 'L''EPONGE');
 
1 ligne créée.
 
10g SOC10> 
10g SOC10> INSERT INTO DVP VALUES ('PATRICK', 'L''ETOILE DE MER');
 
1 ligne créée.
 
10g SOC10> 
10g SOC10> 
10g SOC10> CREATE OR REPLACE PROCEDURE P_DUMMY(P_NOM VARCHAR2) IS
  2    TYPE T_DVP_NOM      IS TABLE OF DVP.NOM%TYPE;
  3    TYPE T_DVP_PRENOM   IS TABLE OF DVP.PRENOM%TYPE;
  4    T_NOM               T_DVP_NOM;
  5    T_PRENOM            T_DVP_PRENOM;
  6  BEGIN
  7    EXECUTE IMMEDIATE 'SELECT NOM, PRENOM FROM DVP WHERE NOM = :P_NOM' BULK COLLECT INTO T_NOM, T
_PRENOM USING P_NOM;
  8  
  9    FOR I IN T_NOM.FIRST..T_NOM.LAST
 10    LOOP
 11      DBMS_OUTPUT.PUT_LINE('NOM = ' || T_NOM(I) || ', PRENOM = ' || T_PRENOM(I));
 12    END LOOP;
 13  END;
 14  /
 
Procédure créée.
 
10g SOC10> EXEC P_DUMMY('BOB');
NOM = BOB, PRENOM = L'EPONGE
 
Procédure PL/SQL terminée avec succès.

[remi4444]

Je vois vraiment pas à quoi sert le 'EXECUTE IMMEDIATE' vu qu'il n'est meme pas utlisé dans la procédure qui fonctionne bien.

Moi je verrais bien un truc du style

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Procedure Test (F_Test out Type_Test,
                      pNom IN TableClient.Nom%type)
IS
Str_chaine varchar2(1000);
Str_table varchar2(20);
begin
Str_table:='TableClient';
Str_chaine :=' Select nom, prenom, adresse, codepostal, ville '+
' From ' + Str_table +
' Where nom = :1';
Open F_Test for str_chaine using pNom; 
end;

[pifor]

Moi ce qui m'étonne le plus dans cette discussion, c'est la confusion entre l'opérateur '+' (de Java ?) pour concaténer les VARCHAR2 et l'opérateur '||' PL/SQL

[Maglight]

J'avais bien essayé de faire ce que propose remi4444 mais tel quel cela ne compilait pas.
J'ai donc modifié mon code de la façon suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Procedure Test (F_Test out Type_Test,
                      pNom IN TableClient.Nom%type)
Type RefCur IS REF CURSOR;
CUR      RefCur;
IS
Str_chaine varchar2(1000);
Str_table varchar2(20);
begin
Str_table:='TableClient';
Str_chaine :=' Select nom, prenom, adresse, codepostal, ville '+
' From ' + Str_table +
' Where nom = pNom';
Open Cur for str_chaine using pNom; 
end;

Cela compile bien mais ne fonctionne pas.
Je pense que cela vient ( entre autre ) du fait que j'attend en sortie le cursor F_Test qui n'est pas trouvé.
Comment faire le lien entre Cur et F_Test?
Ou autre piste...

[remi4444]

Moi ce qui m'étonne le plus dans cette discussion, c'est la confusion entre l'opérateur '+' (de Java ?) pour concaténer les VARCHAR2 et l'opérateur '||' PL/SQL

Oui certes.... revenant moi meme sur oracle apres 2 mois de javascript, j'avais meme pas fait gaffe!

[Maglight]

Pardon pifor pour ces erreurs de syntaxe sur des copier-coller mais j'ai donc bien :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Procedure Test (F_Test out Type_Test,
                      pNom IN TableClient.Nom%type)
Type RefCur IS REF CURSOR;
CUR      RefCur;
IS
Str_chaine varchar2(1000);
Str_table varchar2(20);
begin
Str_table:='TableClient';
Str_chaine :=' Select nom, prenom, adresse, codepostal, ville '||
' From ' + Str_table ||
' Where nom = pNom';
Open Cur for str_chaine using pNom; 
end;

Qui compile mais ne fonctionne pas.
Je pense que cela vient du fait que j'attend en cursor de sortie F_Test et que c'est Cur sur lequel je travaille.
Existe-t-il le moyen de lier les deux ?

[remi4444]

Pardon pifor pour ces erreurs de syntaxe sur des copier-coller mais j'ai donc bien :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Procedure Test (F_Test out Type_Test,
                      pNom IN TableClient.Nom%type)
Type RefCur IS REF CURSOR;
CUR      RefCur;
IS
Str_chaine varchar2(1000);
Str_table varchar2(20);
begin
Str_table:='TableClient';
Str_chaine :=' Select nom, prenom, adresse, codepostal, ville '||
' From ' + Str_table ||
' Where nom = pNom';
Open Cur for str_chaine using pNom; 
end;

Qui compile mais ne fonctionne pas.
Je pense que cela vient du fait que j'attend en cursor de sortie F_Test et que c'est Cur sur lequel je travaille.
Existe-t-il le moyen de lier les deux ?

Tu as encore oublié un "+" ....

Effectivement, si tu ouvres un autre curseur que celui que tu passes en paramètre, ça a peu de chance de marcher. Pourquoi n'ouvres tu pas directement celui que tu a passé comme j'avais mis sur mon exemple ?

D'autre part, essaye d'etre attentif à la logique constante/variable. tu as mis 'pNom' directement dans la chaine, la aussi ça ne risque pas de fonctionner, si tu veux utiliser une variable bind alors il faut que tu utilises la bonne syntaxe à savoir les ":".

[Maglight]

Encore raté pour l'histoire du + et des variables bint.
Je ne peux pas faire hélas référence sur l'Open à mon cursor F_Test car la compilation d'Oracle9 me met le message suivant :
"PLS-00455:Impossible d'utiliser le curseur F_Test dans une instruction SQL Dynamique OPEN".
Sur internet j'ai vu qu'ils disaient de
"define a REF CURSOR without return type, and use it in the statement.".
J'ai donc modifié mon package de la manière suivante :

Code :

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

Type F_Test IS ref cursor RETURN;

et mon body comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Procedure Test (F_Test out Type_Test,
                      pNom IN TableClient.Nom%type)
IS
Str_chaine varchar2(1000);
Str_table varchar2(20);
begin
Str_table:='TableClient';
Str_chaine :=' Select nom, prenom, adresse, codepostal, ville '||
' From ' || Str_table ||
' Where nom = :pNom';
Open F_Test for str_chaine using pNom; 
end;

Bilan :
Compilation -> OK
Execution -> Pb.

[pifor]

Voila un exemple complet testé avec Oracle 10 mais qui doit aussi marche pour Oracle 9.

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
64
65
66
67
68
69
70
71
72
73
74
dev001> drop table t;
 
Table dropped.
 
dev001> 
dev001> create table t
  2  (c1 varchar2(30),
  3   c2 varchar2(30)
  4  );
 
Table created.
 
dev001> 
dev001> INSERT into t values('A', 'a');
 
1 row created.
 
dev001> INSERT into t values('B', 'b');
 
1 row created.
 
dev001> COMMIT;
 
Commit complete.
 
dev001> 
dev001> CREATE OR REPLACE PACKAGE p
  2  IS
  3  TYPE trc IS REF CURSOR;
  4  --
  5  PROCEDURE test (prc out trc, pc1 IN t.c1%type);
  6  END;
  7  /
 
Package created.
 
dev001> show errors
No errors.
dev001> 
dev001> CREATE OR REPLACE PACKAGE BODY p
  2  IS
  3  PROCEDURE test
  4  (prc out trc,
  5   pc1 IN t.c1%type
  6  )
  7  IS
  8  cmd varchar2(100);
  9  BEGIN
 10  cmd := 'select c1, c2 from t where c1 = :v';
 11  OPEN prc FOR cmd USING pc1;
 12  END;
 13  --
 14  END;
 15  /
 
Package body created.
 
dev001> show errors
No errors.
dev001> 
dev001> set serveroutput on
dev001> DECLARE
  2  vc p.trc;
  3  vc1 t.c1%type;
  4  vc2 t.c1%type;
  5  BEGIN
  6  p.test(vc, 'A');
  7  FETCH vc INTO vc1, vc2;
  8  dbms_output.put_line(vc1 || ' ' || vc2);
  9  END;
 10  /
A a                                                                             
 
PL/SQL procedure successfully completed.

Au lieu de modifier votre code chaque fois avant de le poster, essayez plutôt soit de le poster tel quel ou si ce n'est pas possible essayez de construire un petit test complet en SQL avec toutes les étapes complètes: création de tous les objets (tables, package) ainsi que le code de test.

[remi4444]

J'ai pas tout compris, mais moi aussi j'ai fait une petite démo de ce qui fonctionne chez moi, avec tout ça, tu vas bien finir par t'en sortir....

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
 
 
SQL> create table TableClient (nom varchar2(16), prenom varchar2(16), adresse varchar2(16),
  2   codepostal varchar2(16), ville varchar2(16));
 
Table créée.
 
SQL> insert into TableClient values ('MACHIN','TOTO','rue truc','44000','NANTES');
 
1 ligne créée.
 
SQL> insert into TableClient values ('bidule','titi','bd du truc','33000','Bordeaux');
 
1 ligne créée.
 
SQL> commit;
 
Validation effectuée.
 
SQL> create package pkgType is
  2    TYPE Type_Test is ref cursor;
  3    end pkgType;
  4  /
 
Package créé.
 
SQL> create Procedure Test (F_Test out pkgType.Type_Test,
  2                           pNom IN TableClient.Nom%type)
  3      IS
  4      Str_chaine varchar2(1000);
  5      Str_table varchar2(20);
  6      begin
  7      Str_table:='TableClient';
  8      Str_chaine :=' Select nom, prenom, adresse, codepostal, ville '||
  9      ' From ' || Str_table ||
 10     ' Where nom = :pNom';
 11     Open F_Test for str_chaine using pNom;
 12     end;
 13  /
 
Procédure créée.
 
SQL> var CC REFCURSOR;
SQL> BEGIN
  2      Test (:CC,'MACHIN');
  3      END;
  4  /
 
Procédure PL/SQL terminée avec succès.
 
SQL> print CC
 
NOM              PRENOM           ADRESSE          CODEPOSTAL
---------------- ---------------- ---------------- ----------------
VILLE
----------------
MACHIN           TOTO             rue truc         44000
NANTES
 
 
SQL>

[remi4444]

Je n'ai pas de Oracle9i sous la main, mais je crois que cette version accepte le type 'SYS_REFCURSOR', ce qui évite de devoir creer un type intermédiaire dans un package comme en 8i.

La procédure est alors simplifiée par rapport à ce qu'on t'a donné plus haut:

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
 
 
SQL> create table TableClient (nom varchar2(16), prenom varchar2(16), adresse varchar2(16),
 codepostal varchar2(16), ville varchar2(16));
 
Table créée.
 
SQL> insert into TableClient values ('MACHIN','TOTO','rue truc','44000','NANTES');
 
1 ligne créée.
 
SQL> insert into TableClient values ('bidule','titi','bd du truc','33000','Bordeaux');
 
1 ligne créée.
 
SQL> commit;
 
Validation effectuée.
 
SQL> create Procedure Test2 (F_Test out sys_refcursor,
  2                           pNom IN TableClient.Nom%type)
  3      IS
  4      Str_chaine varchar2(1000);
  5      Str_table varchar2(20);
  6      begin
  7      Str_table:='TableClient';
  8      Str_chaine :=' Select nom, prenom, adresse, codepostal, ville '||
  9      ' From ' || Str_table ||
 10     ' Where nom = :pNom';
 11     Open F_Test for str_chaine using pNom;
 12     end;
 13  /
 
Procédure créée.
 
SQL> var cc refcursor;
SQL> begin
  2  Test2(:cc,'bidule');
  3  end;
  4  /
 
Procédure PL/SQL terminée avec succès.
 
SQL> print cc
 
NOM              PRENOM           ADRESSE          CODEPOSTAL
---------------- ---------------- ---------------- ----------------
VILLE
----------------
bidule           titi             bd du truc       33000
Bordeaux
 
 
SQL>

[Maglight]

Merci beaucoup pour votre aide à tous. Cela fonctionne maintenant.
Ouf