NUMBER function [Miscellaneous]

Generates numbers starting at 1 for each successive row in the results of the query.

NUMBER ( * )

The following statement returns the numbered list

SELECT NUMBER( * )
FROM department
WHERE dept_id > 10

Use the NUMBER function only in a select list or a SET clause of an UPDATE statement. A syntax error is generated if you use NUMBER in any other type of statement. For example, the following statement updates each row of the seq_id column with a number 1 greater than the previous row. The number is applied in the order specified by the ORDER BY clause.

update empl
set seq_id = number(*)
order by empl_id

NUMBER can also be used to generate primary keys when using the INSERT from SELECT statement, although using IDENTITY/AUTOINCREMENT is a preferred mechanism for generating sequential primary keys.