发布日期:2015-11-05 16:34 来源: 标签: 数据库 oracle教程 oracle函数 dbms_metadata
本章我们主要学习oracle中LOB数据类型具体有哪些,下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。
General Information
Source{ORACLE_HOME}/rdbms/admin/dbmsmeta.sql
First Available9.0.1

几个常用过程或函数:

GET_DDL

Fetch DDL for objects
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;

GET_DEPENDENT_DDL

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;

GET_GRANTED_DDL

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;

exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'DEFAULT');

Overload 2
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')));

相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。