This discussion is archived
10 Replies Latest reply: Mar 18, 2013 5:55 AM by BluShadow RSS

auto-increment  identity column through procedure in oracle 10g on windows

NehaRK Newbie
Currently Being Moderated
Hi,

I need identity primary key which should be auto increment before while inserting data into table.
for this i had use sequence and then trigger to increment it.

but now i need to increment it in Procedure, while my procedure is having code to insert data in same table which has primary key
  • 1. Re: auto-increment  identity column through procedure in oracle 10g on windows
    APC Oracle ACE
    Currently Being Moderated
    and your question is .... what?

    Cheers, APC
  • 2. Re: auto-increment  identity column through procedure in oracle 10g on windows
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    SNEHA RK wrote:
    Hi,

    I need identity primary key which should be auto increment before while inserting data into table.
    for this i had use sequence and then trigger to increment it.
    Right. Some database products have auto-increment columns, and they are really handy. Unfortunately, Oracle does not have auto-increment columns. A sequence is an auto-increment object, and it's the right way to automatically generate unique identifiers, but you need to explicity reference the sequence, either in you DML statements, or in a trigger that will automatically fire before a DML statement.
    but now i need to increment it in Procedure, while my procedure is having code to insert data in same table which has primary key
    Are you saying that you need to increment the sequence, completely aside from INSERTing into the table?
    If so, just reference sequence_name.NEXTVAL wherever you want to. In PL/SQL, you can say
    SELECT  sequence_name.NEXTVAL
    INTO    number_variable
    FROM    dual;
    This works in any version of Oracle, but starting in Oracle 11, you also have the option of referencing te sequence without using dual, or any other table.

    I hope this answers your question.
    If not, post a complete script that people can run to re-create the problem and test their ideas.
    For example:
    -- Here are the table and the seqauence that I created:
    CREATE TABLE table_x ...
    CREATE SEQUENCE ...
    
    -- Here is the BEFORE INSERT trigger I wrote:
    CREATE OR REPLACE TRIGGER ...
    
    -- The trigger works exactly how I want it to in statements like this:
    INSERT INTO table_x ...
    
    -- So there are no problems (that I know of) with anything up to this point.
    
    -- Now I want to use the same sequence to ...
    -- so that when I execute a statement like this
    ...
    -- then the next time  I add a new row to the orginal table, like this
    INSERT INTO table_x ...
    -- then the contents of table_x should be ... because ...
  • 3. Re: auto-increment  identity column through procedure in oracle 10g on windows
    EdStevens Guru
    Currently Being Moderated
    SNEHA RK wrote:
    Hi,

    I need identity primary key which should be auto increment before while inserting data into table.
    for this i had use sequence and then trigger to increment it.

    but now i need to increment it in Procedure, while my procedure is having code to insert data in same table which has primary key
    If you are saying you are now doing your inserts inside a procedure, then you don't need to do anything. The the trigger will still fire on the insert statement. Doesn't matter if that insert comes from a procedure or a sqlplus command typed at a keyboard. In fact, I prefer doing it in a trigger, so that it can't be over-ridden, ignored, or otherwise circumvented by the actual insert code.
  • 4. Re: auto-increment  identity column through procedure in oracle 10g on windows
    NehaRK Newbie
    Currently Being Moderated
    Hi Frank Kulash,

    I have created table with primary key field,to populate that table with values I have created SP , in SP I am using
    " insert into table_name ( select col1 , col2,col3... from joining tables)
    now the table in which I am inserting value is having primary key and i am not selecting that field from this statement (i.e.
    ( select col1 , col2,col3... from joining tables)) for this i had use sequence and trigger ,, but now i dont want to use sequence and trigger

    so how to achieve this??

    please reply
    Thanks in advanced
  • 5. Re: auto-increment  identity column through procedure in oracle 10g on windows
    jeneesh Guru
    Currently Being Moderated
    SNEHA RK wrote:
    Hi Frank Kulash,

    I have created table with primary key field,to populate that table with values I have created SP , in SP I am using
    " insert into table_name ( select col1 , col2,col3... from joining tables)
    now the table in which I am inserting value is having primary key and i am not selecting that field from this statement (i.e.
    ( select col1 , col2,col3... from joining tables)) for this i had use sequence and trigger ,, but now i dont want to use sequence and trigger
    Can you explain, why dont you want SEQUENCE?

    so how to achieve this??
    If your table is always populated with the above mentioned procedure, you will be able to use max()+rownum.

    But, this will fail in a multi user environement..
  • 6. Re: auto-increment  identity column through procedure in oracle 10g on windows
    NehaRK Newbie
    Currently Being Moderated
    Hi,

    If i am using sequence then i have to create trigger... as per the client requirement they dont want trigger for auto increment column
  • 7. Re: auto-increment  identity column through procedure in oracle 10g on windows
    Paul Horth Expert
    Currently Being Moderated
    SNEHA RK wrote:
    Hi,

    If i am using sequence then i have to create trigger... as per the client requirement they dont want trigger for auto increment column
    Then just do it in your stored procedure as Frank suggested.
    insert into table1 (pk_col, col1, col2,...)
    select seq.nextval, t.col1,t.col2,...
    from other_tables...
    Why is your client giving you technical requirements like "no triggers"? Surely the client gives a business specification and it is your job
    to come up with the technical solution?
  • 9. Re: auto-increment  identity column through procedure in oracle 10g on windows
    EdStevens Guru
    Currently Being Moderated
    SNEHA RK wrote:
    Hi,

    If i am using sequence then i have to create trigger... as per the client requirement they dont want trigger for auto increment column
    As Paul said, eliminating triggers as a technical option makes no sense. You can reference the sequence in your procedure, but as I mentioned in my earlier post, that will not prevent someone from

    1) writing another procedure that does NOT use the sequence
    2) modifying your procedure in a normal maintenance operation and inadvertently removing the reference (that's called a "programming bug", and yes they do happen)
    3) inserting using straight sql (not a pl/sql procedure) either in a script or at a command prompt, and not using the sequence.

    There are people who believe triggers are inherently evil and have a policy of never using them. This is a stupid policy. And this example of using a sequence to populate a PK is, to me, one of the primary situations where a triggers is exactly the proper technique for fulfilling the requirement.
  • 10. Re: auto-increment  identity column through procedure in oracle 10g on windows
    BluShadow Guru Moderator
    Currently Being Moderated
    Completely agree with Ed, triggers ARE the solution to this requirement.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points