DBMS > metadata usage

Time:2020-2-17

Oracle database provides DBMS metadata API to facilitate users to obtain DDL statements for creating database objects. The extended purpose includes comparing the differences of objects in different databases. Next, I will introduce the simple application of DBMS ﹣ metadata

Get database object to create DDL

A more general approach

You can use the following PL / SQL function to get the DDL statement of an object

CREATE OR REPLACE FUNCTION get_table_md
  RETURN CLOB
IS
  -- Define local variables.
  h  NUMBER; --handle returned by OPEN
  th NUMBER; -- handle returned by ADD_TRANSFORM
  doc CLOB;
BEGIN
  -- Specify the object type.
  h := DBMS_METADATA.OPEN('TABLE');
  dbms_output.put_line('H is '||h );
  -- Use filters to specify the particular object desired.
  DBMS_METADATA.SET_FILTER(h,'SCHEMA','APPLSYS');
  DBMS_METADATA.SET_FILTER(h,'NAME','FND_USER');
  -- Request that the metadata be transformed into creation DDL.
  th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
  -- Fetch the object.
  doc := DBMS_METADATA.FETCH_CLOB(h);
  -- Release resources.
  DBMS_METADATA.CLOSE(h);
  RETURN doc;
END;
/ 

After creating the get table MD function in the database, you can obtain the DDL statement in the following ways:

SET PAGESIZE 0
SET LONG 1000000
SELECT get_table_md from dual;

More convenient method

In the newer version of Oracle (above 11.1 in my observation), you can directly use DBMS? Metadata. Get? DDL method to get DDL statements.

SET PAGESIZE 0
SET LONG 1000000
select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;

Possible problems

When using DBMS? Metadata. Get? DDL, you may encounter an error similar to the following

*
ERROR at line 1:
ORA-31603: object "OBJECT1" of type TYPE not found in schema "USER1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at "USER2.TEST", line 4
ORA-06512: at line 1

According to the MOS article DBMS? Metadata. Get? DDL returns error when select types ora-31603 (document ID 312883.1), this problem is caused by the user’s lack of select? Catalog? Role permission. After granting this permission to the corresponding user, the problem can be solved.