Results 1 to 5 of 5

Thread: sequence

  1. #1
    Join Date
    Feb 2002
    Posts
    18

    Unanswered: sequence

    hi,

    create or replace procedure testproc
    is
    insert into testtab values(test_seq.nextval(),'abcd');
    end;
    /
    above is the code sample.This procedure is giving me a compilation error saying sequnce couldnot be referenced here.But in a normal insert the statment is working fine.
    i want to auto generate the primary key firing the insert from the stored procedure.
    How could i do it.
    PLS advice.
    Thanx
    kaushik

  2. #2
    Join Date
    Jul 2003
    Location
    Ukraine
    Posts
    1

    Re: sequence

    Try to use 'execute immediate' statement. This statement allows to execute DML/DDL queris in your procedures/functions.

    Vitaliy Vorontsov

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    insert into testtab (test_id) values(test_seq.nextval(),'abcd');

    you are missing the table's col ..


    '

  4. #4
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    I would use a trigger, something like this

    Code:
    create or replace trigger trg_testtab_bri
    before insert on testtab
    for each row
    when (new.testtab_id is null)
    begin
    select test_seq.nextval into :new.testtab_id from dual;
    end;
    Then in your procedure you don't need to reference the sequence at all. The insert would just be:

    insert into testtab (other_col) values('abcd');

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: sequence

    Originally posted by kaushik.mondal
    hi,

    create or replace procedure testproc
    is
    insert into testtab values(test_seq.nextval(),'abcd');
    end;
    /
    above is the code sample.This procedure is giving me a compilation error saying sequnce couldnot be referenced here.But in a normal insert the statment is working fine.
    i want to auto generate the primary key firing the insert from the stored procedure.
    How could i do it.
    PLS advice.
    Thanx
    kaushik
    I don't know why you have this problem, although your procedure as shown is invalid (no BEGIN). It works for me:

    SQL> create table testtab(id int, name varchar2(10) );

    Table created.

    SQL> create sequence test_seq;

    Sequence created.

    SQL> create or replace procedure testproc
    2 is
    3 begin
    4 insert into testtab values(test_seq.nextval(),'abcd');
    5 end;
    6 /

    Procedure created.

    SQL> exec testproc

    PL/SQL procedure successfully completed.

    SQL> select * from testtab;

    ID NAME
    ---------- ----------
    1 abcd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •