DBMS_METADATA Package in Oracle

All SQL statements are devided into two main categories:
DDL - data definition language;
DML - data manipulation languauge.
DML is used to change the data in the database tables. Instructions of DML are well-known for everyone: insert, update, delete. In order to save changes to database (so, the other users will see them), you need to execute commit operator. To discard all of your changes you have to execute rollback operator.
All database objects (triggers, tables, indices, etc.) have their definitions. DDL-expressions (metadata) of these objects can be extracted from the database schema. DDL-expressions will help you during database analysis and optimization.
This article will teach you, how objects’ definitions can be extracted from the Oracle database instance.

Previous versions of Oracle provide no specialized ways for DDL extraction. The only way is to execute some SQL-statements or simply export database schema and parse it. These methods have both limitations and disadvantages.
Oracle 9i (and higher) brings us a powerful package - DBMS_METADATA. Functions from DBMS_METADATA package provide an easy way to get objects’ definitions either in XML representation or in DDL one.
The main function you are going to use is get_ddl. There are two ways of calling it:

• with two parameters - object’s type and object’s name;
• with three parameters - object’s type, object’s name and schema’s name.

For example, in order to get metadata for TRIGGER1 trigger form schema SCHEMA1 you have to use this call:

select dbms_metadata.get_ddl('TRIGGER','TRIGGER1', 'SCHEMA1') from dual.

While working with your own schema, you can ommit the third parameter. The valid objects’ types are PROCEDURE, TABLE, CONSTRAINT, INDEX, etc.
You might have noticed the dual table in the SQL statement. This is auxiliary Oracle’s table. It consists of one column (name - “DUMMY”) and one record (value - “X”). Try to execute

select * from dual

and you will see it.
The owner of “dual” table is SYS, but every user of database can use it. While trying to execute valid SQL statement over the “dual” table we will get no more then one record in the result set (just believe me :)). Of course, you can modify the definition of “dual” table but I strongly recommend you not to do so.
Sometimes you will not be informed about the objects’ names. In order to get objects’ names for the current schema you can execute this sort of statement:

select * from USER_TABLES;
select * from USER_TAB_COLUMNS;
select * from USER_INDEXES;

and so on.
So, we did it. From now you can use metadata where ever you wish.
Best regards.