1 2 Previous Next 18 Replies Latest reply: May 8, 2014 10:33 AM by Mark D Powell RSS

    Not Null Field Returning Null  Version 11.2.0.1

    896733

      The memb_appeal_code field below is defined VARCHAR2(15) Not Null with a default of ' ', it is not indexed

      This First query returns the value of NULL for this field and the second query returns zero records.  Also, If I run a count where memb_appeal_code = ' ' I get the expected count.  Anyone have any idea how  can a NULL value be returned from a not null defined field?  Is there a patch that fixes this issue?  Thanks in Advance.

       

      First Query that returns NULL

      SELECT memb_appeal_code
      FROM   memb

      WHERE  memb_number = '0000548856';

       

      Second Query returns zero records

      select * FROM   memb
      WHERE  memb_appeal_code is null;
        • 1. Re: Not Null Field Returning Null  Version 11.2.0.1
          sybrand_b

          In a relational database there are no such things as 'fields'. There are only columns.

          You need to correct your terminology.

          Also in Oracle there is no difference between the empty string ''  and NULL.

          As you don't show your CREATE TABLE statement, and you seem to indicate you have the default defined as the empty string, this is your problem: you have successfully wrecked Oracle by defaulting a column to the empty string.

          Whether this is a bug is hard to tell because in Oracle NULL and '' have always been treated the same.

          If there would be any patch for this, your paid support contract will allow you to obtain this patches on My Oracle Support.

          As this is not a free support site, you can not ask for patches here.

           

          ----------

          Sybrand Bakker

          Senior Oracle DBA

          • 3. Re: Not Null Field Returning Null  Version 11.2.0.1
            896733

            The default is not an empty string it is one space and I do not need any help with my terminology because I was talking about one discrete field.  The whole column, memb_appeal_code, does not have the problem, just one field in one record does.  Also,  I am not asking for anyone to send me a patch, I am just asking if anyone has encountered this issue before, knows the cause or knows of a patch that might fix it.   

             

             

            Finally,  I hope to get a response from a knowledgeable person who likes to help people instead of someone who tries to demonstrate their supposed Job Title by criticizing someones request for help.

             

             

            Thanks

            • 4. Re: Not Null Field Returning Null  Version 11.2.0.1
              RNi

              why do you think the first query returns NULL?

              did you try

              SELECT nvl(memb_appeal_code,'unknown') ...

               

              and got 'unknown' as the result?

               

              regards

              RNi

              • 5. Re: Not Null Field Returning Null  Version 11.2.0.1
                sybrand_b

                Sorry you seem to think I am not a knowledgeable person

                I will report your insulting reply as abuse of this forum.

                 

                ---------

                Sybrand Bakker

                Senior Oracle DBA

                • 6. Re: Not Null Field Returning Null  Version 11.2.0.1
                  896733

                  A 3rd party application that sits on Oracle broke because a Null value was returned and I traced it down to this issue.  I ran the queries in my initial post in both SQL Developer and TOAD and both returned a NULL value.  I also ran several nvl  sql statements and the one suggested above, nvl(memb_appeal_code,'unknown') and the replacement value, e.g., unknown, was not returned. 

                  • 7. Re: Not Null Field Returning Null  Version 11.2.0.1
                    Brian Bontrager

                    Are you assuming a blank in the result grid of TOAD/SQL Developer means null?  My copy of SQL*Developer(3.2.20.09) is configured to display null as "(null)", and TOAD 10.1.1.8 to display "{null}".

                    As stated already, Null in Oracle is an empty string (no characters). A string consisting of a single whitespace character is by definition not empty, therefore not null. 

                    • 8. Re: Not Null Field Returning Null  Version 11.2.0.1
                      Mark D Powell

                      user896733, you should not store spaces in a varchar2 column.  Logically if all you have for a character column is a space you do not have a value so the column should be NULL.

                      - -

                      MPOWEL01> create table t1 (col1  varchar2(10));

                      Table created.

                      MPOWEL01> insert into t1 values (' ');   -- space within quotes

                      1 row created.

                      MPOWEL01> select * from t1 where col1 is null;

                      no rows selected

                      MPOWEL01> select * from t1;

                      COL1
                      ----------

                       

                      --
                      So does the qeury really return null or a space?
                      - -

                      SB has a point since memb_appeal_code is a table column.  It is not a field,  a field would be a work in storage or flat file variable not a database column.  Proper terminology is important to properly stating the problem and for understanding the response.

                      - -
                      HTH -- Mark D Powell --

                      • 9. Re: Not Null Field Returning Null  Version 11.2.0.1
                        896733

                        To answer the questions,  I know the value returned is NULL because the query results from both SQL Developer and TOAD say NULL.   Also,  the 3rd party software would not have broken if the value was anything other than NULL.  And for the recommendation of not using spaces, I agree and would not design a table that way, but the definition was done by the 3rd party software provider and I cannot change it.

                         

                        P.S.  As defined in Computer Science Manuals "Relational databases arrange data as sets of database records, also called rows.  Each records consists of several fields,  the fields of all records form the columns.  Also, Fields can have different meanings when used in other contexts, e.g., object oriented design.

                        • 10. Re: Not Null Field Returning Null  Version 11.2.0.1
                          JustinCave

                          What does

                           

                          SELECT dump(memb_appeal_code)
                            FROM   memb

                          WHERE  memb_number = '0000548856'

                           

                          return?

                           

                          Justin

                          • 11. Re: Not Null Field Returning Null  Version 11.2.0.1
                            sybrand_b

                            It seems you read incorrect 'Computer Science Manuals' 

                            A table consists of rows and columns, not of 'records' and 'fields'. This applies to 3GL flat-file systems only.

                            And please, ANY, really ANY decent text on RDBMS theory will show I am right and will show you are wrong, so you will loose this little war.

                            You need to go back to school and get your money back.

                             

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

                            Sybrand Bakker

                            Senior Oracle DBA

                            • 12. Re: Not Null Field Returning Null  Version 11.2.0.1
                              896733

                              Hi Justin,

                               

                              Your query returns the following:

                               

                              Typ=1 Len=4: 78,85,76,76

                               

                              Thanks

                              • 13. Re: Not Null Field Returning Null  Version 11.2.0.1
                                Mark Williams-Oracle

                                896733 wrote:

                                 

                                Hi Justin,

                                 

                                Your query returns the following:

                                 

                                Typ=1 Len=4: 78,85,76,76

                                 

                                Thanks

                                 

                                Which shows the column is not in fact NULL but contains the characters "NULL" (without quotes).

                                • 14. Re: Not Null Field Returning Null  Version 11.2.0.1
                                  Ric Van Dyke

                                  A simple test to show nulls for a column in SQL*Plus is to do something like this:

                                   

                                  SQL> drop table bla;

                                  SQL>

                                  SQL>

                                  SQL> create table bla (col1 varchar2(4));

                                  SQL> insert into bla values (' ');

                                  SQL> insert into bla values (NULL);

                                  SQL> insert into bla values ('');

                                  SQL>

                                  SQL> set null NULL

                                  SQL>

                                  SQL> select * from bla;

                                   

                                  COL1

                                  ----

                                   

                                  NULL

                                  NULL

                                  SQL>

                                   

                                  note the last two inserts one using the key work NULL and the other an empty string (two single quotes with no space between) are seen a NULL.  The first insert is a space, which is a valid character.  In Oracle land a NULL is the absence of a value.  A space is a value, as is a 0.  The best way to insert a null is to use the key word null.  (And the case of null is not important, NULL and null are both not a value in this context.)  Oh and don't put quotes around the word null, because then it's a value!  It would be the word null as apposed to the non-value null. Confused yet?  ;-)

                                   

                                   

                                  As to terminology: to be absolutely correct, there aren't tables; there are relations, there aren't rows; there are tuples, there aren't columns; there are attributes.... But we digress...

                                  1 2 Previous Next