RE: Specifying column datatypes in CTAS

  • From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 24 Feb 2004 16:12:27 -0700

Something like this with the CAST operator may work for you:

SQL> create table t1 ( 
  2    col1 varchar2(100) 
  3  );

Table created.

SQL> insert into t1 values ('100');

  1  create table t2 as
  2*   select CAST( col1 AS varchar2(50)) col1 from t1
SQL> /

Table created.

SQL> desc t2;
 Name                                  Null?    Type
-------------------------------------- -------- ----------------------------
 COL1                                           VARCHAR2(50)

SQL>  select * from t2;

COL1
--------------------------------------------------
100

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Mark Richard
Sent: Tuesday, February 24, 2004 4:00 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Specifying column datatypes in CTAS





Hi List,

I have stumbled across a problem and I am hoping there is an easy solution.
I have to add two columns to an existing table.  One of these columns is
populated with a constant value for all existing records and the other can
be determined using a simple DECODE on other existing columns.

The table is quite large (~100 million records) and I was hoping to combine
the activity with a rebuild into a new tablespace at the same time.
Therefore, I figure the quickest way to achieve this is a simple "create
table as select" type statement.

My problem is specifying the datatypes, and in particular the NOT NULL
state of the newly added columns.  Working on Oracle 8.1.7.4 I get an error
if I try something like "create table my_dual (the_col varchar2(10) not
null) as select * from dual" - I cannot specify datatypes when using an "as
select..." clause it seems.

I realise I can name the new columns using column aliasing in the select
clause, but I am struggling to get the length of the data type and the NOT
NULL attribute set on the new columns.

What is the best way to resolve this problem?  Is there some syntax I am
missing or do I absolutely need to create structure and then insert data?

Thanks in advance,

Mark.




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for 
delivery of the message to such person), you may not copy or deliver this 
message to anyone.
In such a case, you should destroy this message and kindly notify the sender by 
reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet 
e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate 
to the official business of Transurban Infrastructure Developments Limited and 
CityLink Melbourne Limited shall be understood as neither given nor endorsed by 
them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: