Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

create Table as select - Adding columns with datatype

SaurabhKJan 5 2010 — edited Jan 5 2010
Hi,

I want to create a table from select with some additional columns with NULL value.
create table test01
(
	col1 number,
	col2 varchar2(10)
);
 
insert into test01 values(1,'a');
insert into test01 values(2,'e');
insert into test01 values(3,'d');
insert into test01 values(4,'c');
insert into test01 values(5,'b');
 
commit;
I want to create a table with additional column with null value. Is it possible without an update?
SQL> create table test02
  2  (
  3   col3 number,
  4   col4 varchar2(20),
  5   col5 date
  6  )
  7  as
  8  select col1 as col3, col2 as col4, null as col5 from test01;
 col3 number,
 *
ERROR at line 3:
ORA-01773: may not specify column datatypes in this CREATE TABLE
 
 
SQL> 
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 2 2010
Added on Jan 5 2010
4 comments
29,509 views