Discussion :

[drsbmm]

bonjour tout le monde,
SVP j'ai trouvé une grande difficulté à résoudre le problème suivant :
je voudrais écrire une procédure pl/sql qui permet d'afficher , pour une table, les noms de colonnes dont les enregistrements contiennent une chaîne de caractères passée en arguments.
il faut que j'utilise la table all_tab_columns(Owner,Table_name, Column_name,...)
svp c'est très urgent , aidez moi svp
et merci d'avance

[al1_24]

Quelle est la difficulté ?
Si tu ne nous expliques pas le problème, il sera difficile d'y apporter une solution.

[drsbmm]

Si j'ai bien compris ce problème ? la difficulté est : comment trouver les noms des colonnes selon leur contenu ??? c-à-d selon une chaîne de caractère déjà donnée ?
svp aidez moi, ma tête va exploser

[skuatamad]

Vous pouvez vous inspirer de ça (basé sur une astuce de Laurent Schneider) :

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
SQL> select * from emp2;
 
     EMPNO ENAME                JOB              MGR HIREDATE        SAL       COMM     DEPTNO NEW_COL              COL2
---------- -------------------- --------- ---------- -------- ---------- ---------- ---------- -------------------- --------------------
      7369 SMITH                CLERK           7902 17/12/80        800                    20 SMITH                SMITH
      7499 ALLEN                SALESMAN        7698 20/02/81       1600        300         30 ALLEN                ALLEN
      7521 WARD                 SALESMAN        7698 22/02/81       1250        500         30 WARD                 WARD
      7566 JONES                MANAGER         7839 02/04/81       2975                    20 JONES                JONES
      7654 MARTIN               SALESMAN        7698 28/09/81       1250       1400         30 MARTIN               MARTIN
      7698 BLAKE                MANAGER         7839 01/05/81       2850                    30 BLAKE                BLAKE
      7782 CLARK                MANAGER         7839 09/06/81       2450                    10 CLARK                CLARK
      7788 SCOTT                ANALYST         7566 09/12/82       3000                    20 SCOTT                SCOTT
      7839 KING                 PRESIDENT            17/11/81       5000                    10 KING                 KING
      7844 TURNER               SALESMAN        7698 08/09/81       1500          0         30 TURNER               TURNER
      7876 ADAMS                CLERK           7788 12/01/83       1100                    20 ADAMS                ADAMS
      7900 JAMES                CLERK           7698 03/12/81        950                    30 JAMES                JAMES
      7902 FORD                 ANALYST         7566 03/12/81       3000                    20 FORD                 FORD
      7934 MILLER               CLERK           7782 23/01/82       1300                    10 MILLER               MILLER
 
14 rows selected.
 
SQL>
SQL> create or replace procedure search_val (p_owner varchar2, p_table_name varchar2, p_val varchar2, p_cur out sys_refcursor) as
  2  begin
  3    open p_cur for
  4    WITH t AS (
  5  SELECT owner,table_name, column_name
  6    FROM ALL_TAB_COLUMNS
  7   WHERE owner      = p_owner
  8     AND table_name = p_table_name
  9  )
 10  SELECT table_name, column_name,
 11         nvl(to_number(extractvalue(
 12                     xmltype(
 13                         dbms_xmlgen.getxml ('SELECT count(*) as cnt
 14                                                FROM '|| owner ||'.'|| table_name ||
 15                                             ' WHERE to_char('||column_name||') = ''' || p_val || '''')),
 16                                             '/ROWSET/ROW/CNT'))
 17             ,0) AS nb_val_par_colonne
 18    FROM T;
 19  end;
 20  /
 
Procedure created.
 
SQL> var c refcursor
SQL> exec search_val('SKUATAMAD','EMP2','SCOTT',:c);
 
PL/SQL procedure successfully completed.
 
SQL> print c;
 
TABLE_NAME                     COLUMN_NAME                    NB_VAL_PAR_COLONNE
------------------------------ ------------------------------ ------------------
EMP2                           EMPNO                                           0
EMP2                           ENAME                                           1
EMP2                           JOB                                             0
EMP2                           MGR                                             0
EMP2                           HIREDATE                                        0
EMP2                           SAL                                             0
EMP2                           COMM                                            0
EMP2                           DEPTNO                                          0
EMP2                           NEW_COL                                         1
EMP2                           COL2                                            1
 
10 rows selected.
 
SQL>

Attention aux perfs exécrables, surtout en prod !

Sinon vous pouvez adapter cette procédure à votre besoin (pas de stockage juste de l'affichage) :
http://www.developpez.net/forums/d11...e/#post6313182

[drsbmm]

merci beaucoup Skuatamad
mais est ce que tu peux m'explique à quoi ça sert cette ligne : var c refcursor ??

ou bien en général qu'est ce que c'est "refcursor" et c'est quoi son rôle ???
je te remercie beaucoup encore une fois

[skuatamad]

à quoi ça sert cette ligne : var c refcursor ??

A partir de cette ligne j'utilise la procédure dans sqlplus.
La procédure renvoie un curseur (p_cur out sys_refcursor) il faut donc une variable de ce type lors de l'appel.