5 Replies Latest reply: Feb 20, 2013 7:11 AM by Marwim RSS

    ID plotting in oracle table

    Ekalabya
      Hi masters,

      how can i plotted sequence id in a table when someone insert a data on this table. ID start with 1.
      2nd condition is if i truncate this table then sequence id start with 1.

      Please Help.
        • 1. Re: ID plotting in oracle table
          Marwim
          plotted sequence
          Do you mean a gap free sequence?
          Not possible in a multiuser environment without prohibiting concurrent access.

          Can you give us the business case why you need it?

          Regards
          Marcus
          • 2. Re: ID plotting in oracle table
            Chanchal Wankhade
            Hi,

            Unlike in SQL database identified data type, oracle does not have any such datatype. If you want to insert data using sequence then you need to user sequencename.nextval.
            About *"If i truncate table"* . In this contest you would need to recreate the sequence because the nextval value in the sequence will not truncate.
            • 3. Re: ID plotting in oracle table
              jeneesh
              You need to write a BEFORE INSERT trigger on the table to populate the id column with the sequnce..

              To restart the sequence number, you need to manually do it once the table is truncated... Either drop and recreate or Follow this
              • 4. Re: ID plotting in oracle table
                Ekalabya
                Hi,

                business case :

                there are one table always insert some data then want on row_id column which id start with 1. if anyone truncate this table then row_id again start with 1.
                • 5. Re: ID plotting in oracle table
                  Marwim
                  always insert some data then want on row_id column which id start with 1.
                  This is not a business case. A business case would be "Regulations demand a gap free sequence for invoice numbering". Even then this is an external requirement and does not determine a gap free sequence stored in the table. The ID of the table date might have gaps (created by a sequence), but when you query it you can include ROWNUMBER to generate a gap free numbering.
                  This way you don't have to care about resetting a sequence back to 1 when someone truncates the table.

                  If you really need a gap free sequence, then you can query MAX(id) + 1. Though you will have to serialize access (one at a time) to the table by locking it, otherwise 2 transactions might calculate identical numbers.

                  Regards
                  Marcus

                  Edited by: Marwim on 20.02.2013 14:11