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;

I think he means to create a same table structure(not including the records in the table) with a different table name. — StarPinkER, Jan 27 '13 at 09:53
Yes, but not the way like 'Create Table as Select...', I also want to add a new column to the copied table. So I think i have to work with DBMS_METADATA — Maxii, Jan 27 '13 at 09:56
I've never tried it before (and am not 100% sure it's possible) so I'm not going to answer but if you need to add an additional column then the GET_DDL subprogram is the wrong way to go. You can use `create table as select` to add an additional column, but you might lose inline constraints, which DBMS_METADATA provides. You can, however, use the XML functionality of DBMS_METADATA to create a new table rather than trying to parse the result of GET_DDL. — Ben, Jan 27 '13 at 11:31
Additionally, your comment that you want to add a new column is _extremely_ important. Please do not leave information out of your question as you have effectively invalidated the answers you have given. Always explain what you're trying to achieve as fully as possible. — Ben, Jan 27 '13 at 11:33

score 4 · Answer 1 · answered Jan 27 '13 at 09:54

4

Try this :

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

answered Jan 27 '13 at 09:54

Iswanto San

18,263
13
58
79

score 2 · Answer 2 · answered Jan 27 '13 at 10:07

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;

score 2 · Answer 3 · answered Jan 27 '13 at 16:40

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;

/

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

3 Answers3