Tuesday, August 30, 2011

Oracle "create table as .. select " to add new column

I came across this requirement when one of my friend wanted to create new table from existing table but with  new columns (few extra column for future use) added to it.
He doesn't wanted to write 'Alter' script


Requirement:
Add new column 'comments varchar(30)'  with default  value as null to new_table
while creating copy of existing table old_table

Solution:
create table new_table as select old_column1 as new_column1 ,old_column2 as new_column2, cast('' as varchar2(30)) as comments from old_table;

Reason:

? Why we need to perform cast of null here
If  I don't perform cast here and add new column as
, '' as new_column1
Oracle gives error like 'column cannot be of zero length...' because it creates datatype of that column based on the value provided

Like if I give new column as
'null' as comments  it considers datatype as char(4) for comments column and successfully creates new column

So, when I try to give
'' as comments   it assumes we are trying to give datatype as char(0), which is not acceptable to Oracle

So we need to cast value here as varchar2 here.

No comments:

Post a Comment