| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Specifying column datatypes in CTAS
Hi Tanel,
I think you may be correct - I was really hoping to combine it into a single statement but I guess the net result is basically the same. I was just surprised that CREATE TABLE doesn't allow specific column specs to be defined when using the AS SELECT clause. Does anyone know if this situation has changed in Oracle 9 or 10?
Regards,
Mark.
Tanel Põder
<tanel.poder.003_at_m To: <oracle-l_at_freelists.org>
ail.ee> cc:
Sent by: Subject: Re: Specifying column datatypes in CTAS
oracle-l-bounce_at_fr
eelists.org
25/02/2004 11:22
Please respond to
oracle-l
Precreate the table with the structure you like and use INSERT /*+ APPEND
*/
command to populate your table afterwards.
Tanel.
>
>
>
>
> 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.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Feb 24 2004 - 18:42:37 CST
 |
 |