Discussion :

[marieo]

Bonjour,

J'aimerais savoir s'il est possible de récupérer dans Oracle (11gR2) le texte de création d'un index contenant une fonction.

Depuis SQL Developer, je peux récupérer ce texte dans l'onglet "Index" de ma table :

Code :

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

1
2
3
4
 
INDEX_OWNER  INDEX_NAME  UNIQUENESS STATUS INDEX_TYPE             TEMPORARY  PARTITIONED  FUNCIDX_STATUS  JOIN_INDEX  COLUMNS                     COLUMN_EXPRESSION
NOM_PROP      IDX_MONIDX  NONUNIQUE  VALID  FUNCTION-BASED NORMAL  N         NO             ENABLED        NO          SYS_NC00057$,SYS_NC00058$   TO_CHAR("DATE_SORTIE",'yyyy')
NOM_PROP      IDX_MONIDX  NONUNIQUE  VALID  FUNCTION-BASED NORMAL  N         NO             ENABLED        NO          SYS_NC00057$,SYS_NC00058$   TO_CHAR("DATE_SORTIE",'mm')

Depuis SQL Developer, voici le descriptif de mon index (onglet "Colonnes" lorsque je choisis l'index)

Code :

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

1
2
3
4
 
INDEX_OWNER INDEX_NAME  TABLE_OWNER  TABLE_NAME  COLUMN_NAME  COLUMN_POSITION DESCEND
NOM_PROP     NOM_INDEX  NOM_PROP     NOM_TABLE   SYS_NC00057$  1               ASC
NOM_PROP     NOM_INDEX  NOM_PROP     NOM_TABLE   SYS_NC00058$  2               ASC

Depuis SQL Developer, voici le texte que je récupère depuis l'onglet "SQL"

Code :

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

1
2
 
CREATE INDEX NOM_PROP.NOM_INDEX ON NOM_PROP.NOM_TABLE (TO_CHAR("DATE_SORTIE",'yyyy'), TO_CHAR("DATE_SORTIE",'mm'));

Ma question est : comment récupérer dans les métadonnées Oracle le texte complet de création de l'index (avec la "traduction" entre les colonnes "SYS_NC00057$" et TO_CHAR("DATE_SORTIE",'yyyy') et "SYS_NC00058$" et TO_CHAR("DATE_SORTIE",'mm').

Merci beaucoup pour les solutions que vous pourriez me proposer.

MarieO

[pachot]

Dans quel but? SYS_NC00057$ est un nom généré. On ne doit pas le mettre dans le CREATE. Imaginez que ce no existe déjà par exemple.

[vanagreg]

Bonjour,

Tu peux retrouver le texte des FBI (Function-Based-Indexes) dans la vue ALL_IND_EXPRESSIONS:

https://docs.oracle.com/cd/E18283_01...views_1101.htm

Sinon pour reconstruire l'instruction DDL d'un index tu peux utiliser la fonction DBMS_METADATA.GET_DDL:

https://docs.oracle.com/cd/E11882_01...htm#ARPLS66885

[marieo]

Bonjour,

Je viens de tester la requête suivante sur ma base de données:

Code :

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

1
2
3
4
5
 
SELECT table_owner, index_owner, table_name, index_name, 
     listagg(column_expression,',') within group 
	   (order by table_owner, table_name, index_name, column_position) as list_columns
   FROM all_ind_expressions where index_owner='NOM_PROP';

et j'ai le message d'erreur ci-dessous :

Code :

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

1
2
3
4
5
6
 
ORA-00932: types de données incohérents ; attendu : NUMBER ; obtenu : LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Erreur à la ligne 2, colonne 14

A quoi cela est-il dû et comment faire pour corriger cette anomalie ?

Merci beaucoup.

[vanagreg]

Bonjour,

Tu obtiens l'erreur car tu appliques une fonction sur une colonne de type LONG. Ce type de données n'est pas du tout pratique à manipuler, et pourtant Oracle continue de l'utiliser dans son dictionnaire de données!
Il faut que tu passes par du pl/sql. Par exemple:
https://blog.mclaughlinsoftware.com/...t-long-to-clob

De plus, ta requête ne fonctionnera pas car il manquera un GROUP BY.

[marieo]

Effectivement, il manque la ligne GROUP BY à ma requête SQL

Code :

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

1
2
3
4
5
6
7
 
SELECT table_owner, index_owner, table_name, index_name, 
     listagg(column_expression,',') within group 
	   (order by table_owner, table_name, index_name, column_position) as list_columns
   FROM all_ind_expressions where index_owner='NOM_PROP'
GROUP BY table_owner, index_owner, table_name, index_name
ORDER BY table_name, index_name;

[marieo]

J'ai fini par trouver comment faire, en suivant les idées de vanagreg

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT owner, table_name, index_name, index_type, table_owner, table_type,
     trim(replace(replace(replace(
	        substr(DBMS_METADATA.GET_DDL('INDEX',index_name,'NOM_PROP'),1,
                   instr(DBMS_METADATA.GET_DDL('INDEX',index_name,'NOM_PROP'),'PCTFREE')-1),
          chr(10)),chr(13)),'"'))) as index_text
   FROM all_indexes 
   WHERE owner='NOM_PROP'
    AND Not Exists(SELECT 1 FROM all_constraints WHERE all_indexes.owner=all_constraints.owner 
	                  AND all_indexes.table_name=all_constraints.table_name
                      AND all_indexes.index_name=all_constraints.index_name)
   ORDER BY table_owner, table_name, index_name;

Un grand merci à tous pour vos idées et votre aide...