1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to insert or display serial numbers in Oracle

Discussion in 'SQL PL/SQL' started by jagadekara, Nov 25, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    The following is a test case Query to insert or display serial numbers in Oracle.

    Code (SQL):
    CREATE TABLE test(sno NUMBER);
    Here I am inserting 9 crore records.

    Code (SQL):
    DECLARE
    a NUMBER;
    BEGIN
    FOR i IN 1..90000000 loop
    INSERT INTO test3 VALUES(i);
    END loop;
    commit;
    END;
    You can also display serial numbers like below.

    1
    2
    3
    4
    5
    6

    Code (SQL):
    DECLARE
    a NUMBER;
    BEGIN
    FOR i IN 1..900000 loop
    dbms_output.put_line(i);
    END loop;
    END;
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Other Options include:
    1) Use of Oracle Sequences
    2) Use of CONNECT BY LEVEL: for example
    Code (SQL):
    SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10000
    ;
     
    Sathishkumar R likes this.
  3. Durgam sasidhar

    Durgam sasidhar Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Bangalore
    Very Good Post.