Monday, October 17, 2016

Generate Sequence and Random Numbers Using SQL Query

Generate Sequence of Numbers from 1 to N using SQL Query


SELECT LEVEL AS ID 
FROM DUAL 
CONNECT BY LEVEL <= 20;

































After certain numbers generation, you will run out of your memory saying that ORA-30009: Not enough memory for CONNECT BY operation. Use with the clause and create the cartesian product as shown below.

WITH more_data AS (
  SELECT /*+ MATERIALIZE */ level AS id
  FROM   dual
  CONNECT BY level <= 100
)
SELECT rownum AS id
FROM   more_data, more_data, more_data
WHERE  rownum <= 1000000;


Generate a random number using SQL Query


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as HR@ORCLL

SQL> SELECT dbms_random.random FROM dual;
    RANDOM
----------
1624593079

SQL>