Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Create Table..As Select: Number formats

RE: RE: Create Table..As Select: Number formats

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 01 Oct 2003 08:14:31 -0800
Message-ID: <F001.005D1B1F.20031001081431@fatcity.com>


:31
>
>>>I'm trying to create a table using 'Create
>>>Table...As Select...'
>...
>
>> I don't think that there is any problem here.
>Specifying the number of
>digits is largely cosmetic - consider it as > a
>default mask. It doesn't
>affect how data is stored inside the tables AFAIK.
>>
>> Regards,
>>
>> Stephane Faroult
>> Oriole
>
>The problem is when I try to exchange the newly
>created table into the
>partitioned table - the designers (in their
>infinite wisdom) have
>specified number formats for that table.
>
>"alter table daily_total exchange partition jun_02
>with table dt_temp
>including indexes"
>
>gives me the error:
>
>*
>ERROR at line 1:
>ORA-14097: column type or size mismatch in ALTER
>TABLE EXCHANGE PARTITION
>
>Unless I get the column types to match exectly - I
>can't ask for a change
>in table design to remove the awkward formatting
>until I've at least tried
>to get the format to work in the 'Create Table...As
>Select..'
>
>Cheers
>Simon Anderson
>

Simon,

  Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ?

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Oct 01 2003 - 11:14:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US