Export des procédures/fonctions/package d’une instance Oracle

Publié le par

Après avoir galéré plusieurs heures afin de pouvoir exporter facilement chaque procédure / package / fonction d’une instance Oracle dans un fichier il me semblait nécessaire de créer un post sur le sujet.

Première chose à savoir : où se trouve le contenu de ces objets ?
2 réponse à cette question :

  • dans la table USER_SOURCE pour le schéma courant
  • dans la table DBA_SOURCE pour le schéma SYSTEM afin d’avoir un listing exhaustif de tous les objets

Au regard de ces tables il est donc possible de filtrer sur un schéma et/ou des types d’objets en particulier.

Deuxième élément à connaitre : comment concaténer le contenu du résultat du SELECT sur la table USER_SOURCE ou DBA_SOURCE ?
Réponse : utiliser la fonction DBMS_METADATA.GET_DDL() qui prend en paramètre le type d’objet, le nom de l’objet et schéma.

Troisième et dernier élément : comment écrire tout cela dans un fichier ?
Réponse : en utilisant non pas les procédures du package UTL_FILE comme PUT_RAW() (comme j’avais tenté de le faire en premier lieu…) mais en utilisant la procédure dbms_advisor.create_file() qui prend en paramètre le contenu à écrire (un CLOB), le dossier cible et le nom du fichier.

A savoir que UTL_FILE.PUT_RAW() ne peut écrire un fichier plus gros que 32766Byte… d’où l’utilisation de dbms_advisor.

Un pré-requis également à connaitre : il faut que le compte SYSTEM puisse écrire dans le DIRECTORY cible.

Et voilà donc le résultat :

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
DECLARE

    vContent CLOB;
    vFile utl_file.file_type;
    vCurrentUser VARCHAR2(30) DEFAULT user;
    vDirectoryName VARCHAR2(255) default 'DATA_PUMP_DIR';
    vFileName VARCHAR2(1000);

BEGIN

    FOR C1 IN (
        SELECT distinct OWNER, NAME, TYPE
        FROM DBA_SOURCE
        WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'XDB', 'EXFSYS', 'CTXSYS', 'MDSYS', 'ORDSYS', 'OLAPSYS', 'WMSYS', 'SYSMAN', 'ORDPLUGINS')
        AND OWNER  NOT LIKE 'APEX%'
        AND TYPE IN ('PACKAGE', 'FUNCTION', 'PROCEDURE')
    ) LOOP


        vCurrentUser := C1.OWNER;

        SELECT DBMS_METADATA.GET_DDL(C1.TYPE, C1.NAME, vCurrentUser)
        INTO vContent
        FROM dual;
       
        vFileName := vCurrentUser || '.' || C1.TYPE || '.' || C1.NAME || '.sql';

        BEGIN

            dbms_advisor.create_file(vContent, vDirectoryName, vFileName);
     
            EXCEPTION WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Erreur lors de l''écriture dans le fichier ' || vFileName);
     
        END;

    END LOOP;

END;

Laisser un commentaire