Generate Random number using SQL Query

Generate Random number

Here is the script to generate the random 10 digits number using SQL. And of course, this can be modified accordingly to suit the requirements.


Select SUBSTR('0000000000'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,10000000000),0)),-10) from dual;


This Select query generates a 10 digit random number. This will be useful for using generating primary keys when using on the primary key tables.

Example of FORALL Insert statement

FORALL Insert statement.

As we all know, FORALL is much faster than the FOR LOOP for processing.It is usually used for the BULK processings records.

Here is simple example of the usage of the FORALL Insert statement.

TYPE cm_per_tbl_type IS TABLE OF per_table%ROWTYPE INDEX BY PLS_INTEGER

l_per_tbl_type cm_per_tbl_type;

IF l_per_tbl_type .count > 0
THEN

          FORALL l_cnt IN l_per_tbl_type.FIRST .. l_per_tbl_type.LAST
          INSERT INTO cmx_per_table
          values  (l_per_tbl_type(l_cnt).per_id,l_per_tbl_type(l_cnt).per_name);
END IF;

The same can be modified according to the requirements and be used.