null with CTAS
Craig, September 14, 2004 - 3:43 pm UTC
What about when using CTAS? There are times when it would be extraordinarily convenient to be able to include a null in a CTAS statement. I understand why, I mean, the db needs to know what datatype to create the column as. Is there some clever cast that will "trick" the optimizer into creating a null column with the datatype of my choosing when doing CTAS?
Thanks again for a jim-dandy site!
September 14, 2004 - 3:53 pm UTC
ops$tkyte@ORA9IR2> create table t
2 as
3 select cast( null as varchar2(10) ) a, cast( null as date ) b, cast( null as number ) c
4 from dual;
Table created.
ops$tkyte@ORA9IR2> desc t;
Name Null? Type
---------------------------------------- -------- ----------------------------
A VARCHAR2(10)
B DATE
C NUMBER
A reader, September 14, 2004 - 4:50 pm UTC
Thanks a lot..
A reader, September 15, 2004 - 12:29 am UTC
Hi Tom,
Thanks for the information. I sincerely appreciate your help.
I have a request : Can all archived weekwise Asktom pages be make available for download in a single zip file (yearwise)? That would be very helpful, if made available in the larger interest of Oracle programmer/DBA community.
Any plans of addressing Oracle OpenWorld - Mumbai scheduled for November 2004 ?
Best regards,
Rakesh.
null with CTAS
Craig, September 15, 2004 - 10:50 am UTC
Your expertise knows no bounds. So tell me, how many licks does it take to get to the tootsie roll center of a tootsie pop?
casting to gain a precision
Neil, November 23, 2004 - 7:22 am UTC
Tom,
I would like do create a number(6) column from a character source:
create table copied (col1 varchar2(6));
insert into copied (col1)
values '123456';
commit;
create table copy as
select cast( col1 as number(6) ) col1
from copied;
desc copy
Name Null? Type
---------------------- ----- --------------
COL1 NUMBER
select data_length from user_tab_columns where table_name = 'COPY';
DATA_LENGTH
-----------
22
1 row selected.
Why doesn't the precision propogate to my new table? Is there any way around this?
November 23, 2004 - 7:54 am UTC
it does in current releases (but the data length is going to be 22 regardless, it is that way for numbers)
ops$tkyte@ORA9IR2> create table copied (col1 varchar2(6));
Table created.
ops$tkyte@ORA9IR2> insert into copied (col1)
2 values ('123456');
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> create table copy as
2 select cast( col1 as number(6) ) col1
3 from copied;
Table created.
ops$tkyte@ORA9IR2> desc copy
Name Null? Type
----------------------------- -------- --------------------
COL1 <b>NUMBER(6)</b>
ops$tkyte@ORA9IR2> select data_length from user_tab_columns where table_name = 'COPY';
DATA_LENGTH
-----------
22
<b>you would really want to use CREATE TABLE to get the datatypes "precise as you want" followed by insert or insert /*+ append */ to populate them.</b>
Perfect, but doesn't seem to work in 9.2.0.1.0
Robert Piras, November 23, 2004 - 8:34 am UTC
Very useful example. Unfortunately I could not replicate in my 9.2.0.1.0 instance:
SQL> create table copied (col1 varchar2(6));
Table created.
SQL> insert into copied (col1) values ('123456');
1 row created.
SQL> commit;
Commit complete.
SQL> create table copy as
2 select cast( col1 as number(6) ) col1
3 from copied;
Table created.
SQL> desc copy
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
SQL> select COLUMN_NAME, DATA_LENGTH,DATA_PRECISION Precision, DATA_SCALE Scale
2 from dba_tab_columns where OWNER='ITM' and TABLE_NAME='COPY';
COLUMN_NAM DATA_LENGTH PRECISION SCALE
---------- ----------- ---------- ----------
COL1 22
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 rp1
XYZ
9.2.0.1.0 16-NOV-04 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
November 23, 2004 - 8:43 am UTC
in 9206, the precision came over -- but I'll re-iterate
to get the precise datatypes you want, there is but one way -- CREATE TABLE (....)
using a CTAS you are going to see many differences depending on the text of the query.
Changing datatype in CTAS
Mohan, August 07, 2009 - 10:52 am UTC
Thanks for explaining about cast and increasing the data length. Is there anyway we can change Number to Integer? I tried this.
create table tbl1(
a varchar2(30),
b varchar2(30),
c varchar2(30),
d number,
e number,
f varchar2(1));
create table tbl2 as
select a,b,d,cast(e as integer) e from tbl1;
desc tbl2;
NAME Null? Type
------------------------------- --------- -----
A VARCHAR2(30)
B VARCHAR2(30)
D NUMBER
E NUMBER
Why I could not able to create an Integer column using CTAS. I am using Oracle 10.1.0.5
Thanks
Mohan
August 07, 2009 - 2:29 pm UTC
there is no such thing as an integer, an integer is just a synonym for number.
We have numbers, binary floats and binary doubles - that is all.
Mohan, August 10, 2009 - 9:51 am UTC
Thanks a lot for the clarification.
Mohan, August 10, 2009 - 10:18 am UTC
Tom,
Thanks for your clarification.
I ran this test, just to see what would happen when using NUMBER or INTEGER. Even though INTEGER may be an alias, Oracle still handles the two data types differently.
drop table test1 purge;
create table test1
(pkid varchar2(30) not null,
test_int integer,
test_nbr number);
insert into test1 (pkid, test_int, test_nbr)
values (1, 123.4567, 987.6543);
commit;
select * from test1;
update test1
set test_nbr=test_int;
commit;
select * from test1;
Thanks
Mohan
August 13, 2009 - 8:41 am UTC
specifically, an integer is a number(38), all precision - no scale.
it is an alias. number, by itself, without a precision or scale, is a floating point number (like number(*))
integer is number(*,0)
bottom line is "there is no such thing as integer", it is just an alias to the underlying datatype that is "number"
ops$tkyte%ORA10GR2> create table test1
2 (pkid varchar2(30) not null,
3 test_int integer,
4 test_int2 number(*,0),
5 test_nbr number,
6 test_nbr2 number(*)
7 );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select column_name, data_type, data_precision, data_scale
2 from user_tab_columns
3 where table_name = 'TEST1'
4 /
COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
------------------------------ -------------- -------------- ----------
PKID VARCHAR2
TEST_INT NUMBER 0
TEST_INT2 NUMBER 0
TEST_NBR NUMBER
TEST_NBR2 NUMBER
mad skills
justin, May 23, 2012 - 1:48 pm UTC
default 0 for varchar2
reader, July 17, 2012 - 3:15 pm UTC
Hi Tom,
It is syntactically permissible to define a column as "varchar2(10) default 0" as shown in the following. Is it a good practice to define a varchar2 column to have a default NUMBER? Will the default values of column c1 and c2 in this example be stored the same way interally?
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table test (c1 varchar2(10) default '0', c2 varchar2(10) default 0);
Table created.
Thanks a lot!
July 17, 2012 - 4:41 pm UTC
it is the same as:
default to_char(0)
there is an implicit conversion going on. It would be a really bad practice. suggest you default it to '0', a string.
null as number
A reader, November 18, 2015 - 2:03 pm UTC
Thank you. I was getting this error in OWB.