API tools faq
paste
Advertisement
ExaGridDba

Number and Oracle float storage are similar

ExaGridDba
May 15th, 2015
278
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 6.99 KB | None | 0 0
raw download clone embed print report
  1. Notes:
  2.  
  3. 1. Oracle FLOAT IS stored LIKE Oracle NUMBER, NOT IEEE 754.
  4. 2. Oracle stores two digits per byte.
  5. 3. A trailing 102 byte means negative NUMBER
  6. 4. The FIRST byte IS the exponent.
  7. 5. FLOAT AND NUMBER can store up TO 38 digits without loss. NUMBER up TO 39.
  8. 6. The encoded digits have different meanings FOR POSITIVE AND negative numbers.
  9. IF the byte VALUE IS b, THEN it means b-1 FOR POSITIVE numbers AND 101-b FOR negative numbers.
  10. FOR example byte 92 means 91 FOR POSITIVE numbers AND -9 FOR negative numbers.
  11.  
  12. [oracle@stormking cdb12102 SQL]$ sqlplus /nolog @ NUMBER.vs.FLOAT.SQL
  13.  
  14. SQL*Plus: RELEASE 12.1.0.2.0 Production ON Fri May 15 22:44:25 2015
  15.  
  16. Copyright (c) 1982, 2014, Oracle.  ALL rights reserved.
  17.  
  18. Connected.
  19. SQL> COLUMN n format 9.99999999999999999999999999999999999999999eeee
  20. SQL> COLUMN f format 9.99999999999999999999999999999999999999999eeee
  21. SQL> COLUMN d format a170
  22. SQL> COLUMN eq format a9
  23. SQL>
  24. SQL> DROP TABLE nf;
  25.  
  26. TABLE dropped.
  27.  
  28. SQL> CREATE TABLE nf ( n NUMBER, f FLOAT);
  29.  
  30. TABLE created.
  31.  
  32. SQL>
  33. SQL> INSERT INTO nf ( n , f ) VALUES ( NULL, NULL );
  34.  
  35. 1 ROW created.
  36.  
  37. SQL> INSERT INTO nf ( n , f ) VALUES ( 0, 0 );
  38.  
  39. 1 ROW created.
  40.  
  41. SQL> INSERT INTO nf ( n , f ) VALUES ( .91, .91 );
  42.  
  43. 1 ROW created.
  44.  
  45. SQL> INSERT INTO nf ( n , f ) VALUES ( 91, 91 );
  46.  
  47. 1 ROW created.
  48.  
  49. SQL> INSERT INTO nf ( n , f ) VALUES ( 9100, 9100 );
  50.  
  51. 1 ROW created.
  52.  
  53. SQL> INSERT INTO nf ( n , f ) VALUES ( -9, -9 );
  54.  
  55. 1 ROW created.
  56.  
  57. SQL> INSERT INTO nf ( n , f ) VALUES ( -900, -900 );
  58.  
  59. 1 ROW created.
  60.  
  61. SQL> INSERT INTO nf ( n , f ) VALUES ( -.09, -.09 );
  62.  
  63. 1 ROW created.
  64.  
  65. SQL> INSERT INTO nf ( n , f ) VALUES ( -.10, -.10 );
  66.  
  67. 1 ROW created.
  68.  
  69. SQL>
  70. SQL> define digits=1.234567890123456789012345678901234567891
  71. SQL> INSERT INTO nf ( n , f ) VALUES ( &&digits.e0, &&digits.e0 );
  72. old   1: INSERT INTO nf ( n , f ) VALUES ( &&digits.e0, &&digits.e0 )
  73. NEW   1: INSERT INTO nf ( n , f ) VALUES ( 1.234567890123456789012345678901234567891e0, 1.234567890123456789012345678901234567891e0 )
  74.  
  75. 1 ROW created.
  76.  
  77. SQL> define digits=.12345678901234567890123456789012345678
  78. SQL> INSERT INTO nf ( n , f ) VALUES ( &&digits.e0, &&digits.e0 );
  79. old   1: INSERT INTO nf ( n , f ) VALUES ( &&digits.e0, &&digits.e0 )
  80. NEW   1: INSERT INTO nf ( n , f ) VALUES ( .12345678901234567890123456789012345678e0, .12345678901234567890123456789012345678e0 )
  81.  
  82. 1 ROW created.
  83.  
  84. SQL> INSERT INTO nf ( n , f ) VALUES ( &&digits.e2, &&digits.e2 );
  85. old   1: INSERT INTO nf ( n , f ) VALUES ( &&digits.e2, &&digits.e2 )
  86. NEW   1: INSERT INTO nf ( n , f ) VALUES ( .12345678901234567890123456789012345678e2, .12345678901234567890123456789012345678e2 )
  87.  
  88. 1 ROW created.
  89.  
  90. SQL> INSERT INTO nf ( n , f ) VALUES ( &&digits.e-2, &&digits.e-2 );
  91. old   1: INSERT INTO nf ( n , f ) VALUES ( &&digits.e-2, &&digits.e-2 )
  92. NEW   1: INSERT INTO nf ( n , f ) VALUES ( .12345678901234567890123456789012345678e-2, .12345678901234567890123456789012345678e-2 )
  93.  
  94. 1 ROW created.
  95.  
  96. SQL> INSERT INTO nf ( n , f ) VALUES ( &&digits.e126, &&digits.e126 );
  97. old   1: INSERT INTO nf ( n , f ) VALUES ( &&digits.e126, &&digits.e126 )
  98. NEW   1: INSERT INTO nf ( n , f ) VALUES ( .12345678901234567890123456789012345678e126, .12345678901234567890123456789012345678e126 )
  99.  
  100. 1 ROW created.
  101.  
  102. SQL> INSERT INTO nf ( n , f ) VALUES ( &&digits.e-128, &&digits.e-128 );
  103. old   1: INSERT INTO nf ( n , f ) VALUES ( &&digits.e-128, &&digits.e-128 )
  104. NEW   1: INSERT INTO nf ( n , f ) VALUES ( .12345678901234567890123456789012345678e-128, .12345678901234567890123456789012345678e-128 )
  105.  
  106. 1 ROW created.
  107.  
  108. SQL>
  109. SQL> SELECT CASE n - f WHEN 0 THEN 'equal' ELSE 'not equal' END eq, n, f FROM nf;
  110.  
  111. EQ                                                        N                                                 F
  112. --------- ------------------------------------------------- -------------------------------------------------
  113. NOT equal
  114. equal        .00000000000000000000000000000000000000000E+00    .00000000000000000000000000000000000000000E+00
  115. equal       9.10000000000000000000000000000000000000000E-01   9.10000000000000000000000000000000000000000E-01
  116. equal       9.10000000000000000000000000000000000000000E+01   9.10000000000000000000000000000000000000000E+01
  117. equal       9.10000000000000000000000000000000000000000E+03   9.10000000000000000000000000000000000000000E+03
  118. equal      -9.00000000000000000000000000000000000000000E+00  -9.00000000000000000000000000000000000000000E+00
  119. equal      -9.00000000000000000000000000000000000000000E+02  -9.00000000000000000000000000000000000000000E+02
  120. equal      -9.00000000000000000000000000000000000000000E-02  -9.00000000000000000000000000000000000000000E-02
  121. equal      -1.00000000000000000000000000000000000000000E-01  -1.00000000000000000000000000000000000000000E-01
  122. NOT equal   1.23456789012345678901234567890123456789000E+00   1.23456789012345678901234567890123456790000E+00
  123. equal       1.23456789012345678901234567890123456780000E-01   1.23456789012345678901234567890123456780000E-01
  124. equal       1.23456789012345678901234567890123456780000E+01   1.23456789012345678901234567890123456780000E+01
  125. equal       1.23456789012345678901234567890123456780000E-03   1.23456789012345678901234567890123456780000E-03
  126. equal      1.23456789012345678901234567890123456780000E+125  1.23456789012345678901234567890123456780000E+125
  127. equal      1.23456789012345678901234567890123456780000E-129  1.23456789012345678901234567890123456780000E-129
  128.  
  129. 15 ROWS selected.
  130.  
  131. SQL>
  132. SQL> SELECT 'n=' || DUMP(n) || CHR(10) || 'f=' || DUMP(f) d FROM nf;
  133.  
  134. D
  135. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  136. n=NULL
  137. f=NULL
  138.  
  139. n=Typ=2 Len=1: 128
  140. f=Typ=2 Len=1: 128
  141.  
  142. n=Typ=2 Len=2: 192,92
  143. f=Typ=2 Len=2: 192,92
  144.  
  145. n=Typ=2 Len=2: 193,92
  146. f=Typ=2 Len=2: 193,92
  147.  
  148. n=Typ=2 Len=2: 194,92
  149. f=Typ=2 Len=2: 194,92
  150.  
  151. n=Typ=2 Len=3: 62,92,102
  152. f=Typ=2 Len=3: 62,92,102
  153.  
  154. n=Typ=2 Len=3: 61,92,102
  155. f=Typ=2 Len=3: 61,92,102
  156.  
  157. n=Typ=2 Len=3: 63,92,102
  158. f=Typ=2 Len=3: 63,92,102
  159.  
  160. n=Typ=2 Len=3: 63,91,102
  161. f=Typ=2 Len=3: 63,91,102
  162.  
  163. n=Typ=2 Len=21: 193,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90
  164. f=Typ=2 Len=21: 193,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90,2,24,46,68,91
  165.  
  166. n=Typ=2 Len=20: 192,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  167. f=Typ=2 Len=20: 192,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  168.  
  169. n=Typ=2 Len=20: 193,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  170. f=Typ=2 Len=20: 193,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  171.  
  172. n=Typ=2 Len=20: 191,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  173. f=Typ=2 Len=20: 191,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  174.  
  175. n=Typ=2 Len=20: 255,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  176. f=Typ=2 Len=20: 255,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  177.  
  178. n=Typ=2 Len=20: 128,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  179. f=Typ=2 Len=20: 128,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79
  180.  
  181.  
  182. 15 ROWS selected.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!