Discussion :

[Carhiboux]

Bonjour à tous,

Je cherche à générer dynamiquement un script SQL pour recréer les indexes d'une de mes tables.

J'ai le morceau de script suivant :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Parametrage pour n'avoir que les requetes : 
set heading off;
set timing off;
set feedback off;
set echo off;
Set pages 999;
set long 90000;
 
SPOOL /opt/test.sql
select dbms_metadata.get_ddl('INDEX', index_name)||';'
from user_indexes 
where table_name = upper( 'MA_TABLE' ) 
order by index_name ;
 
SPOOL off

Ca marche plutot bien. Sauf que...

Code :

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

1
2
3
4
5
6
7
 CREATE INDEX "USER"."ABC_INDEX_A_NB" ON "USER"."MA_TABLE" ("A_NB", "A_
NB_TOTO", "A_SYS_DATE")                                      
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS                         
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              
  TABLESPACE "DATA"                                                             
 ;

A_NB_TOTO est coupé en deux entre deux lignes, et ça me fout la grouille ensuite...

Toute solution, idée ou suggestion est la bienvenue!

[Pomalaix]

Juste pour vous orienter vite fait : jetez un coup d'oeil à DBMS_METADATA.SET_TRANSFORM_PARAM, avec les attributs PRETTY et SQLTERMINATOR notamment.

GET_DDL renvoie un CLOB, qui contient en un seul tenant toutes les instructions DDL correspondant à vos filtres.
Si ça génère le DDL de 2 index à la fois par exemple, votre ";" manuel ne marchera pas, car il n'apparaîtra qu'une seule fois, à la fin.

[skuatamad]

Il faut paramétrer la longueur de la ligne et de la colonne à afficher, par exemple :

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
SQL> set long 10000
SQL> set pagesize 100
SQL> SELECT dbms_metadata.get_ddl('TABLE', 'T') as c from dual;
 
C
--------------------------------------------------------------------------------
 
  CREATE TABLE "SKUATAMAD"."T"
   (    "A" CHAR(2000),
        "B" CHAR(2000),
        "C" CHAR(2000),
        "D" CHAR(2000)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
 
 
SQL> set linesize 500
SQL> column c format A150
SQL> SELECT dbms_metadata.get_ddl('TABLE', 'T') as c from dual;
 
C
-------------------------------------------------------------------------------------------------------------------
-----------------------------------
 
  CREATE TABLE "SKUATAMAD"."T"
   (    "A" CHAR(2000),
        "B" CHAR(2000),
        "C" CHAR(2000),
        "D" CHAR(2000)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
 
 
SQL>

[Carhiboux]

Juste pour vous orienter vite fait : jetez un coup d'oeil à DBMS_METADATA.SET_TRANSFORM_PARAM, avec les attributs PRETTY et SQLTERMINATOR notamment.

GET_DDL renvoie un CLOB, qui contient en un seul tenant toutes les instructions DDL correspondant à vos filtres.
Si ça génère le DDL de 2 index à la fois par exemple, votre ";" manuel ne marchera pas, car il n'apparaîtra qu'une seule fois, à la fin.

Bonjour,

Merci pour votre réponse et votre piste!

Par contre, le GET_DDL renvoie bien les commandes de création d'index une par une. Et de ce fait, l'ajout du ";" se fait bien à la fin de chaque instruction.

J'ai coupé mon exemple de sortie pour ne vous montrer qu'un cas où cela pose problème, mais j'ai bien un ";" à la fin de chaque instruction. Avec un saut de ligne, mais ce n'est pas trop gênant.

[Carhiboux]

Ok, alors j'ai résolu le problème.

La solution :

Code sql :

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
 
-- Parametrage pour n'avoir que les requetes : 
SET heading off;
SET timing off;
SET feedback off;
SET echo off;
set long 500;
set longchunksize 500;
set linesize 500; 
 
SPOOL /opt/test.sql
SELECT dbms_metadata.get_ddl('INDEX', index_name)||';'
FROM user_indexes 
WHERE table_name = upper( 'MA_TABLE' ) 
ORDER BY index_name ;
 
SPOOL off

Et comme cela ça marche bien.

Remarque : un "set linesize 500" seul ne marche pas. Jusqu'à 80, ça marche, au delà, ça bloque à 80. Il faut rajouter les "set long 500;" et "set longchunksize 500;".

Et attention à l'ordre... il faut setter long et longchunksize AVANT de setter linesize, sinon pas d'effet.

Si quelqu’un à une idée du pourquoi du comment de la chose, je suis prenneur, parce que là je suis vraiment curieux de savoir pourquoi cela bloque à 80 caractères sans cela.

[Pomalaix]

Par contre, le GET_DDL renvoie bien les commandes de création d'index une par une. Et de ce fait, l'ajout du ";" se fait bien à la fin de chaque instruction.

J'ai coupé mon exemple de sortie pour ne vous montrer qu'un cas où cela pose problème, mais j'ai bien un ";" à la fin de chaque instruction. Avec un saut de ligne, mais ce n'est pas trop gênant.

Je me suis fié à un vieux souvenir pas frais, j'aurais mieux fait de tester pour ne pas dire une c...