Oracle PL/SQL/Data Type/Binary Data Type

BINARY_FLOAT and BINARY_DOUBLE example

 
SQL> CREATE TABLE binary_test (
  2    bin_float BINARY_FLOAT,
  3    bin_double BINARY_DOUBLE
  4  );
Table created.
SQL>
SQL> INSERT INTO binary_test (
  2    bin_float, bin_double
  3  ) VALUES (
  4    39.5f, 15.7d
  5  );
1 row created.
SQL>
SQL> INSERT INTO binary_test (
  2    bin_float, bin_double
  3  ) VALUES (
  4    BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY
  5  );
1 row created.
SQL>
SQL> drop table binary_test;
Table dropped.

BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY

 
SQL> CREATE TABLE binary_test (
  2    bin_float BINARY_FLOAT,
  3    bin_double BINARY_DOUBLE
  4  );
Table created.
SQL>
SQL> INSERT INTO binary_test (
  2    bin_float, bin_double
  3  ) VALUES (
  4    39.5f, 15.7d
  5  );
1 row created.
SQL>
SQL> INSERT INTO binary_test (
  2    bin_float, bin_double
  3  ) VALUES (
  4    BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY
  5  );
1 row created.
SQL>
SQL> drop table binary_test;
Table dropped.

BINARY_INTEGER as loop counter

  
SQL>
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> DECLARE
  2    v_LoopCounter BINARY_INTEGER := 1;
  3  BEGIN
  4    LOOP
  5      INSERT INTO MyTable (num_col)
  6        VALUES (v_LoopCounter);
  7      v_LoopCounter := v_LoopCounter + 1;
  8      EXIT WHEN v_LoopCounter > 50;
  9    END LOOP;
 10  END;
 11  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
 NUM_COL CHAR_COL
-------- ------------------------------------------------------------
    1.00
    2.00
    3.00
    4.00
    5.00
    6.00
    7.00
    8.00
    9.00
   10.00
   11.00
 NUM_COL CHAR_COL
-------- ------------------------------------------------------------
   12.00
   13.00
   14.00
   15.00
   16.00
   17.00
   18.00
   19.00
   20.00
   21.00
   22.00
 NUM_COL CHAR_COL
-------- ------------------------------------------------------------
   23.00
   24.00
   25.00
   26.00
   27.00
   28.00
   29.00
   30.00
   31.00
   32.00
   33.00
 NUM_COL CHAR_COL
-------- ------------------------------------------------------------
   34.00
   35.00
   36.00
   37.00
   38.00
   39.00
   40.00
   41.00
   42.00
   43.00
   44.00
 NUM_COL CHAR_COL
-------- ------------------------------------------------------------
   45.00
   46.00
   47.00
   48.00
   49.00
   50.00
50 rows selected.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>

BINARY_INTEGER assignment

  
SQL>
SQL> DECLARE
  2    v_BinInt BINARY_INTEGER;
  3  BEGIN
  4    v_BinInt := 2147483647;
  5    v_BinInt := v_BinInt + 1 - 1;
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 5

SQL>

Use binary data type

 

SQL> --TO_BINARY_DOUBLE(x): New for Oracle Database 10g. Converts x to a BINARY_DOUBLE.
SQL> CREATE TABLE float_point_demo
  2    (dec_num    NUMBER(10,2),
  3     bin_double BINARY_DOUBLE,
  4     bin_float  BINARY_FLOAT);
Table created.
SQL>
SQL> INSERT INTO float_point_demo VALUES (1234.56,1234.56,1234.56);
1 row created.
SQL>
SQL> SELECT * FROM float_point_demo;
   DEC_NUM BIN_DOUBLE  BIN_FLOAT
---------- ---------- ----------
   1234.56 1.235E+003 1.235E+003
SQL>
SQL> SELECT DUMP(dec_num) "Decimal",
  2     DUMP(bin_double) "Double"
  3     FROM float_point_demo;
Decimal
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Double
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=2 Len=4: 194,13,35,57
Typ=101 Len=8: 192,147,74,61,112,163,215,10

SQL>
SQL>
SQL>
SQL> drop table float_point_demo;
Table dropped.
SQL>
SQL>
SQL>