Chapter 9. Numbers

Where would we be without numbers ? While those of us who are math-challenged might prefer a text-only view of the world, the reality is that much of the data in any database is numeric . How much inventory do we have? How much money do we owe? At what rate is our business growing? These are just some of the questions that we expect to answer using numbers from databases.

When working with numbers in PL/SQL, you need to have at least a passing familiarity with the following:

  • The numeric datatypes at your disposal. It also helps to know in what situations they are best used.

  • Conversion between numbers and their textual representations. How else do you expect to get those numbers into and out of your database?

  • PL/SQL’s rich library of built-in numeric functions. After all, you don’t want to reinvent the wheel.

Each of these topics is discussed in this chapter. We’ll begin by looking at the datatypes themselves.

Numeric Datatypes

Like the Oracle RDBMS, PL/SQL offers a variety of numeric datatypes to suit different purposes:


A true decimal datatype that is ideal for working with monetary amounts. NUMBER is also the only one of PL/SQL’s numeric types to be implemented in a completely platform-independent fashion. Anything you do with NUMBERs should work the same regardless of the underlying hardware.


Integer datatypes conforming to your hardware’s underlying, integer representation. Arithmetic is performed using your hardware’s native, machine ...

Get Oracle PL/SQL Programming, 4th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.