Discussion :

[Kahben]

Bonjour à tous,

J'essaye de réaliser une fonction qui permettrait, pour le nom d'une table donnée en entrer, fournir l'ensemble du contenu de la table.
Pour le moment 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
DECLARE
  tabl VARCHAR(20) := 'table1'; --------> Il n'aime pas ceci, 
  CURSOR contenu IS SELECT * FROM tabl; -------> Par contre quand je met table1 après le FROM, tout fonctionne
  tuple contenu%ROWTYPE;  
BEGIN
  OPEN contenu;
  FETCH contenu INTO tuple;
  WHILE contenu%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(tuple.nom);
    FETCH contenu INTO tuple;
  END LOOP;
  CLOSE contenu;
END;

Mon but est donc de créer une procédure de la forme :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE PROCEDURE CONTEN (tabl VARCHAR) IS 
  CURSOR contenu IS SELECT * FROM tabl; 
  tuple contenu%ROWTYPE;  
BEGIN
  OPEN contenu;
  FETCH contenu INTO tuple;
  WHILE contenu%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(tuple.nom);
    FETCH contenu INTO tuple;
  END LOOP;
  CLOSE contenu;
END;

J'ai essayé cela mais ça ne fonctionne pas. Pourriez vous m'indiquer comment avancer?

Merci à vous.

[islamov2000]

Tu n'as qu'à utiliser le sql dynamique.
Ce lien sera utile pour ça.

[Kahben]

Bonsoir, merci pour votre réponse.

Effectivement, votre solution parait plus cohérente.

Du coup j'ai désormais cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE PROCEDURE contenu (tablname VARCHAR(20)) IS
  TYPE DynCurs IS REF CURSOR;
  dyn DynCurs;
  tuple tablename%TYPE;
BEGIN
  OPEN dyn FOR 'SELECT * FROM tablname';
  FETCH dyn INTO tuple;
  WHILE dyn%FOUND LOOP
  DBMS_OUTPUT.PUT_LINE(tuple);
  FETCH dyn INTO tuple;
  END LOOP;
  CLOSE dyn;
END;

Mais la procédure ne compile pas, j'ai essayé de plusieurs manières différentes mais je ne comprend pas d'où provient l'erreur.
Ici on a bien un varchar en entrée qui correspond en fait au nom de ma table, et on l'utilise ensuite dans le SELECT....
Sauriez-vous m'aider?

[al1_24]

Mais la procédure ne compile pas

Mais elle affiche des messages d'erreur qui devraient contribuer à leur résolution

Mes commentaires dans le 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
CREATE OR REPLACE PROCEDURE contenu (tablname VARCHAR(20)) 
IS
    TYPE DynCurs IS REF CURSOR;
    dyn DynCurs;
    tuple tablename%TYPE;   -- D'où sort "tablename" ?   
BEGIN
    OPEN dyn FOR 'SELECT * FROM tablname';  -- Existe-t-il une vue ou une table nommée "tablname" ?
    --  OPEN dyn FOR 'SELECT * FROM ' || tablname;  -- Peut être comme ça
    FETCH dyn INTO tuple;
    WHILE dyn%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(tuple);    -- tuple est de type record, DBMS_OUTPUT.PUT_LINE attend une chaine
        FETCH dyn INTO tuple;
    END LOOP;
    CLOSE dyn;
END;

Je ne suis pas sur que ça fonctionnera, je n'ai pas les moyens de tester... et de toute façon j'évite le SQL dynamique qui est tout sauf performant

[islamov2000]

Il faut penser à implémenter une procédure dynamique de genre

Code :

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

execute immediate 'declare var number; begin LE CODE....; end;

[islamov2000]

Ce que tu cherches, je pense:

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
75
76
77
78
create or replace procedure z_test6 (latable varchar2)as
 
type Type_Type is table of varchar2(1000);
val_Type1 Type_Type;--Où on recupère les noms des champs
val_Type2 Type_Type;--Où on recupère les types des champs
 
Str_Type varchar2(1000):='';
Str_Affiche varchar2(500);
Str_Col varchar2(500);
 
programme varchar2(4000);--Programme dynamique
 
 
begin
 
 
select B.COLUMN_NAME, ' '||B.DATA_TYPE||
case
when B.DATA_TYPE IN('VARCHAR2','CHAR')  then
'('||B.DATA_LENGTH||')'
when B.DATA_TYPE IN('NUMBER')and B.DATA_PRECISION is not null  then
'('||B.DATA_PRECISION||decode(B.DATA_SCALE,  0,'',','||B.DATA_SCALE)||')'
end||','
 
BULK COLLECT INTO val_Type1,val_Type2--Variable de type collection 
 
from  user_tab_columns B  
where B.table_name = upper(latable) ;
 
 
 
 FOR indx IN 1 .. val_Type1.COUNT 
   LOOP
 
      Str_Type:=Str_Type||(val_Type1(indx))||(val_Type2(indx));--Aide à déclarer un RECORDS par example: type rec IS RECORD (ID number, NOM VARCHAR2(40));
      Str_Affiche:=Str_Affiche||(val_Type1(indx))||',';--Pour estétique en sortie
      Str_Col:=Str_Col||'l_tab(i).'||RTrim((val_Type1(indx)),',')||'||'',''||';--Pour estétique en sortie
   END LOOP;
 
 
Str_Type:=''||Rtrim(Str_Type,',');
Str_Col:=RTrim(Str_Col,'||');
Str_Affiche:=RTrim(Str_Affiche,'||');
 
--Programme dynamique
programme:=
 
 
'  
declare 
  l_cursor   SYS_REFCURSOR;
  type ty IS REF CURSOR;
 
  TYPE t_ptf_row IS RECORD ('||
    Str_Type
  ||');
  TYPE t_ptf_tab IS TABLE OF t_ptf_row;
   l_tab t_ptf_tab;
 
begin
OPEN  l_cursor for  ''SELECT *    FROM '||latable||''';
 
loop
FETCH l_cursor   BULK COLLECT INTO l_tab;
EXIT WHEN l_cursor%NOTFOUND;
end loop;
CLOSE l_cursor;
dbms_output.put_line('''||Str_Affiche||''');
for i in 1..l_tab.count loop
dbms_output.put_line('||Str_Col||' );  
end loop;
end;
 
  ';
 
execute immediate programme;
 
end;

Code :

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

exec z_test6('ta_table');

Bonne chance.

[skuatamad]

La vrai question à se poser est pourquoi faire une procédure générique ?

L'objectif est-il de donner un GRANT EXECUTE à un utilisateur pour qu'il puisse faire un SELECT * sur une table qu'il ne possède pas ?
Si oui autant juste donner les GRANT SELECT à ce user ou mieux à un rôle, et / ou peut être passer par une vue, bref je ne pense pas que la procédure générique soit la meilleure approche.

[islamov2000]

Je suis d’accord avec toi à propos l'utilité de cette procédure, mais j'ai vu coté faisabilité de la chose.