Hi,
Oracle database version: 12c
Issue:
Altered a table and created a column which is
1. NUMBER(1)
2. DEFAULT 0
3. NOT NULL
4. Constraint only 0 or 1 allowed
Gathered stats on the altered table
Altered a force edition-able view with read only to include the above created column.
When I describe it, I see NUMBER as the data type but not NUMBER(1)
Tried in sqldeveloper IDE as well as sqlplus
Noted: all_tab_cols, dba_tab_cols have the precision when queried for the table altered
Table altered has about 1500 records
Am I missing something?
Due to company policies, I cannot put the queries here but some sample sql for perusal that may help:
create table test1(a varchar2(100));
insert into test1 values('first');
insert into test1 values('second');
select * from test1;
create or replace force editionable view v_test1
as
select a
from
test1;
select * from v_test1;
alter table test1
add b number(1) default 0 not null
constraint chk
check (b in (0,1));
create or replace force editionable view v_test1
as
select a,b
from
test1;
desc v_test1
a VARCHAR2(100)
b NUMBER <--- shouldn't that be NUMBER(1)
Just wondering if anyone has seen this behavior in Oracle 12c.
Note: v_test1 has joins to two tables and a view. Can that result in precision of view column not showing up?
Any input will be appreciated.
Thanks!
Message was edited by: abyss Typo corrected a VARCHAR2(100) not NUMBER(1)
I could simulate the issue
CREATE TABLE test1
(
a NUMBER
);
insert into test1
select level
from dual
connect by level <= 100000;
CREATE TABLE test2
(
a NUMBER
);
insert into test2
select level
from dual
connect by level <= 100000;
alter table
test2
add b NUMBER(1,0) default 0
constraint test2_b_ck check(b IN (0, 1));
CREATE OR REPLACE FORCE EDITIONABLE VIEW v_tests AS
SELECT
t1.a,
t2.b
FROM
test1 t1
LEFT OUTER JOIN test2 t2 ON t1.a = t2.a
WITH READ ONLY;
select * from v_tests order by a desc;
desc v_tests
CREATE OR REPLACE FORCE EDITIONABLE VIEW v_tests2 AS
SELECT
t1.a,
t2.b
FROM
test1 t1
JOIN test2 t2 ON t1.a = t2.a
WITH READ ONLY;
desc v_tests2
I see this when I described the views
Why we see NUMBER(1) in one view which has inner join but not where we have outer join?