Extract ddl for alter statement in oracle

Question

I'm working on an automation to generate the ddl automatically. Ofcourse I'm using DBMS_METADATA.GET_DDL to get the same. It works fine. Now I need help on the following.

Assume TableA is already there in the database with three column COLA,COLB & COLC

Lets say I've modified the table by adding another column COLD

Now I wanted to generate the below script automatically,

ALTER TABLE TABLEA ADD COLUMN COLD VARCHAR2(100);

Suggest me a way to accompolish the same.

In general you should not do DDL "manually". First put the statements into a SQL script. Test the script, then put it into your version control system. If you follow that guideline you never need to "reverse engineer" your changes. If you have the old definition around, you can use dbms_metadata_diff: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metadiff.htm#ARPLS354 — , Aug 22 '14 at 17:23

score 3 · Answer 1 · answered Sep 14 '14 at 23:51

DBMS_METADATA_DIFF generates DDL difference scripts.

create user test_user1 identified by test_user1;
create user test_user2 identified by test_user2;

create table test_user1.tablea(cola number, colb number, colc number);
create table test_user2.tablea(cola number, colb number, colc number
    ,cold varchar2(100));

select
    dbms_metadata_diff.compare_alter(
        object_type => 'TABLE',
        name1 =>       'TABLEA',
        name2 =>       'TABLEA',
        schema1 =>     'TEST_USER1',
        schema2 =>     'TEST_USER2'
    ) alter_ddl
from dual;


ALTER_DDL
---------
ALTER TABLE "TEST_USER1"."TABLEA" ADD ("COLD" VARCHAR2(100))

That's a technical solution. From a process perspective, I agree with a_horse_with_no_name that it's generally better to hand-code those things and use version control. However, in practice, database programmers rarely use version control in a meaningful way. I don't expect you to suddenly switch methods, but do consider that approach when the next project starts.

Extract ddl for alter statement in oracle

1 Answers1