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!

How to generate values in a columns automatically, while inserting other columns, wit

Discussion in 'SQL PL/SQL' started by Vicky, Jul 2, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How to generate values in a column automatically, while inserting other columns, without giving sequence_name.next_value or creating trigger?!

    for ex:

    i, I need to insert values in SNO column automatically, but i won't give sequence_name.next_value for that while inserting values for other columns.,
    ii, I should not use trigger too.,
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: How to generate values in a columns automatically, while inserting other columns,

    Hi.
    Everything depends on the task: whether the continuous sequence or random numbers (unique) is necessary...etc

    As a rule if the surrogate key isn't necessary, then to use a natural key.

    only for the purposes of study ...
    1)
    it can be used for reference tables : If only one write process in the table , then it is possible to use select max (id) of +1 from...

    2) to use the program generator: SQL expression or pl/sql-function

    3)
    if the table is refilled then it is possible to use value of the psevdno-column ROWNUM in SELECT
    insert into...
    select rownum...
    from...


    and others possible options...
     
    Vicky likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Re: How to generate values in a columns automatically, while inserting other columns,

    Hi Vicky,

    The 2 methods you've outlined are the ones used generally to populate a column automatically.
    However, in Oracle 12c, it can be done by including "...GENERATED ... AS IDENTITY ..." clause in the column definition.

    Refer to following articles for more details:

    http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php
    http://www.oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1.php
     
    Vicky likes this.
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Re: How to generate values in a columns automatically, while inserting other columns,

    Thanks for your suggestions., So, there's no other way to populated column values without using sequence or trigger in 11g or 10g.,?!

    Sequence can be used., But the only thing i want to omit is using seq_name.nextval while inserting the value., Isn't possible?!
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: How to generate values in a columns automatically, while inserting other columns,

    it is technically possible to arrive so(fif write process - one):
    1) to receive the current value of sequence...
    2) to carry out an insertion of records
    3) to reinstall values of sequence on the actual...
    ALTER SEQUENCE

    but it is better to use sequnce with cache

    Note: I will repeat: methods of the decision depend on the task
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Re: How to generate values in a columns automatically, while inserting other columns,

    In heard in mysql there's a func named Autoincrement, to auto generate values in a column while inserting values in other columns.,.

    IS der any function in Oracle like Autoincrement which is very simple to use , other than using Sequence and Trigger?!!!
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: How to generate values in a columns automatically, while inserting other columns,

    In oracle version < 12с Isn't present opportunity to create the table with an autoincremental field.

    Rajen already wrote about it and showed links with examples
     
  8. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Re: How to generate values in a columns automatically, while inserting other columns,

    Yes, Sergey, I understand.,But, what about SYS_GUID.,

    I see that it's generating 16 digits unique value.,., Is der any way to convert it and store it to a column according to our wish.,?! Like auto increment it from 1......?!
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: How to generate values in a columns automatically, while inserting other columns,

    Yes, it is possible to use sys_op_guid for generation of unique values , but not for generation of sequential values : 1 to n ...
    Code (SQL):

        SELECT
            sys_op_guid()
        FROM dual CONNECT BY  level <= 7;

    SQL>
     
    SYS_OP_GUID()
    --------------------------------
    FD3812379C267CFDE043350410ACAECE
    FD3812379C277CFDE043350410ACAECE
    FD3812379C287CFDE043350410ACAECE
    FD3812379C297CFDE043350410ACAECE
    FD3812379C2A7CFDE043350410ACAECE
    FD3812379C2B7CFDE043350410ACAECE
    FD3812379C2C7CFDE043350410ACAECE
     
    7 ROWS selected

     
     
    Vicky likes this.