I had a developer ask me an interesting question recently. He was working on a problem where numeric values were stored in a table but when he queried that table in PL/SQL Developer, it would show trailing zeroes after the last digit. He wondered if this was contributing to the problem he was trying to debug. The developer needed to know if Oracle was storing those trailing zeroes.
My response was that Oracle does not store trailing zeroes. Oracle only stores the exponent and mantissa of the number. Oracle does not right-pad the numeric value with zeroes. The developer now knew that his problem was not with the data in the database but rather with something his development platform was doing.
But was my statement true? Many times I’ve made a statement about how Oracle works internally but then had to go back and validate my statement or prove that it was false which inevitably leads to the correct statement.
To test out my statement, I created a simple table and inserted data into it.
SQL> create table test_tab (val number(38,5));
Table created.
SQL> insert into test_tab values (25);
1 row created.
SQL> insert into test_tab values (25.0);
1 row created.
SQL> insert into test_tab values (25.2);
1 row created.
SQL> insert into test_tab values (25.20);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab;
VAL
----------
25
25
25.2
25.2
In SQL*Plus, we do not see any trailing zeroes even though I explicitly added them in. The values 25 and 25.0 as well as 25.2 and 25.20 all look the same. But maybe this is just how SQL*Plus is displaying the values. So let’s dump the data block to see how exactly Oracle is storing these values.
SQL> select file_id,block_id,blocks 2 from dba_extents where segment_name='TEST_TAB'; FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- 6 128 8 SQL> alter system dump datafile 6 block min 128 block max 135; System altered.
I had to determine the file and block number for my segment I created. I then issued the command to dump the contents of the data blocks to a trace file. When you look in the trace file, search for the keyword “block_row_dump” and you can see the contents of these rows in dump below:
block_row_dump: tab 0, row 0, @0x1f92 tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 2] c1 1a tab 0, row 1, @0x1f8c tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 2] c1 1a tab 0, row 2, @0x1f85 tl: 7 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 3] c1 1a 15 tab 0, row 3, @0x1f7e tl: 7 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 3] c1 1a 15 end_of_block_dump
We can see from the block dump the first value is 2 bytes long and consists of the hex characters “C1 1A”. The second row has the same exact values! This is important because it verifies my initial claim that Oracle is not storing any extra zeroes for the second row in the table. If there were an extra zero, the length would not be 2 bytes. For the third and fourth row, we can see the hex values are identical, “C1 1A 15”.
We understand that women deserve vibrant sex lives as much as men do, and it is the one creating levitra online uk the discharge of chemical substances that coils around the large arteries of the brain. So, it is important to either have safe sex buy levitra online http://downtownsault.org/casual-lifestyles/ or no sex relationship. This adaptogen is used as cialis 60mg a medicine plant. This fact has been revealed by the cialis sample http://downtownsault.org/wp-content/uploads/2015/07/Senior-Day-Poster-2016.pdf talented and expert herbal researchers who have also been affected by diabetes, high cholesterol level and pre existing eye disorders.
But let’s be sure that these hex values correspond to our data. To do that, we’ll use the DBMS_STATS.CONVERT_RAW_VALUE procedure.
SQL> set serveroutput on
SQL> declare
2 n number;
3 begin
4 dbms_stats.convert_raw_value('C11A',n);
5 dbms_output.put_line(n);
6 end;
7 /
25
PL/SQL procedure successfully completed.
SQL> declare
2 n number;
3 begin
4 dbms_stats.convert_raw_value('C11A15',n);
5 dbms_output.put_line(n);
6 end;
7 /
25.2
PL/SQL procedure successfully completed.
So the hex values “C1 1A” are the internal (raw) representation of ’25’ and “C1 1A 15” is 25.2 as we would have expected.
The moral of this story is that sometimes when you think you know how Oracle is working internally, you may still have to devise a test case to validate your statements.
