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