Procédure prenant en entrée le nom d'une table

Version imprimable

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:

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:

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.

28/12/2014, 11h25
islamov2000

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

Bonsoir, merci pour votre réponse.

Effectivement, votre solution parait plus cohérente.

Du coup j'ai désormais cela :
Code:

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?

Citation:

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:

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

29/12/2014, 12h36
islamov2000

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

Code:

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

Ce que tu cherches, je pense:

Code:

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:

exec z_test6('ta_table');

Bonne chance.

29/12/2014, 13h28
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.
29/12/2014, 13h42
islamov2000

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