StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>Oracle</code> is very very very old.</p> <p>Back in <code>80's</code> when it was developed (and before there were any standards) they thought is was a good idea, and given then way <code>Oracle</code> stores its values, it really was.</p> <p>Here's how <code>Oracle</code> stores data (taken from the <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm" rel="nofollow noreferrer"><strong>documentation</strong></a>):</p> <p><img src="https://docs.oracle.com/cd/B19306_01/server.102/b14220/img/cncpt043.gif" alt="alt text" title="How Oracle stores data"></p> <p>No datatype is stored within the data, only the data length and the data itself.</p> <p>If the <code>NULL</code> occurs between two columns with values, it's stored as a single byte meaning column has length <code>0</code> (actually, <code>0xFF</code>). Trailing <code>NULL</code>s are not stored at all.</p> <p>So to store the value <code>'test'</code>, <code>Oracle</code> needs to store 5 bytes: <code>04 74 65 73 74</code>.</p> <p>However, to store both an empty string and a <code>NULL</code>, <code>Oracle</code> just needs to set data length to <code>0</code>.</p> <p>Very smart if your data are to be stored on <code>20 Mb</code> hard drives that cost <code>5,000$</code> each.</p> <p>Later, when the standards appeared, it wasn't such a good idea anymore, but by that time there already were lots and lots of code relying on <code>NULL</code> and <code>''</code> being the same thing.</p> <p>Making <code>VARCHAR</code> to do such a distinction will break <strong>tons</strong> of code.</p> <p>To fix it, they renamed <code>VARCHAR</code> to <code>VARCHAR2</code> (which is not a part of any standard), stated that <code>VARCHAR2</code> will <strong>never</strong> distinguish between a <code>NULL</code> and an empty string and urged everybody to use this datatype instead.</p> <p>Now they are probably waiting for the last person who used a <code>VARCHAR</code> in <code>Oracle</code> database to die.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload