dbms_metadata.get_ddl( object_type IN VARCHAR2, name IN VARCHAR2, schema IN VARCHAR2 DEFAULT NULL, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE', transform IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;
Table CREATE TABLE test PCTFREE 0 TABLESPACE uwdata AS SELECT table_name, tablespace_name FROM user_tables;
SET LONG 10000
SELECT dbms_metadata.get_ddl('TABLE', 'TEST') FROM dual;
View CREATE OR REPLACE VIEW my_tables AS select table_name, tablespace_name FROM user_tables;
SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES') FROM dual;
Function CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS
BEGIN RETURN user; END whoami; /
SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI') FROM dual;
Tablespace SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA') FROM dual;
Fetch DDL for dependent objects (audits, object grants)
dbms_metadata.get_dependent_ddl( object_type IN VARCHAR2, base_object_name IN VARCHAR2, base_object_schema IN VARCHAR2 DEFAULT NULL, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE', transform IN VARCHAR2 DEFAULT 'DDL', object_count IN NUMBER DEFAULT 10000) RETURN CLOB;
GRANT select ON servers TO hr; GRANT select ON servers TO scott;
set long 100000
SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT','SERVERS') FROM dual;
Fetch granted objects (system grants, role grants) DDL
dbms_metadata.get_granted_ddl( object_type IN VARCHAR2, grantee IN VARCHAR2 DEFAULT NULL, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE', transform IN VARCHAR2 DEFAULT 'DDL', object_count IN NUMBER DEFAULT 10000) RETURN CLOB;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'UWCLASS') FROM dual;
SET_TRANSFORM_PARAM
Specify parameters to the XSLT stylesheet identified by transform_handle.Use them to modify or customize the output of the transform
Overload 1
dbms_metadata.set_transform_param( transform_handle IN NUMBER, name IN VARCHAR2, value IN VARCHAR2), object_type IN VARCHAR2 DEFAULT NULL);
set long 2000000 set pagesize 0
SELECT dbms_metadata.get_ddl('TABLE', 'T1') FROM dual;
-- omit the storage clause exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'STORAGE', FALSE);
SELECT dbms_metadata.get_ddl('TABLE', 'T1') FROM dual;
dbms_metadata.set_transform_param( transform_handle IN NUMBER, name IN VARCHAR2, value IN BOOLEAN DEFAULT TRUE, object_type IN VARCHAR2 DEFAULT NULL);
TBD
Overload 3
dbms_metadata.set_transform_param( transform_handle IN NUMBER, name IN VARCHAR2, value IN NUMBER,), object_type IN VARCHAR2 DEFAULT NULL);
TBD
Demo (copied from http://www.orafaq.com/node/59)
CREATE TYPE tableddl_ty AS OBJECT ( table_name VARCHAR2(30), orig_schema VARCHAR2(30), orig_ddl CLOB, comp_schema VARCHAR2(30), comp_ddl CLOB); /
CREATE TYPE tableddl_ty_tb AS TABLE OF tableddl_ty; /
CREATE OR REPLACE FUNCTION tableddl_fc (input_values SYS_REFCURSOR) RETURN tableddl_ty_tb PIPELINED IS
PRAGMA AUTONOMOUS_TRANSACTION;
-- variables to be passed in by sys_refcursor */ table_name VARCHAR2(30); orig_schema VARCHAR2(30); comp_schema VARCHAR2(30);
-- setup output record of TYPE tableddl_ty out_rec tableddl_ty := tableddl_ty(NULL,NULL,NULL,NULL,NULL);
/* setup handles to be used for setup and fetching metadata information handles are used to keep track of the different objects (DDL) we will be referencing in the PL/SQL code */ hOpenOrig0 NUMBER; hOpenOrig NUMBER; hOpenComp NUMBER; hModifyOrig NUMBER; hTransDDL NUMBER; dmsf PLS_INTEGER;
/* CLOBs to hold DDL Orig_ddl0 will hold the baseline DDL for the object to be compared Orig_ddl1 will also hold the baseline DDL for the object to be compared against but will also go through some translations before being compared against Comp_ddl2 Comp_ddl2 will contain the DDL to be compared against the baseline */ Orig_ddl0 CLOB; Orig_ddl1 CLOB; Comp_ddl2 CLOB;
ret NUMBER; BEGIN /* Strip off Attributes not concerned with in DDL. If you are concerned with TABLESPACE, STORAGE, or SEGMENT information just comment out these few lines. */ dmsf := dbms_metadata.session_transform dbms_metadata.set_transform_param(dmsf, 'TABLESPACE', FALSE); dbms_metadata.set_transform_param(dmsf, 'STORAGE', FALSE); dbms_metadata.set_transform_param(dmsf, 'SEGMENT_ATTRIBUTES', FALSE);
-- Loop through each of the rows passed in by the reference cursor LOOP /* Fetch the input cursor into PL/SQL variables */ FETCH input_values INTO table_name, orig_schema, comp_schema; EXIT WHEN input_values%NOTFOUND;
/* Here is the first use of our handles for pointing to the original table DDL It names the object_type (TABLE), provides the name of the object (our PL/SQL variable table_name), and states the schema it is from */ hOpenOrig0 := dbms_metadata.open('TABLE'); dbms_metadata.set_filter(hOpenOrig0,'NAME',table_name); dbms_metadata.set_filter(hOpenOrig0,'SCHEMA',orig_schema);
/* Setup handle again for the original table DDL that will undergo transformation We setup two handles for the original object DDL because we want to be able to Manipulate one set for comparison but output the original DDL to the user */ hOpenOrig := dbms_metadata.open('TABLE'); dbms_metadata.set_filter(hOpenOrig,'NAME',table_name); dbms_metadata.set_filter(hOpenOrig,'SCHEMA',orig_schema);
-- Setup handle for table to compare original against hOpenComp := dbms_metadata.open('TABLE'); dbms_metadata.set_filter(hOpenComp,'NAME',table_name); dbms_metadata.set_filter(hOpenComp,'SCHEMA',comp_schema);
/* Modify the transformation of "orig_schema" to take on ownership of "comp_schema" If we didn't do this, when we compared the original to the comp objects there would always be a difference because the schema_owner is in the DDL generated */ hModifyOrig := dbms_metadata.add_transform(hOpenOrig,'MODIFY'); dbms_metadata.set_remap_param(hModifyOrig,'REMAP_SCHEMA',orig_schema,comp_schema);
-- This states to created DDL instead of XML to be compared hTransDDL := dbms_metadata.add_transform(hOpenOrig0,'DDL'); hTransDDL := dbms_metadata.add_transform(hOpenOrig ,'DDL'); hTransDDL := dbms_metadata.add_transform(hOpenComp ,'DDL');
-- Get the DDD and store into the CLOB PL/SQL variables Orig_ddl0 := dbms_metadata.fetch_clob(hOpenOrig0); Orig_ddl1 := dbms_metadata.fetch_clob(hOpenOrig);
/* Here we are providing for those instances where the baseline object does not exist in the Comp_schema. */ BEGIN Comp_ddl2 := dbms_metadata.fetch_clob(hOpenComp); EXCEPTION WHEN OTHERS THEN comp_ddl2 := 'DOES NOT EXIST'; END;
-- Now simply compare the two DDL statements and output row if not equal ret := dbms_lob.compare(Orig_ddl1, Comp_ddl2); IF ret != 0 THEN out_rec.table_name := table_name; out_rec.orig_schema := orig_schema; out_rec.orig_ddl := Orig_ddl0; out_rec.comp_schema := comp_schema; out_rec.comp_ddl := Comp_ddl2; PIPE ROW(out_rec); END IF;
-- Cleanup and release the handles dbms_metadata.close(hOpenOrig0); dbms_metadata.close(hOpenOrig); dbms_metadata.close(hOpenComp); END LOOP; RETURN; END TABLEDDL_FC; /
SELECT * FROM TABLE(tableddl_fc(CURSOR(SELECT table_name, owner, 'UWCLASS' FROM dba_tables where owner = 'ABC')));