Re: CTAS and ora-1723

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Fri, 2 Jul 2004 02:59:47 +0300
Message-ID: <07cd01c45fc7$7d1b5390$a9879fd9@porgand>

Hi!
In 10g the precision and scale of number datatype get inherited too (in 9i only (var)char type fields inherit the length).

SQL> create table t as select cast(null as number(10,2)) x from dual;

Table created.

SQL> desc t;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER(10,2)

SQL> drop table t;

Table dropped.

SQL> create table t as select cast(null as varchar2(99)) x from dual;

Table created.

SQL> desc t;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  VARCHAR2(99)

SQL> select * from v$version;

BANNER

Oracle10i Enterprise Edition Release 10.1.0.1.0 - Beta PL/SQL Release 10.1.0.1.0 - Beta
CORE 10.1.0.1.0 Beta
TNS for 32-bit Windows: Version 10.1.0.1.0 - Beta NLSRTL Version 10.1.0.1.0 - Beta

Tanel.

Original Message ----- From: "Tim Gorman" <tim_at_sagelogix.com> To: <oracle-l_at_freelists.org> Sent: Friday, July 02, 2004 1:48 AM Subject: Re: CTAS and ora-1723

> But datatype is only half the problem. The error message itself has to do
> with length, precision, scale, etc...
>
>
> on 7/1/04 4:09 PM, Tanel P=F5der at tanel.poder.003_at_mail.ee wrote:
>
> >> The NULL is the problem because Oracle doesn't know what datatype, scale=
> ,
> > or
> >> precision you want the PROVIDER column to be. And, as the error message
> >> states, "zero-length columns" are not permitted...
> >>=20
> >> How about breaking it up into two statements: a CREATE TABLE followed b=
> y
> > an
> >> INSERT ... SELECT?
> >=20
> > IIRC, it is also possible to use CAST in such statement to set a datatype=
> to
> > a column.
> >=20
> > Tanel.
> >=20
> >=20
> > ----------------------------------------------------------------
> > 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
> > -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>

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 Thu Jul 01 2004 - 18:56:29 CDT