Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre du Club
    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 :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

  2. #2
    Expert éminent
    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.
    Franck Pachot - dbi services - Consulting et Formation en Suisse et remote - fpa@dbi-services.com
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  3. #3
    Membre éclairé
    Bonjour,

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

    https://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_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/appdev.112/e40758/d_metada.htm#ARPLS66885

  4. #4
    Membre du Club
    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.

  5. #5
    Membre éclairé
    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.

  6. #6
    Membre du Club
    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;

  7. #7
    Membre du Club
    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...

###raw>template_hook.ano_emploi###