How create a stored procedure for Oracle with DDL

Question

How create a stored procedure for read the tables into a schema, and for all tables create a .sql file with the DDL (only CREATE TABLE and PRIMARY KEY).

The input parameter is the schema.

Thank you so much

Answer 1

You'll need to change 'MY_DIRECTORY' to an Oracle Directory object that you can write to. Also, this'll take a little while to run - GET_DDL is not fast.

create or replace procedure table_ddl_to_file(schema_name in varchar2)
is
    v_clob clob := null;
begin
    for t in (select distinct table_name from all_tables where owner = schema_name)
    loop
        v_clob := v_clob || DBMS_METADATA.GET_DDL('TABLE',t.table_name, schema_name) || chr(10);
    end loop;

    DBMS_XSLPROCESSOR.CLOB2FILE(v_clob, 'MY_DIRECTORY', schema_name || '_table_ddl.sql');
end;
/