MAJOR BUG with View SQL in Edit Table and Export User Objects

[Felix]

Originally I had my CREATE TABLE with in-line named not null constraint. I called this Method 1n.

Method 1n
create table TEST_CONS
(
col1 VARCHAR2(20) constraint con_test_col1 not null
);

When I click on View SQL in Edit Table it converts it to Method 2. The same thing happens with Export User Objects.

-- Create table
create table TEST_CONS
(
col1 VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Create/Recreate check constraints
alter table TEST_CONS
add constraint CON_TEST_COL1
check ("COL1" IS NOT NULL);

If my CREATE TABLE with in-line not null does not have a named constraint Method 1u

create table TEST_CONS
(
col1 VARCHAR2(20) not null
);

The generated SQL from View SQL leaves it as in-line.

-- Create table
create table TEST_CONS
(
col1 VARCHAR2(20) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;

Method 2 creates a problem with Oracle. The describe and the all_tab_columns/user_tab_columns views do not show the nullable value correctly. The all_constraints/user_constraints does show in the search_condition column "COL1" IS NOT NULL.

Another problem I noticed from my test is when inserting into an required column, if you use Method 1, you get ORA-01400: cannot insert NULL into ("FELIX"."TEST_CONS"."COL1") and with Method 2, you get ORA-02290: check constraint (FELIX.CON_TEST_COL1) violated. The correct error should be ORA-01400 not ORA-02290.

Here is my test script:
spool test_cons.txt
set echo on
set feedback on
set linesize 500
set trimspool on
column table_name format a30
column table_name heading 'Table Name'

column column_name format a30
column column_name heading 'Column Name'

column data_type format a9
column data_type heading 'Data|Type'

column data_length format 9,999
column data_length heading 'Data|Length'

column data_precision format 9,999
column data_precision heading 'Precision'

column data_scale format 9,999
column data_scale heading 'Precision'

column nullable format a4
column nullable heading 'Null'

drop table test_cons purge;

create table test_cons (
col1 varchar2(32) default sys_guid(),
col2 varchar2(20) not null,
col3 varchar2(30),
col4 varchar2(40),
col5 varchar2(50),
col6 varchar2(60) constraint con_test_col6 not null
)
tablespace cv_misc
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

alter table test_cons
add constraint con_test_guid primary key (col1)
using index
tablespace cv_misc_ndx
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

alter table test_cons
add constraint con_test_col4
check (col4 IS NOT NULL);

alter table test_cons
add constraint con_test_col5
check ("COL5" IS NOT NULL);

set linesize 80
describe test_cons
set linesize 500

select table_name, column_name, data_type, data_length,
data_precision, data_scale, nullable from user_tab_columns
where table_name = 'TEST_CONS';
order by column_name;

select t.constraint_name, t.constraint_type, t.generated, t.search_condition
from user_constraints t where t.table_name = 'TEST_CONS';

select * from user_cons_columns t where t.table_name = 'TEST_CONS';

insert into test_cons (col1, col2, col3, col4, col5, col6) values ('1','2','3','4','5','6');

insert into test_cons (col1, col2, col3, col4, col5, col6) values ('1','2','3',null,'5','6');

insert into test_cons (col1, col2, col3, col4, col5, col6) values ('1','2','3','4',null,'6');

insert into test_cons (col1, col2, col3, col4, col5, col6) values ('1','2','3','4','5',null);

rollback;

alter table test_cons modify (col5 not null);

set linesize 80
describe test_cons
set linesize 500

select table_name, column_name, data_type, data_length,
data_precision, data_scale, nullable from user_tab_columns
where table_name = 'TEST_CONS';
order by column_name;

select t.constraint_name, t.constraint_type, t.generated, t.search_condition
from user_constraints t where t.table_name = 'TEST_CONS';

select * from user_cons_columns t where t.table_name = 'TEST_CONS';

insert into test_cons (col1, col2, col3, col4, col5, col6) values ('1','2','3','4','5','6');

insert into test_cons (col1, col2, col3, col4, col5, col6) values ('1','2','3',null,'5','6');

insert into test_cons (col1, col2, col3, col4, col5, col6) values ('1','2','3','4',null,'6');

insert into test_cons (col1, col2, col3, col4, col5, col6) values ('1','2','3','4','5',null);

rollback;

drop table test_cons purge;

spool off

 

[Marco Kalter]

We'll check it out.