Create a copy of a table with an additional column using dbms_metadata

Question

You can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME'). I'm looking for a way to change the name of the table in the DDL and then execute the DDL to create an equivalent table with a new name and an additional column.

DECLARE
stmt clob;
BEGIN
    SELECT dbms_metadata.get_ddl('TABLE', 'TABLE_NAME') into stmt FROM DUAL;
    /*change the name*/
    EXECUTE IMMEDIATE(stmt);
END;

Answer 1

Try this :

CREATE TABLE XXX as SELECT * FROM YYY WHERE 1 = 0;

Answer 2

I would suggest something along the lines of:

DECLARE
stmt clob;
BEGIN

SELECT REPLACE (dbms_metadata.get_ddl('TABLE', 'TABLE_NAME'), 'CREATE TABLE ' || 'TABLE_NAME' , 'CREATE TABLE ' || 'NEW_TABLE_NAME') INTO stmt FROM DUAL;
 EXECUTE IMMEDIATE(stmt);
END;

Answer 3

Try This:

DECLARE

 v_seq NUMBER;
 v_orig_ddl CLOB;

 BEGIN

 v_seq := 13;

 v_orig_ddl := dbms_metadata.get_ddl('TABLE','TEST', 'BI');

 -- Rename Production tables with extension
 EXECUTE IMMEDIATE 'ALTER TABLE BI.TEST RENAME TO TEST_' || TO_CHAR(v_seq);

 EXECUTE IMMEDIATE v_orig_ddl; 

 END;

/