3 Replies Latest reply on Sep 14, 2018 3:22 PM by rp0428

    not null column populated with a null

    3778616

      We have a table with column name 'Name' that has been set to NOT NULL.

       

      However, when we recently had an issue due to NULL values in this table.

       

      We have checked the script that populates the table and test various scenarios by trying to insert a null value manually and this fails ( as would be expected)

       

      1. Check table does not accept null values:

       

      DESC LRF_PRT_REQUESTOR

      Name Null Type

      ----------------- -------- -----------------

      PRT_REQSTR_ID NOT NULL NUMBER(9)        

      FACILITY_ID NOT NULL NUMBER(9)        

      NAME NOT NULL VARCHAR2(20 CHAR)

      PRT_REQSTR_DESC   NOT NULL VARCHAR2(40 CHAR)

      PRIORITY NOT NULL NUMBER(9)        

      CREATED_DTTM NOT NULL TIMESTAMP(6)     

      LAST_UPDATED_DTTM TIMESTAMP(6)     

       

      1.   Try to insert a null  quoted value

       

      Error starting at line : 5 in command -

      INSERT INTO LRF_PRT_REQUESTOR

      SELECT  LRF_PRT_REQSTR_ID_SEQ.NEXTVAL, -- PRT_REQSTR_ID

      1, -- FACILITY_ID

      '', -- NAME

      '', -- PRT_REQST_DESC

      1, -- PRIORITY

      SYSDATE, SYSDATE

          FROM DUAL

      Error report -

      SQL Error: ORA-01400: cannot insert NULL into ("WM14DEV1A"."LRF_PRT_REQUESTOR"."NAME")

      1. 00000 - "cannot insert NULL into (%s)"

      *Cause:   

      *Action:

       

       

       

      1. Try to insert specific null into table .

      Error starting at line : 5 in command -

      INSERT INTO LRF_PRT_REQUESTOR

      SELECT  LRF_PRT_REQSTR_ID_SEQ.NEXTVAL, -- PRT_REQSTR_ID

      1, -- FACILITY_ID

      null, -- NAME

      null, -- PRT_REQST_DESC

      1, -- PRIORITY

      SYSDATE, SYSDATE

          FROM DUAL

      Error report -

      SQL Error: ORA-01400: cannot insert NULL into ("WM14DEV1A"."LRF_PRT_REQUESTOR"."NAME")

      1. 00000 - "cannot insert NULL into (%s)"

      *Cause:   

      *Action:

       

      1. Insert V_name variable with null values  ( as per script).

      Error starting at line : 16 in command -

      declare

      v_name varchar2(10);

       

      begin

      INSERT INTO LRF_PRT_REQUESTOR

      SELECT  LRF_PRT_REQSTR_ID_SEQ.NEXTVAL, -- PRT_REQSTR_ID

      1, -- FACILITY_ID

      v_name, -- NAME

      v_name, -- PRT_REQST_DESC

      1, -- PRIORITY

      SYSDATE, SYSDATE

          FROM DUAL;  

      end;

      Error report -

      ORA-01400: cannot insert NULL into ("WM14DEV1A"."LRF_PRT_REQUESTOR"."NAME")

      ORA-06512: at line 5

      1. 00000 -  "cannot insert NULL into (%s)"

      *Cause:   

      *Action:

       

       

       

      The only anomaly we see if when we write a select statement in SQL Developer the word 'name' is highlighted in blue to suggest that it is a reserved word.

      Is name a reserved word in sql developer and if so, is it referring to a function that exists possibly?

       

      We are struggling to determine how NULL values are being inserted.

        • 1. Re: not null column populated with a null
          John_K

          "Name" is not a reserved word. https://docs.oracle.com/database/121/SQLRF/ap_keywd001.htm#SQLRF55621

           

          As for your "null in a not null column" problem, that can happen when a constraint is added to a table already containing null values, with the novalidate option specified.

           

          SQL> set echo on
          SQL> set feedback on
          SQL> @a
          SQL> create table a(name varchar2(10));
          
          
          Table created.
          
          
          SQL>
          SQL> insert into a values ('Hello');
          
          
          1 row created.
          
          
          SQL> insert into a values (null);
          
          
          1 row created.
          
          
          SQL>
          SQL> alter table a modify (name not null novalidate);
          
          
          Table altered.
          
          
          SQL>
          SQL> select * from a;
          
          
          NAME
          ----------
          Hello
          
          
          
          
          2 rows selected.
          
          
          SQL>
          SQL> insert into a values (null);
          insert into a values (null)
                                *
          ERROR at line 1:
          ORA-01400: cannot insert NULL into ("APPS"."A"."NAME")
          
          
          
          
          SQL>
          
          • 2. Re: not null column populated with a null
            JuanM

            3778616 wrote:

             

            We have a table with column name 'Name' that has been set to NOT NULL.

             

            However, when we recently had an issue due to NULL values in this table.

             

            We have checked the script that populates the table and test various scenarios by trying to insert a null value manually and this fails ( as would be expected)

             

            1. Check table does not accept null values:

             

            DESC LRF_PRT_REQUESTOR

            Name Null Type

            ----------------- -------- -----------------

            PRT_REQSTR_ID NOT NULL NUMBER(9)

            FACILITY_ID NOT NULL NUMBER(9)

            NAME NOT NULL VARCHAR2(20 CHAR)

            PRT_REQSTR_DESC NOT NULL VARCHAR2(40 CHAR)

            PRIORITY NOT NULL NUMBER(9)

            CREATED_DTTM NOT NULL TIMESTAMP(6)

            LAST_UPDATED_DTTM TIMESTAMP(6)

             

            1. Try to insert a null quoted value

             

            Error starting at line : 5 in command -

            INSERT INTO LRF_PRT_REQUESTOR

            SELECT LRF_PRT_REQSTR_ID_SEQ.NEXTVAL, -- PRT_REQSTR_ID

            1, -- FACILITY_ID

            '', -- NAME

            '', -- PRT_REQST_DESC

            1, -- PRIORITY

            SYSDATE, SYSDATE

            FROM DUAL

            Error report -

            SQL Error: ORA-01400: cannot insert NULL into ("WM14DEV1A"."LRF_PRT_REQUESTOR"."NAME")

            1. 00000 - "cannot insert NULL into (%s)"

            *Cause:

            *Action:

             

             

             

            1. Try to insert specific null into table .

            Error starting at line : 5 in command -

            INSERT INTO LRF_PRT_REQUESTOR

            SELECT LRF_PRT_REQSTR_ID_SEQ.NEXTVAL, -- PRT_REQSTR_ID

            1, -- FACILITY_ID

            null, -- NAME

            null, -- PRT_REQST_DESC

            1, -- PRIORITY

            SYSDATE, SYSDATE

            FROM DUAL

            Error report -

            SQL Error: ORA-01400: cannot insert NULL into ("WM14DEV1A"."LRF_PRT_REQUESTOR"."NAME")

            1. 00000 - "cannot insert NULL into (%s)"

            *Cause:

            *Action:

             

            1. Insert V_name variable with null values ( as per script).

            Error starting at line : 16 in command -

            declare

            v_name varchar2(10);

             

            begin

            INSERT INTO LRF_PRT_REQUESTOR

            SELECT LRF_PRT_REQSTR_ID_SEQ.NEXTVAL, -- PRT_REQSTR_ID

            1, -- FACILITY_ID

            v_name, -- NAME

            v_name, -- PRT_REQST_DESC

            1, -- PRIORITY

            SYSDATE, SYSDATE

            FROM DUAL;

            end;

            Error report -

            ORA-01400: cannot insert NULL into ("WM14DEV1A"."LRF_PRT_REQUESTOR"."NAME")

            ORA-06512: at line 5

            1. 00000 - "cannot insert NULL into (%s)"

            *Cause:

            *Action:

             

             

             

            The only anomaly we see if when we write a select statement in SQL Developer the word 'name' is highlighted in blue to suggest that it is a reserved word.

            Is name a reserved word in sql developer and if so, is it referring to a function that exists possibly?

             

            We are struggling to determine how NULL values are being inserted.

            Hello,

            How are you sure that you have NULL values in your table?

             

            Also, Oracle recomments to don't use the '' empty string to insert NULL values, use NULL instead.

            • 3. Re: not null column populated with a null

              However, when we recently had an issue due to NULL values in this table.

              Ok - but there is NOTHING in what you posted that supports that statement.

               

              You haven't shown ANY results that show the column has  a NULL value for any row.

               

              Post the DDL for the table and all indexes and constraints.

               

              Post info from the data dictionary showing the status/setting for all constraints (e.g. validate, no validate)

               

              Post results that show the table actually has a null value in that column.