RE: Specifying column datatypes in CTAS
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Feb 2004 20:23:15 -0500
You can also specify a partitioning clause when it's needed.
Regards,
Waleed
-----Original Message-----
From: Mark Richard [mailto:mrichard@xxxxxxxxxxxxxxxxx]
Sent: Tuesday, February 24, 2004 8:16 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Specifying column datatypes in CTAS
Waleed !!!
You win the virtual beer award. Combining your tip for specifying NOT NULL
(but not datatype) in the CREATE TABLE side of the statement, along with
the tip of CAST for specifying string length easily (although not number
length it seems) provides me with a workable solution.
Now if only somewhere could explain Oracle's reasoning for allowing NOT
NULL in the create table, but not the rest of the datatype!
Regards,
Mark.
"Khedr, Waleed"
<Waleed.Khedr@FMR. To:
"'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
COM> cc:
Sent by: Subject: RE: Specifying
column datatypes in CTAS
oracle-l-bounce@fr
eelists.org
25/02/2004 11:47
Please respond to
oracle-l
You can specify not null
create table test_ctas111 ( c1 not null) as select * from dual ;
Waleed
-----Original Message-----
From: Mark Richard [mailto:mrichard@xxxxxxxxxxxxxxxxx]
Sent: Tuesday, February 24, 2004 7:42 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Specifying column datatypes in CTAS
Tim and Justin,
Thanks for the tip on using CAST to set the length of the column. I will
have to consider the length of time to issue the "modify... not null"
statement though - on a table of this size I am expecting about 10-20
minutes to scan and validate the table. Then again - it will take around 4
hours to rebuild the indexes on this table, so what is another 10 minutes I
guess. :-)
Regards,
Mark.
Tim Johnston
<tjohnston@quallab To:
oracle-l@xxxxxxxxxxxxx
y.com> cc:
Sent by: Subject: Re: Specifying
column datatypes in CTAS
oracle-l-bounce@fr
eelists.org
25/02/2004 10:14
Please respond to
oracle-l
Um... What about something like this?
SQL> create table tim (col1 number);
Table created.
SQL> insert into tim values (1);
1 row created.
SQL> create table tim2 as select col1, 'test' col2 from tim;
Table created.
SQL> desc tim2
Name Null? Type
----------------------------------------- --------
----------------------------
COL1 NUMBER
COL2 CHAR(4)
SQL> create table tim3 as select col1, cast('test' as varchar2(100))
col2 from tim;
Table created.
SQL> desc tim3
Name Null? Type
----------------------------------------- --------
----------------------------
COL1 NUMBER
COL2 VARCHAR2(100)
SQL> alter table tim3 modify (col2 not null);
Table altered.
SQL> desc tim3
Name Null? Type
----------------------------------------- --------
----------------------------
COL1 NUMBER
COL2 NOT NULL VARCHAR2(100)
SQL>
Mark Richard wrote:
>
>
>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
>-----------------------------------------------------------------
>
>
--
Regards,
Tim Johnston
Tel: 978-322-4226
Fax: 978-322-4100
----------------------------------------------------------------
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
-----------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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
-----------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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:
