30. Juli 2007

Skripte generieren: DBMS_METADATA!

Zum Auslesen von Metadaten aus der Datenbank gibt es seit einiger Zeit ein PL/SQL-Paket, welches diese Aufgabe sehr einfach löst. Der einfachste Weg ist die Funktion GET_DDL - Ein Beispiel:
select dbms_metadata.get_ddl('TABLE', 'EMP') from dual;

DDL
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
 (    "EMPNO" NUMBER(4,0),
      "ENAME" VARCHAR2(10),
      "JOB" VARCHAR2(9),
      "MGR" NUMBER(4,0),
      "HIREDATE" DATE,
      "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)
TABLESPACE "USERS"  ENABLE,
       CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
        REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
 ) 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)
TABLESPACE "USERS"
Es geht aber noch weiter. Das folgende Beispiel nimmt noch einige Einstellungen vor. So soll das DDL-Skript ...
  • ... ein anderes Schema verwenden (MYSCHEMA)
  • ... ohne Storage-Klausel generiert werden
  • ... einen SQL Terminator enthalten
create or replace function mymetadata return sys.ku$_ddls is
  md_handle number;
  tr_handle number;
  dl_handle number;

  result_array sys.ku$_ddls;
begin
  md_handle := dbms_metadata.open('TABLE');
  -- Transformationen TABLESPACE und SCHEMA
  tr_handle := dbms_metadata.add_transform(md_handle, 'MODIFY');
  dbms_metadata.set_remap_param(tr_handle, 'REMAP_TABLESPACE', 'USERS', 'EXAMPLE');
  dbms_metadata.set_remap_param(tr_handle, 'REMAP_SCHEMA', 'SCOTT', 'MYSCHEMA');

  -- Einstellungen für das DDL
  dl_handle := dbms_metadata.add_transform(md_handle, 'DDL');
  dbms_metadata.set_transform_param(dl_handle, 'SEGMENT_ATTRIBUTES', false);
  dbms_metadata.set_transform_param(dl_handle, 'SQLTERMINATOR', true);

  -- Welche Objekte?
  dbms_metadata.set_filter(md_handle, 'NAME', 'EMP');

  result_array := dbms_metadata.fetch_ddl(md_handle);
  dbms_metadata.close(md_handle);
  return result_array;
end;
/
Das Ergebnis:
select ddltext from table(mymetadata);

DDLTEXT
---------------------------------------------------------------------------
CREATE TABLE "MYSCHEMA"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "MYSCHEMA"."DEPT" ("DEPTNO") ENABLE
   ) ;
Mit DBMS_METADATA kann die DDL-Generierung beliebig angepasst werden. Auch das Generieren der Metadaten als XML-Dokument ist möglich. Mit XSLT-Stylesheets kann die Ausgabe dann beliebig formatiert werden. HTML, PDF oder andere Formate sind dann kein Problem mehr ...

Beliebte Postings