Oracle PL/SQL/System Packages/DBMS METADATA

Материал из SQL эксперт
Перейти к: навигация, поиск

dbms_metadata.get_ddl( "TABLE", "TABLENAME" )

   <source lang="sql">

SQL> SQL> create table t

 2  ( x int primary key,
 3    y date,
 4    z clob
 5  )
 6  /

Table created. SQL> select dbms_metadata.get_ddl( "TABLE", "T" ) from dual; DBMS_METADATA.GET_DDL("TABLE","T")


 CREATE TABLE "sqle"."T"
  (    "X" NUMBER(*,0),
       "Y" DATE,
       "Z" CLOB,
        P

SQL> SQL> SQL> SQL> drop table t; Table dropped. SQL>

 </source>
   
  


Save value returned from DBMS_METADATA.GET_XML to xmltype

   <source lang="sql">

SQL> create table myTable

 2  (myID  number primary key,
 3  ddl     xmltype );

Table created. SQL> SQL> create index all_idx on myTable(extractvalue(ddl,"/ROWSET/ROW/TABLE_T/SCHEMA_OBJ/NAME"))

 2

SQL> insert into myTable values (1,xmltype((SELECT DBMS_METADATA.GET_XML("TABLE","emp","sqle")FROM DUAL)))

 2  select index_name, index_type from user_indexes
 3  where table_name = "myTable"
 4

SQL> drop table myTable; Table dropped. SQL>

 </source>