[Oracle] Get DDL statement from SQL query

In Oracle, it's possible to get an object DDL statement via a simple SQL query, by relying on the get_ddl function in the DBMS_METADATA package.

The syntax is simple:

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;

For example, to get the DDL statement for all VIEWs under schema USER_SCHEMA:

SELECT DBMS_METADATA.get_ddl ('VIEW', view_name, 'USER_SCHEMA')
FROM user_views;

if you want tables, use:

SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, 'USER_SCHEMA')
FROM user_tables;

while if you are interested in a specific object (eg a table) you can use:

SELECT DBMS_METADATA.get_ddl ('TABLE', MY_TABLE_NAME, 'USER_SCHEMA')
FROM DUAL;