exp/imp DDL(TRIGGER,PROC,Func) via fichier plat

Version imprimable

20/08/2010, 17h29
Bat91

exp/imp DDL(TRIGGER,PROC,Func) via fichier plat

Bonjour,
Je cherche à créer un process permettant d'exporter du DDL , genre un trigger ou une procédure ou une fonction, ou une vue) d'un serveur Oracle A sur un autre serveur B. Le transfert entre eux se fera par un fichier plat.

Voici le contenu du script shell qui extrait le DDL de la base du serveur A:
=>
set pagesize 0
set verify off
set long 90000
set feedback off
set echo off
define file='&1..sql'

spool &&file
SELECT DBMS_METADATA.GET_DDL( '&2.' , '&3.' ) FROM dual;
spool off;
exit;
/
=>

Note: il appelle les 3 paramètres suivants:
1: le nom du fichier
2: le type d'objet Oracle (TRIGGER, PROCEDURE, SEQUENCE,VUE,FUNCTION)
3: le nom

Voici le contenu du script shell qui importe le DDL dans la base du serveur B:
=>
sqlplus '&1.'/'&2.'#@'&3.' @'&4.'
=>

Note: il appelle les 4 paramètres suivants:
1: le nom du user
2: le mot de passe
3: le SID Oracle
4: le nom du fichier généré par le script précédent.

Mon problème est que la fonction dbms_metadata.get_dll récupère les DDL mais n'écrit que 80 caractères par ligne dans le fichier de sortie sans se soucier du découpage. Du coup, l'exécution du deuxième script sur le serveur B provoque des erreurs de compilation.

Avez-vous une idée?
Sinon, avez-vous procédé autrement pour atteindre l'objectif de transmettre des DDL d'un serveur à un autre?
Merci de votre aide.
20/08/2010, 17h37
Pomalaix
DBMS_METADATA.GET_DDL renvoie un CLOB, qui peut potentiellement faire des milliards de caractères.
Or SQL*Plus n'affiche que le nombre de caractères défini par l'attribut LONG, 80 par défaut.

Il suffit de fixer une valeur suffisante :
Code:

1 2 SET LONG 10000 EXEC DBMS_METADATA...
23/08/2010, 09h02
Bat91

Bonjour,
c'est déjà ce que j'ai déclaré en ligne 3 du script d'extract:
Voici le contenu du script shell qui extrait le DDL de la base du serveur A:
=>
set pagesize 0
set verify off
set long 90000
set feedback off
set echo off
define file='&1..sql'

spool &&file
SELECT DBMS_METADATA.GET_DDL( '&2.' , '&3.' ) FROM dual;
spool off;
exit;
/
23/08/2010, 14h02
Pomalaix

Chez moi votre code fonctionne très bien, la sortie n'est absolument pas tronquée...

En quelle version d'Oracle êtes-vous, et sous quel OS ?
24/08/2010, 08h59
Bat91

Citation:

Envoyé par Pomalaix

Chez moi votre code fonctionne très bien, la sortie n'est absolument pas tronquée...

En quelle version d'Oracle êtes-vous, et sous quel OS ?

Bonjour, Je ne suis pas à mon poste aujourd'hui mais demain mercredi.
De tête, j'utilise Oracle 10 quelquechose sur Linux RedHat.
mes scripts shell sont exécutés directement sur le serveur Linux.
Cordialement,
24/08/2010, 10h43
argoet

Citation:

Envoyé par Pomalaix

Chez moi votre code fonctionne très bien, la sortie n'est absolument pas tronquée...

En quelle version d'Oracle êtes-vous, et sous quel OS ?

Idem :ccool: +1
25/08/2010, 14h30
Bat91

Désolé pour le retard,
la version d'Oracle est:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

La version de l'OS sur lequel j'execute le script A pour extraire les données et générer un fichier plat est:

Linux cyclonus 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:48 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

j'utilise mons script à travers putty (en ligne de commande).
J'y ai rajouté un set linesize 200 mais cela ne change pas le problème.
Je suis toujours limité à 80 colonnes.
25/08/2010, 14h52
Bat91

par contre, j'essaye de vérifier que le contenu de mon script fonctionne bien manuellement: c.a.d. en exécutant chaque ligne dans sqlplus via putty avec le même user Oracle et tout et tout.
Et surprise, lorsque j'exécute tous les set, une requête SQL qui récupère le contenu d'une table personnelle contenant les USERS affiche correctement les valeurs sur 200 caractères par ligne.
Cela veut dire que ,sans utiliser mon script ces commandes fonctionnent bien.
Je passe donc à l'étape suivante mais mauvaise surprise::: Lorsque j'exécute la commande
select DBMS_MEDATA.GET_DLL('TRIGGER','END_DATE_PRJ') from dual;

J'obtiens l'erreur suivante:
SQL> select DBMS_MEDATA.GET_DLL('TRIGGER','END_DATE_PRJ') from dual;
select DBMS_MEDATA.GET_DLL('TRIGGER','END_DATE_PRJ') from dual
*
ERROR at line 1:
ORA-00904: "DBMS_MEDATA"."GET_DLL": invalid identifier

Qu'en pensez-vous?
25/08/2010, 14h57
Bat91

Oubliez ma précédente réponse.
Vous aurez corrigé l'erreur par vous même: j'avais une erreur dans ma commande.
25/08/2010, 15h02
Bat91

Mes vérifications terminées je pense donc que cela vient de la fonction DBMS_METADATA.GET_DDL qui doit être limitée à 80 colonnes.

Citation:

Envoyé par Bat91

Mes vérifications terminées je pense donc que cela vient de la fonction DBMS_METADATA.GET_DDL qui doit être limitée à 80 colonnes.

Non, là-dessus il n'y a aucun doute !

Code:

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
SQL> set long 5000
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP', 'SCOTT') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
 
  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE DEFAULT sysdate,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX 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 FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) 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 "USERS"

26/08/2010, 08h52
Bat91

Ok, Avez-vous une autre idée pour récupérer le code ddl d'un objet sans être limité à 80 colonnes?