tag:blogger.com,1999:blog-8677649049588007585.post1200887063844336451..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Nuances of NULLity (2026)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-8677649049588007585.post-30864916846675903532011-02-24T11:53:22.970+00:002011-02-24T11:53:22.970+00:00If you do a
create view vnull as select ''...If you do a<br />create view vnull as select '' c from dual;<br />followed by a DESC VNULL, you can see that a '' is a CHAR(0), rather than a CHAR(1).SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-56464899540581886312011-02-23T14:24:41.338+00:002011-02-23T14:24:41.338+00:00@Iudith
Your first testcase describes the behavio...@Iudith<br /><br />Your first testcase describes the behaviour of the CHAR datatype(fixed size, padded with spaces until the specified size) and not the empty string behaviour.poelgerhttp://iadviseblog.wordpress.com/noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-14967045754784397602011-02-23T09:40:19.272+00:002011-02-23T09:40:19.272+00:00Another one difference in SQL:
SQL> select dum...Another one difference in SQL:<br /><br />SQL> select dump(''||'a') char_value, dump(null||'a') varchar2_value from dual;<br /> <br />CHAR_VALUE VARCHAR2_VALUE<br />---------------- ---------------<br />Typ=96 Len=1: 97 Typ=1 Len=1: 97_Nikotinhttps://www.blogger.com/profile/05173144223657420531noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-3198678027882583632011-02-23T09:01:20.088+00:002011-02-23T09:01:20.088+00:00Hello All,
Very interesting philosophy about NULLS...Hello All,<br />Very interesting philosophy about NULLS !<br /><br />There clearly exists an inconsistency between<br />the way that PL/SQL treats an empty CHAR(1)<br />string and SQL-s way.<br /><br />PL/SQL not only treats the CHAR(1) variable<br />having an empty string assigned to it as NOT NULL, but it also pads it with one blank,<br />in conformity with the CHAR padding semantics.<br /><br />Along the lines of Justin's sample:<br /><br />DECLARE<br /> empty_char CHAR(1) := ''; -- empty string here !<br />BEGIN<br /> IF empty_char = ' ' -- one blank here !<br /> THEN<br /> DBMS_OUTPUT.put_line('empty_char EQUALS one blank');<br /> ELSE<br /> DBMS_OUTPUT.put_line('empty_char DOES NOT EQUAL one blank');<br /> END IF;<br />END;<br />/<br />empty_char EQUALS one blank<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />But:<br /><br />BEGIN<br /> IF '' = ' ' -- compare an empty string with one blank<br /> THEN<br /> DBMS_OUTPUT.put_line('empty string EQUALS one blank');<br /> ELSE<br /> DBMS_OUTPUT.put_line('empty string DOES NOT EQUAL one blank');<br /> END IF;<br />END;<br />/<br />empty string DOES NOT EQUAL one blank<br /><br />PL/SQL procedure successfully completed.<br /><br />or even simply:<br /><br />BEGIN<br /> IF '' IS NULL THEN -- empty string here !<br /> DBMS_OUTPUT.PUT_LINE('IS NULL');<br /> ELSE<br /> DBMS_OUTPUT.PUT_LINE('IS NOT NULL');<br /> END IF;<br />END;<br />/<br />IS NULL<br /><br />PL/SQL procedure successfully completed.<br /><br />So, even inside the PL/SQL layer, the behavior is not completely consistent.<br /><br />When assigning the empty string to a CHAR(1)<br />variable, it exhibits a CHAR semantics behavior and is padded with 1 blank.<br />But when using it directly, like in the last <br />"IF" tests above, it behaves like a VARCHAR2,<br />though, as per the PL/SQL documentation, string literals are considered as CHAR datatypes<br />and obey CHAR comparison semantics (ignoring trailing blanks), even at the SQL layer.<br /><br />As by our experience, we have lots of problems<br />in our applications in places where (due to unclear historical reasons) many database columns were declared as CHAR type, instead of VARCHAR2 ... lots of trimming, padding and other "performance killers" ...<br /><br />So, my personal opinion is that, at least in the database, VARCHAR2 should ALWAYS be preferred<br />upon CHAR datatype (and the NULL issue is one of them), except maybe for some very special cases.<br /><br />Best Regards,<br />Iudith Mentzeliudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-90103234084791535972011-02-22T19:59:18.299+00:002011-02-22T19:59:18.299+00:00It seems to me that internal to PL/SQL the values ...It seems to me that internal to PL/SQL the values NULL and '' aren't identical but the only place where I have recognized the difference was in a project where I called an external dll on a windows server. I was a little bit lazy and don't passed an indicator for NULL for a parameter. When I first tested it all was ok until I passed a value of NULL to the parameter (it was a string): it crashes with an access violation (access to address $0). But when I passed an empty string to it, all works fine.<br /><br />So it looks to me that internally an assignment of an empty string to a variable initializes a pointer to an memoray area where the first byte is #0 (empty pchar string) and if you don't initialize a variable it points to address $0. But perhaps can Bryn clarify this point.<br /><br />Also astonishing is the description of the VARCHAR (without the "2") datatype - in the 8.1.7 SQL Reference (September 2000):<br /><br />"The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype.<br />Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future,<br />VARCHAR might be defined as a separate datatype used for variable-length<br />character strings compared with different comparison semantics."<br /><br />In the 11g R2 SQL Reference (October 2009):<br /><br />"Do not use the VARCHAR data type. Use the VARCHAR2 data type instead. Although the<br />VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type<br />is scheduled to be redefined as a separate data type used for variable-length character<br />strings compared with different comparison semantics."<br /><br />Perhaps this will be the type where NULL and an empty string won't be the same (perhaps in 2018?).<br /><br />The 11g reference also says:<br /><br />"Note: Oracle Database currently treats a character value with a<br />length of zero as null. However, this may not continue to be true in<br />future releases, and Oracle recommends that you do not treat empty<br />strings the same as nulls."<br /><br />But how can we treat them different if we can't see if they are different (and a comparison to '' don't works).Niels Heckerhttps://www.blogger.com/profile/01416262288792133003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-82485999614993364422011-02-22T16:58:23.202+00:002011-02-22T16:58:23.202+00:00Problem with link to Justin's page should be f...Problem with link to Justin's page should be fixed now.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-35444589281593571072011-02-22T15:20:29.602+00:002011-02-22T15:20:29.602+00:00I'm not convinced. As a programmer I always ha...I'm not convinced. As a programmer I always have to test for null values as they are different from empty space. And the lesson I'm getting from this exercise is that you cannot insert a null value into a string. Which makes sense since null cannot be compared to or add to anything in oracle.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-43948531793862222742011-02-22T14:13:19.620+00:002011-02-22T14:13:19.620+00:00Re #1: I see no problem here. You provide the ques...Re #1: I see no problem here. You provide the question as an image, so the appearance of white space should be the same on all browsers and devices. Unless you manage to OCR and copy the text into some IDE...<br /><br />Re#2: The link to Justion's profile works perfectly in IE8, but not in Firefox 3.6, where it's somehow redirected to the main page of the PL/SQL Challenge.<br /><br />Regards, UrsUrs Metzgernoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-20308158253025822692011-02-22T14:02:09.469+00:002011-02-22T14:02:09.469+00:00Oracle doesn't always treat them the same, for...Oracle doesn't always treat them the same, for example in PL/SQL you can assign a NULL to a BLOB but not a ''.Kevan Gellingnoreply@blogger.com