Récupérer le texte de création d'un index contenant une fonction

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:

---

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:

---

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:

---

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

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.

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

Bonjour,

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

Code:

---

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:

---

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.

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.

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

Code:

---

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;

---

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

Code:

---

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...