NUMBER datatype: PRECISION 5 SCALE 2 : Number « SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL Data Types
3. Number

SQL>
SQL> CREATE TABLE precision (
  2     value NUMBER(38,5),
  3     scale NUMBER(10));

Table created.

SQL>
SQL> INSERT INTO precision (value, scale)
  2     VALUES (12345, 0);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (123456, 0);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (123.45, 0);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (12345, 2);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (123.45, 2);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (12.345, 2);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (1234.5, 2);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     v_integer NUMBER(5);
  3     v_scale_2 NUMBER(5,2);
  4     v_real NUMBER;
  5
  6     CURSOR scale_0_cur
  7     IS
  8        SELECT value
  9        FROM precision
 10        WHERE scale = 0;
 11
 12     CURSOR scale_2_cur
 13     IS
 14        SELECT value
 15        FROM precision
 16        WHERE scale = 2;
 17  BEGIN
 18
 19
 20     DBMS_OUTPUT.PUT_LINE('     ');
 21     DBMS_OUTPUT.PUT_LINE('PRECISION 5 SCALE 2');
 22
 23     OPEN scale_2_cur;
 24
 25     -- Loop through all records that have a scale of 2
 26     LOOP
 27     FETCH scale_2_cur INTO v_real;
 28     EXIT WHEN scale_2_cur%NOTFOUND;
 29
 30     -- Assign different values to the v_scale_2 variable
 31     --  to see how it handles it
 32     BEGIN
 33        DBMS_OUTPUT.PUT_LINE('     ');
 34        DBMS_OUTPUT.PUT_LINE('Assigned: '||v_real);
 35
 36        v_scale_2 := v_real;
 37
 38        DBMS_OUTPUT.PUT_LINE('Stored: '||v_scale_2);
 39     EXCEPTION
 40        WHEN OTHERS
 41        THEN
 42           DBMS_OUTPUT.PUT_LINE('Exception: '||sqlerrm);
 43     END;
 44     END LOOP;
 45
 46     CLOSE scale_2_cur;
 47
 48  END;
 49  /
PRECISION 5 SCALE 2
Assigned: 12345
Exception: ORA-06502: PL/SQL: numeric or value error: number precision too large
Assigned: 123.45
Stored: 123.45
Assigned: 12.345
Stored: 12.35
Assigned: 1234.5
Exception: ORA-06502: PL/SQL: numeric or value error: number precision too large

PL/SQL procedure successfully completed.

SQL>
SQL> drop table precision;

Table dropped.

SQL>