4 Replies Latest reply: Sep 9, 2010 4:29 PM by stuartu RSS

    Why is null field blank in describe of version-enabled table?

    stuartu
      I have installed Workspace Manager in my 10.2.0.4 database (WM version is 10.2.0.4.3) on RHEL 5.5

      When I version enable SCOTT.EMP and SCOTT.DEPT, and do a describe on EMP or DEPT, I see the NULL? field is blank, even though I know there are not null constraints present. The below example is on the DEPT table:
      TEST: SCOTT > desc dept
       Name                                                                    Null?    Type
       ----------------------------------------------------------------------- -------- ------------------------------------------------
       DEPTNO                                                                           NUMBER(2)
       DNAME                                                                            VARCHAR2(14)
       LOC                                                                              VARCHAR2(13)
      
      TEST: SCOTT > select * from dept;
      
          DEPTNO DNAME          LOC
      ---------- -------------- -------------
              10 ACCOUNTING     NEW YORK
              20 RESEARCH       DALLAS
              30 SALES          CHICAGO
              40 OPERATIONS     BOSTON
      
      4 rows selected.
      
      TEST: SCOTT > exec dbms_wm.beginddl('DEPT');
      
      PL/SQL procedure successfully completed.
      
      TEST: SCOTT > alter table dept_lts modify ( dname  not null );
      
      Table altered.
      
      TEST: SCOTT > exec dbms_wm.commitddl('DEPT');
      
      PL/SQL procedure successfully completed.
      
      TEST: SCOTT > desc dept
       Name                                                                    Null?    Type
       ----------------------------------------------------------------------- -------- ------------------------------------------------
       DEPTNO                                                                           NUMBER(2)
       DNAME                                                                            VARCHAR2(14)
       LOC                                                                              VARCHAR2(13)
      
      TEST: SCOTT > insert into dept values (90,null,'TEST LOC');
      insert into dept values (90,null,'TEST LOC')
      *
      ERROR at line 1:
      ORA-02290: check constraint (SCOTT.SYS_C00138590) violated
      ORA-06512: at "SCOTT.OVM_INSERT_10", line 4
      ORA-04088: error during execution of trigger 'SCOTT.OVM_INSERT_10'
      
      TEST: SCOTT > desc dept_lt
       Name                                                                    Null?    Type
       ----------------------------------------------------------------------- -------- ------------------------------------------------
       DEPTNO                                                                           NUMBER(2)
       DNAME                                                                            VARCHAR2(14)
       LOC                                                                              VARCHAR2(13)
       VERSION                                                                          NUMBER(38)
       CREATETIME                                                                       TIMESTAMP(6) WITH TIME ZONE
       RETIRETIME                                                                       TIMESTAMP(6) WITH TIME ZONE
       NEXTVER                                                                          VARCHAR2(500)
       DELSTATUS                                                                        NUMBER(38)
       LTLOCK                                                                           VARCHAR2(100)
      
      TEST: SCOTT > 
      
      TEST: SCOTT > select owner, constraint_name, constraint_type, search_condition, status from all_constraints where table_name = 'DEPT_LT'
      
      OWNER                          CONSTRAINT_NAME                C SEARCH_CONDITION               STATUS
      ------------------------------ ------------------------------ - ------------------------------ --------
      SCOTT                          PK_DEPT                        P                                ENABLED
      SCOTT                          SYS_C00138586                  C "DEPTNO" IS NOT NULL           ENABLED
      SCOTT                          SYS_C00138590                  C "DNAME" IS NOT NULL            ENABLED
      
      3 rows selected.
      So I know the column has the not null check constraint.

      Is this a bug with Workspace Manager? Perhaps it is fixed in a later version of Oracle? A trivial issue really, but it just seems odd why it does not show.

      Edited by: stuartu on Sep 7, 2010 12:00 PM (added query from all_constraints)