oracle 来自 dbms_metadata.get_ddl 的自定义表 DDL

Customized Table DDL from dbms_metadata.get_ddl

提问by nirvanastack

I want DDLs of Tables so that I can put it to the database server using UTLPackage in Oracle.

我想要DDLs 个表，以便我可以使用UTLOracle 中的 Package将其放入数据库服务器。

The problem is : When we

问题是：当我们

select dbms_metadata.get_ddl('TABLE','Table_name','Schema_Name') 
  from dual; 

I get an output which contains CLOBbefore CREATE. Like

我得到一个包含CLOBbefore的输出CREATE。喜欢

"(CLOB) 
  CREATE TABLE "SCHEMA"."TABLE_NAME" 
   (TABLE_COLUMNS.....) 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 DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "INTF_DATA01" ;"

What I want is the above DDLwithout CLOBand without the parameters such as SEGMENT CREATION, PCTFREE... ETC..

我想要的是上面DDL没有CLOB和没有参数，例如SEGMENT CREATION，PCTFREE...... ETC ..

Would be glad to get advise.

很高兴得到建议。

回答by APC

We can use the DBMS_METADATA.SET_TRANSFORM_PARAM method to control what clauses are included in the generated DDL.

我们可以使用 DBMS_METADATA.SET_TRANSFORM_PARAM 方法来控制生成的 DDL 中包含哪些子句。

For instance to exclude SEGMENT stuff you would execute this

例如，要排除 SEGMENT 内容，您将执行此操作

exec dbms_metadata.set_transform_param
   (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

before you execute your get_ddl()call.

在执行get_ddl()调用之前。

The package documentation lists all the available Trandformation parameters. Find out more.

包文档列出了所有可用的 Transformation 参数。了解更多。

---

---

I think Alex is right, and the "(CLOB)" at the front is something your client is prepending to the output. But it were real and not an artefact getting rid of it would be a simple string manipulation:

我认为亚历克斯是对的，前面的“（CLOB）”是您的客户在输出之前的内容。但它是真实的而不是人工制品，摆脱它只是一个简单的字符串操作：

substr(your_string, 7) 

回答by Raj

1. Please use below logic: it is working for me

1. 请使用以下逻辑：它对我有用

---

---

CREATE OR REPLACE TYPE ddl_ty AS OBJECT (   object_name
VARCHAR2(30),   object_type VARCHAR2(30),   orig_schema
VARCHAR2(30),   orig_ddl    CLOB ) / CREATE OR REPLACE TYPE
ddl_ty_tb AS TABLE OF ddl_ty /

CREATE OR REPLACE FUNCTION get_object_ddl (input_values
SYS_REFCURSOR) RETURN ddl_ty_tb PIPELINED IS

PRAGMA AUTONOMOUS_TRANSACTION;

-- variables to be passed in by sys_refcursor */ object_name  VARCHAR2(30); object_type  VARCHAR2(30); orig_schema VARCHAR2(30);


-- setup output record of TYPE tableddl_ty out_rec ddl_ty := ddl_ty(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 */ hOpenOrig  
NUMBER; hModifyOrig NUMBER; hTransDDL   NUMBER; dmsf       
PLS_INTEGER; Orig_ddl  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);   dbms_metadata.set_transform_param(dmsf, 'PRETTY', TRUE);  
dbms_metadata.set_transform_param(dmsf, 'SQLTERMINATOR', TRUE);

  -- 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 object_name, orig_schema, object_type;
    EXIT WHEN input_values%NOTFOUND;

    hOpenOrig := dbms_metadata.open(object_type);
    dbms_metadata.set_filter(hOpenOrig,'NAME',object_name);
    dbms_metadata.set_filter(hOpenOrig,'SCHEMA',orig_schema);

    hModifyOrig := dbms_metadata.add_transform(hOpenOrig,'MODIFY');
    dbms_metadata.set_remap_param(hModifyOrig,'REMAP_SCHEMA',orig_schema,null);

    -- This states to created DDL instead of XML to be compared
    hTransDDL := dbms_metadata.add_transform(hOpenOrig ,'DDL');

    Orig_ddl := dbms_metadata.fetch_clob(hOpenOrig);

      out_rec.object_name := object_name;
      out_rec.object_type := object_type;
      out_rec.orig_schema := orig_schema;
      out_rec.orig_ddl := Orig_ddl;
      PIPE ROW(out_rec);

    -- Cleanup and release the handles
    dbms_metadata.close(hOpenOrig);

  END LOOP;   RETURN; END get_object_ddl; / SELECT *   FROM
TABLE(get_object_ddl(CURSOR (SELECT object_name, owner, object_type
                               FROM dba_objects
                              WHERE owner = 'EMP'
                                    AND object_type IN
                                    ('VIEW',
                                         'TABLE',
                                         'TYPE',
                                         'PACKAGE',
                                         'PROCEDURE',
                                         'FUNCTION',
                                         'SEQUENCE'))));

---

---

1. List item

1. 项目清单

回答by Mark Harrison

get_ddlreturns a CLOB. Cast it to a character type and your library won't add the (CLOB).

get_ddl返回一个 CLOB。将其转换为字符类型，您的库不会添加 (CLOB)。

select to_char(dbms_metadata.get_ddl('TABLE','Table_name','Schema_Name'))
    from dual;