Discussion :

[GoLDoZ]

Bonjour.
Je suis en train de "dynamiser" une procédure et je suis un peu embêté.
Pour faire simple, à partir d'une table en paramètre, je construis une requête et je veux ensuite mettre le résultat de cette requête dans un tableau (au pire dans des variables que je traiterai au fur et à mesure). Mais bien évidemment, je ne sais pas à l'avance combien de lignes ni de colonnes va me retourner la requête.
Après une journée de recherche, j'ai presque trouvé la solution mais je bloque sur la dernière étape.

Voici un script qui qui se rapproche de ce que je veux.

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
declare
  c       NUMBER;
  d       NUMBER;
  n_tab   DBMS_SQL.NUMBER_TABLE;
  indx    NUMBER := -10;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  dBMS_SQL.PARSE(c, 'select n from t order by 1', DBMS_SQL.NATIVE);
 
  DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 10, indx);
 
  d := DBMS_SQL.EXECUTE(c);
  loop
    d := DBMS_SQL.FETCH_ROWS(c);
 
    DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);
 
    EXIT WHEN d != 10;
  END LOOP;
 
  DBMS_SQL.CLOSE_CURSOR(c);
 
  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/

La "petite" différence est qu'au lieu d'avoir

Code :

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

'select n from t order by 1'

j'ai une requête du genre:

Code :

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

 'select t1.n1, t2.n2 from table1 t1, table2 t2 where t1.n3=t2.n3'

Et je n'arrive pas à gérer un tableau.
(NB: dans cet exemple, je ne comprends pas bien la gestion de 'indx' )

Merci de m'aider à résoudre ce pb dont la solution m'intéresse beaucoup.

[orafrance]

EXECUTE IMMEDIATE et BULK COLLECT c'est pas plus simple ?

[GoLDoZ]

EXECUTE IMMEDIATE et BULK COLLECT c'est pas plus simple ?

Je l'utilise déjà une fois pour récupérer les noms et types des colonnes (en allant lire dans all_tab_colums).

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
      TYPE typ_colnoms IS TABLE OF VARCHAR2 (30);
 
      TYPE typ_coltypes IS TABLE OF VARCHAR2 (106);
 
      t_colnoms         typ_colnoms;
      t_coltypes        typ_coltypes;
BEGIN
      ls_req_colonnes :=
            'SELECT column_name, data_type FROM all_tab_columns '
         || 'WHERE table_name = upper('''
         || p_table
         || ''')';
      OPEN c_colonnes FOR (ls_req_colonnes);
 
      FETCH c_colonnes
      BULK COLLECT INTO t_colnoms, t_coltypes;
 
      CLOSE c_colonnes;

Mais dans le cas présent, que mets-je derrière le INTO? Je ne connais pas à l'avance le nombre de colonnes, donc je ne peux pas définir un tableau.
Tout le pb est dans la définition et l'utilisation d'un tableau à deux dimensions.

Je ne vois pas ici comment faire, mais ce serait plus simple, bien sûr (ceci dit, cela m'intéresse de savoir utiliser dbms_sql ).

[orafrance]

ha oui... en effet... il me semble qu'on a déjà traité ce genre de problème mais si tu connais pas le nombre de colonnes j'vois pas bien comment tu vas réussir à créer ton tableau

Attention de ne pas trop abuser des tableaux quand même

[GoLDoZ]

Je ne suis pas fan des tableaux. D'ailleurs, si je peux traiter les données à la volée dans une boucle incluant les 'DBMS_SQL.COLUMN_VALUE', je le ferai.

Cependant, je peux avoir le nombre de colonnes en paramètre, même si le but est d'être dynamique au maximum.

Ceci dit, je ne suis pas loin de la solution. Voici ma procédure de travail.

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
CREATE OR REPLACE PROCEDURE proc_test
IS
   TYPE typ_big_tab IS TABLE OF DBMS_SQL.date_table;
 
   big_tab              typ_big_tab         := typ_big_tab ();
   source_cursor        INTEGER;
   ignore1               INTEGER;
   ignore2              INTEGER;
BEGIN
   source_cursor := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (source_cursor,
                   'SELECT sysdate, sysdate+1 from dual',
                   DBMS_SQL.native
                  );
   big_tab.EXTEND;
   DBMS_SQL.define_array (source_cursor, 1, big_tab (1), 1, 1);
   big_tab.EXTEND;
   DBMS_SQL.define_array (source_cursor, 2, big_tab (2), 1, 1);
 
   ignore1 := DBMS_SQL.EXECUTE (source_cursor);
   ignore2 := DBMS_SQL.fetch_rows (source_cursor);
 
   DBMS_SQL.COLUMN_VALUE (source_cursor, 1, big_tab (1));
   DBMS_SQL.COLUMN_VALUE (source_cursor, 2, big_tab (2));
 
   DBMS_SQL.close_cursor (source_cursor);
 
EXCEPTION
   WHEN OTHERS
   THEN
      IF DBMS_SQL.is_open (source_cursor)
      THEN
         DBMS_SQL.close_cursor (source_cursor);
      END IF;
      RAISE;
END;
/

Je me suis inspiré de ceci: http://www.cs.umbc.edu/help/oracle8/...ql.htm#1004875

Mes problèmes:
- j'ai du mal à comprendre les paramètres de la procédure DBMS_SQL.define_array
- le code ci-dessous compile et s'exécute, mais je n'arrive pas à accéder au contenu du tableau (via un dbms_output).

[SheikYerbouti]

Lisez cet article et téléchargez le ZIP. Il contient le script d'un package PL/SQL qui fait ce dont vous avez besoin.