Oracle get column ddl from dba_tab_columns dictionary view

Query to generate alter table add column DDL from Oracle dictionary views:

There will be a situation when we might need to generate add column DDLs for a particular table or all tables in a schema.

Oracle data dictionary view dba_tab_columns/all_tab_columns stores column details such as

DATA_TYPE – column data type

DATA_LENGTH – Length of the column

DATA_PRECISION – Decimal precision for NUMBER type

DATA_SCALE – Digits to right side of decimal point in NUMBER type

Below example covers following data types:

SQL> DESC GRAK_TEST;
 Name Null? Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID NUMBER(38)
 ID1 NUMBER(15,2)
 ID2 NUMBER
 ID3 VARCHAR2(45)
 ID4 VARCHAR2(5)
 ID6 FLOAT(126)
 ID7 CHAR(1)
 ID8 CHAR(100)
 ID9 BLOB
 ID10 CLOB
 ID11 TIMESTAMP(6)
 ID12 TIMESTAMP(6) WITH TIME ZONE
 ID13 LONG
 ID14 DATE

Below query will get us the all the table column DDLs for a particular table:

SQL> SELECT 'alter table '||table_name||' add '||COLUMN_NAME|| ' '|| DATA_TYPE||decode(DATA_TYPE,'CHAR','('||DATA_LENGTH||')','VARCHAR2','('||DATA_LENGTH||')','NUMBER',DECODE(DATA_SCALE,'','','('||DECODE(DATA_PRECISION,'',38,DATA_PRECISION)||','||DATA_SCALE||')' ) ) ||';' myddl from DBA_TAB_COLUMNS where TABLE_NAME='GRAK_TEST';

MYDDL
----------------------------------------------------------------------------------------------------
alter table GRAK_TEST add ID NUMBER(38,0);
alter table GRAK_TEST add ID1 NUMBER(15,2);
alter table GRAK_TEST add ID2 NUMBER;
alter table GRAK_TEST add ID3 VARCHAR2(45);
alter table GRAK_TEST add ID4 VARCHAR2(5);
alter table GRAK_TEST add ID6 FLOAT;
alter table GRAK_TEST add ID7 CHAR(1);
alter table GRAK_TEST add ID8 CHAR(100);
alter table GRAK_TEST add ID9 BLOB;
alter table GRAK_TEST add ID10 CLOB;
alter table GRAK_TEST add ID11 TIMESTAMP(6);
alter table GRAK_TEST add ID12 TIMESTAMP(6) WITH TIME ZONE;
alter table GRAK_TEST add ID13 LONG;
alter table GRAK_TEST add ID14 DATE;

14 rows selected.

Leave a Reply

Your email address will not be published. Required fields are marked *