Home » Developer & Programmer » Forms » problem with sequence generation
problem with sequence generation [message #227166] Tue, 27 March 2007 08:13 Go to next message
karthi_smarty04
Messages: 19
Registered: January 2007
Location: COIMBATORE
Junior Member
hi experts,

iam currently developing an application system using oracle forms9i.

table name: emp
table desc: emp_id number,emp_name varchar2(20),emp_no number

i have to generate the emp_id automatically.i have created a sequence for that with

create sequence emp_seq
start with 100
increment by 1


The code which i have written in Pre_insert trigger is
begin
select emp_seq.nextval into :emp.emp_id from dual
end


the sequence number is getting incremented even if the record is not inserted(record not inserted due to frm 50016 error).please help me out..
i want the sequence number to be generated only when the record id inserted.also i should display the emp_id to the user as soon as it is generated


Re: problem with sequence generation [message #227180 is a reply to message #227166] Tue, 27 March 2007 08:24 Go to previous messageGo to next message
newbie111111
Messages: 4
Registered: March 2007
Location: philippines
Junior Member

hi Dear

you could also try this

trigger when create record

begin
select nvl(max(emp_id),0)+1
into :emp.emp_id
from emp;

exception
when no_data_found then
::emp.emp_id:=1;
end;
Re: problem with sequence generation [message #227182 is a reply to message #227180] Tue, 27 March 2007 08:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
NOO
Bad advice. What if two people create a record at the same time (that is, before actually inserting and committing?)

Best advice is not to worry about the gaps.
Re: problem with sequence generation [message #227204 is a reply to message #227182] Tue, 27 March 2007 09:14 Go to previous messageGo to next message
karthi_smarty04
Messages: 19
Registered: January 2007
Location: COIMBATORE
Junior Member
thanks, but is there any other way to solve it out...Is it possible to use alter sequence statement within a trigger.
Re: problem with sequence generation [message #227213 is a reply to message #227204] Tue, 27 March 2007 09:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why?
Ask yourself why it is so immensely important to have no gaps. It is almost always highly overrated.
All the alternatives make your application non-scalable.
Re: problem with sequence generation [message #227282 is a reply to message #227213] Tue, 27 March 2007 20:09 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
How about having TWO 'sequences'!! One using an Oracle sequence which may contain holes and is used for the primary key. It is populated in the pre-insert trigger if the block is NOT a master block, or in the When-Create-Record trigger if it is a master block. Then in the 'insert' trigger at the database level use the 'nvl(max(emp_id),0)+1' method to give you the 'holeless sequence'.

You then do a requery to get the information onto the screen.

David
Re: problem with sequence generation [message #227301 is a reply to message #227282] Tue, 27 March 2007 22:59 Go to previous messageGo to next message
karthi_smarty04
Messages: 19
Registered: January 2007
Location: COIMBATORE
Junior Member
thanks a lot...let me try it out and let u know the result soon
Re: problem with sequence generation [message #227332 is a reply to message #227282] Wed, 28 March 2007 01:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
djmartin wrote on Wed, 28 March 2007 03:09
Then in the 'insert' trigger at the database level use the 'nvl(max(emp_id),0)+1' method to give you the 'holeless sequence'.

Make sure you lock your entire table for this, to prevent multiple users from working in parallel. Better yet, send all but one of your users home. Make sure that single user never deletes a row.
That is about the only way to make this work.
In the end, you WILL end up with duplicates..
Re: problem with sequence generation [message #227338 is a reply to message #227332] Wed, 28 March 2007 01:18 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Ah, Frank, I think Oracle guarantees that only one database trigger is running against a particular table at any one time.

David
Re: problem with sequence generation [message #227350 is a reply to message #227338] Wed, 28 March 2007 01:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So?
Can you tell in advance how long this transaction will last?
What marvellous other things will happen between the insert on this table and the actual commit?
Re: problem with sequence generation [message #227638 is a reply to message #227350] Wed, 28 March 2007 19:51 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
To this table, nothing! The table is locked at the database level while the databases triggers are running.

David
Re: problem with sequence generation [message #227685 is a reply to message #227638] Thu, 29 March 2007 01:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
"While the triggers are running"
This is/could be just a very small fraction of the time between the insert and the commit (the transaction can involve more than just this one table).
In addition, you encounter the Mutating Trigger problem, another very clear indication that there is something wrong with the design.
Re: problem with sequence generation [message #227855 is a reply to message #227332] Thu, 29 March 2007 18:43 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Frank wrote on Wed, 28 March 2007 16:06
djmartin wrote on Wed, 28 March 2007 03:09
Then in the 'insert' trigger at the database level use the 'nvl(max(emp_id),0)+1' method to give you the 'holeless sequence'.

Make sure you lock your entire table for this, to prevent multiple users from working in parallel. Better yet, send all but one of your users home. Make sure that single user never deletes a row.
That is about the only way to make this work.
In the end, you WILL end up with duplicates..

When I said "the 'insert' trigger at the database level" I meant the
CREATE OR REPLACE TRIGGER xxxx_TRG
   BEFORE DELETE OR INSERT OR UPDATE
   ON xxxx
type trigger, not the 'on-insert', or 'pre-insert', or 'post-insert' type trigger which lives in the a Form.

Yes you can get a mutating table message if you try to do an update of the table that you are already updating. However, for Oracle to maintain its primary key functionality the database must know that it has sole modification access to the table in question. Note that you cannot have a 'commit' in a database trigger because you are already IN a 'commit'.

@others: If I have got this wrong please post a link to the doco.

David
Re: problem with sequence generation [message #227880 is a reply to message #227855] Fri, 30 March 2007 00:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The insert trigger at the database level has NOTHING to do with a database commit. That is exactly my point. There can be hours between the firing of a trigger, be it row level or statement level, and the actual commit:

SQL> create table faq
  2  ( col1 number
  3  , col2 varchar2(10)
  4  );

Table created.

SQL> create or replace trigger faq_bis
  2  before insert on faq
  3  begin
  4    dbms_output.put_line('Trigger is firing!');
  5  end;
  6  /

Trigger created.

SQL> insert into faq values (1, 'One');
Trigger is firing!

1 row created.

See? No commit, and yet the trigger fired.

Furthermore, the proposed functionality can only be achieved if there is a rowlevel trigger involved. Either to save rowids of newly inserted rows (the standard way to circumvent the mutating table problem) or to adjust the 'sequence'column of each individual row.

The mutating table error is not caused by an update on an already updating table, but by a select on it. Therefore, your select max will cause it.

[Updated on: Fri, 30 March 2007 00:24]

Report message to a moderator

Re: problem with sequence generation [message #227899 is a reply to message #227880] Fri, 30 March 2007 02:01 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Thank you for the example. I can see that there is a problem when running in PL/SQL.

From my use of Oracle Designer you can request either a 'sequence' based or 'max' based counter.

Although in PL/SQL there can be a delay between when a record is inserted and the commit, it is my understanding that the insert is only done WHEN the 'commit' is issued. Then again a 'post' would also put the data into the table and it can be backed out leaving you with holes.

Okay, it is probable that you could write a form that produced holes in the 'max' sequence, and you could probably write code that gave rise to pirmary key errors, but if Forms is used the normal way with the 'max' in the database trigger, or the Form API is generated by Designer and is used in the recommended triggers then it will work as required.

Obviously, if the Form does the 'insert' as a DDL statement and not the intrinsic then the problem you have demonstrated would also arise.

If the programmer keeps their code 'simple' (no DDL) and uses Form builtins the way they were designed (no rewrites in the on-insert, etc), I believe that there will be no problems.

David
Re: problem with sequence generation [message #227907 is a reply to message #227899] Fri, 30 March 2007 02:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
If the programmer keeps their code 'simple' (no DDL) and uses Form builtins the way they were designed (no rewrites in the on-insert, etc), I believe that there will be no problems.


I agree.
Actually this whole thing started out because I wanted to point out that the requirement (not your solution as such) would have a negative result on the scalability of the application.
I think we agreed before that there are way too many people that go through the most impossible loop-holes to achieve nonsense requirements.
Therefor, I fear that more and more we will see that your premisse, as quoted here, will not be met... Smile

Previous Topic: how to get it........
Next Topic: master detail relation problem
Goto Forum:
  


Current Time: Sat Dec 10 15:08:00 CST 2016

Total time taken to generate the page: 0.08316 seconds