Home » Developer & Programmer » Forms » How to generate a serial number?
How to generate a serial number? [message #441435] Mon, 01 February 2010 02:41 Go to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
how can i generate a s.no in item id field. it's a primaray key field. please open this image


[3 topics merged by LF]

[Updated on: Wed, 03 February 2010 05:03] by Moderator

Report message to a moderator

Re: generate s.no triger [message #441438 is a reply to message #441435] Mon, 01 February 2010 02:56 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here are some of "sequence" related topics that have been discussed earlier. Check what has been said and apply one of the possibilities to your form. In a case nothing works, I guess you'll have to explain it a little bit more (by the way, there's no image attached to your message).
Re: generate s.no triger [message #441439 is a reply to message #441435] Mon, 01 February 2010 03:02 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
here is a image please open this image.
  • Attachment: untitled.JPG
    (Size: 158.10KB, Downloaded 192 times)
Re: generate s.no triger [message #441441 is a reply to message #441439] Mon, 01 February 2010 03:07 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, what option of the ones mentioned in topics you read by following a link in my previous message did you find the most suitable?
Re: generate s.no triger [message #441446 is a reply to message #441435] Mon, 01 February 2010 04:07 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
i am using this triger syntax

begin
select (nvl(max(id),0)+1)
into :N_SORD_detail.id
from N_SORD_detail
where :N_SORD.id = :N_SORD_DETAIL.id
exception when others then
message(sqlerrm);
end;


but when i compile this trigger showing error message
"encountered the symbol "exception" when expecting one of the following.
Re: generate s.no triger [message #441448 is a reply to message #441435] Mon, 01 February 2010 04:12 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're missing a semi-colon.
But really you should use a sequence for this.
Re: generate s.no triger [message #441463 is a reply to message #441448] Mon, 01 February 2010 04:50 Go to previous messageGo to next message
Supernova11
Messages: 12
Registered: December 2008
Location: Indonesia
Junior Member
you should create sequence for your id column.

CREATE SEQUENCE ID_SEQ
START WITH 0
INCREMENT BY 1
MINVALUE 0
NOCACHE 
NOCYCLE 
NOORDER 



after that in your trigger

CREATE OR REPLACE TRIGGER ID_VAL
BEFORE INSERT
ON TABLE_NAME 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
v_id integer;

BEGIN
select id_seq.nextval into v_id from dual;

:new.id := v_id;

EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END;

Re: generate s.no triger [message #441470 is a reply to message #441435] Mon, 01 February 2010 04:58 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
i solved a problem with out creating a squense.
i am using pre_insert triger.
trigger syntax:


begin
select (nvl(max(id),0)+1)
into :N_SORD_detail.id
from N_SORD_detail
where SO_ID = :N_SORD_DETAIL.SO_ID;
exception when others then
message(sqlerrm);
end;

i am also attach a image.
Re: generate s.no triger [message #441473 is a reply to message #441435] Mon, 01 February 2010 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
@Supernova11 - you realise the OP is talking about forms triggers don't you?

@talk4ever - the trouble with your solution is that if two different users run that form at the same time they'll get the same value for :N_SORD_detail.id and then get an ORA-00001 error. Using a sequence would avoid this, which is why everyone is telling you to use one.
Re: generate s.no triger [message #441478 is a reply to message #441473] Mon, 01 February 2010 05:19 Go to previous messageGo to next message
Supernova11
Messages: 12
Registered: December 2008
Location: Indonesia
Junior Member
Upss..
sorry my bad...
i didn't read it carefully Laughing



anyway, you also can use the sequence code on my previous reply in your form.

using max(id) is not recomended for multiuser application. it could raise unique constraint error when you save the form.
Re: generate s.no triger [message #441559 is a reply to message #441435] Mon, 01 February 2010 22:48 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
@cookiemonster i am using Pre-insert trigger. it's generate s.no when user will commit the transition. so no error will be shown.
Auto Generate [message #441592 is a reply to message #441435] Tue, 02 February 2010 01:24 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
I want to Auto generate "order_id" No with sysdate and sequence through pre-insert trigger.

for example:

020220101
020220102
020220103
020220104

if system date change

030220101
030220102
030220103
030220104

anyone can help me.
Re: generate s.no triger [message #441600 is a reply to message #441435] Tue, 02 February 2010 02:09 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
That minimizes the risk but it's still possible.
Re: Auto Generate [message #441601 is a reply to message #441592] Tue, 02 February 2010 02:11 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like a simple concatenation. What problem are you having doing it?
Re: Auto Generate [message #441604 is a reply to message #441592] Tue, 02 February 2010 02:18 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
I solved a problem
trigger Name: pre-insert
trigger Syntax:

SELECT TO_CHAR(SYSDAtE, 'DDMMRRRR') || NVL(MAX(TO_NUMBER(SUBSTR(ID, 9))), 0) + 1 INTO :N_SORD.id
FROM N_sord
WHERE SUBSTR(ID, 1, Cool = TO_CHAR(SYSDate, 'DDMMRRRR');


Re: Auto Generate [message #441606 is a reply to message #441592] Tue, 02 February 2010 02:23 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Glad to hear it. Now you should probably have have a read of the forum guidelines - especially the bit about formatting code. Using code tags makes your code easier to read and avoids the possibility of smilies appearing in it:
SELECT TO_CHAR(SYSDAtE, 'DDMMRRRR') || NVL(MAX(TO_NUMBER(SUBSTR(ID, 9))), 0) + 1 INTO :N_SORD.id
FROM N_sord
WHERE SUBSTR(ID, 1, 8) = TO_CHAR(SYSDate, 'DDMMRRRR');


So please use them from now on.
Need Trigger Syntax [message #441823 is a reply to message #441435] Wed, 03 February 2010 04:57 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
I want to auto generate invoice No. (inv_ID) user manualy punch date in date field (DATE).
for example
when user input date in date field 11-01-2010 and commit the transuction Auto generate (inv_ID) in this format (RRRRMMdd0001).

201001110001
201001110002
201001110003

if user input date in date field 15-11-2009

200911150004
200911150005
200911150006

Advance Thanks for yor cooperation

talk4ever
Re: How to generate a serial number? [message #441828 is a reply to message #441435] Wed, 03 February 2010 05:06 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
And you can't work this out from what you've done previously?
What exact problem are you having doing this?
Re: How to generate a serial number? [message #441834 is a reply to message #441828] Wed, 03 February 2010 05:35 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
i solved a problem

here is a trigger syntax

select to_number(to_char(:DATE, 'YYYYMMDD')) || Lpad (nvl(max(substr(inv_ID,9,12))o) + 1,4,'0')
into :N_pord.inv_ID
from N_pord
where where inv_ID = :N_PORD_DETAIL.PO_ID;


@cookiemonster it's another form & and another task
you can try this syntax and my last trigger syntax.
both triger are generate different result.
Re: How to generate a serial number? [message #441835 is a reply to message #441435] Wed, 03 February 2010 05:42 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes they do generate different results, but they're all variations on the same basic logic.
Previous Topic: Need sql script and form screenshot
Next Topic: query field based on detail block but on main block canvas
Goto Forum:
  


Current Time: Sat Dec 03 18:03:32 CST 2016

Total time taken to generate the page: 0.09535 seconds