OraFAQ Forum: Forms » Auto Generate for a serial Number Field

Home » Developer & Programmer » Forms » Auto Generate for a serial Number Field (Oracle developer 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Auto Generate for a serial Number Field [message #629772]

Tue, 16 December 2014 00:41

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

Hello guys, I am a beginner in oracle programming. Lately, I have been searching for how to make a field filled automatically, and I want this field to be a serial number.
Do I do that when creating the table, or do I have to make a function that does that for me in the form builder?
This is how I created my table :

create table Buyer_Entery_Table
(
serial_number number(6) not null ,
good_name varchar2(300),
employee_name varchar2(400),
Quantity number(6),
value number(10),
entry_number number(6),
outer_number number(6),
today date,
department_name varchar2(300),
notes varchar2(400)
);

_______________________
As you can see, the field Serial_number, I don't want the user to modify the number, at all.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #629774 is a reply to message #629772]

Tue, 16 December 2014 00:48

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi,

In the PRE-INSERT on Block level

Code: [Select all] [Show/ hide]

declare
   v_no number;
Begin
 select xxc_no.nextval into v_no from dual;
:Block.serial_number:=v_no;
end;

and set enabled is "NO"

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #629777 is a reply to message #629774]

Tue, 16 December 2014 01:05

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Why didn't you SELECT directly into :block.serial_number? Requires less typing.

Anyway, I'd rather use database trigger. It would work in all cases (not only for inserts via your Forms application). As of a SEQUENCE, yes - I agree with mist598, I'd use it too.

[Updated on: Tue, 16 December 2014 01:05]

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #629779 is a reply to message #629777]

Tue, 16 December 2014 01:15

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

Anyway, I'd rather use database trigger

Hi Littlefoot, you mean create DataBase Trigger and use it in the Trigger?

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #629789 is a reply to message #629779]

Tue, 16 December 2014 03:07

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Yes. If you use a DB trigger you will need to set the blocks DML Return property to Yes.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #629791 is a reply to message #629789]

Tue, 16 December 2014 03:10

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

Yes. If you use a DB trigger you will need to set the blocks DML Return property to Yes.

Thank U cookiemonster.. Smile

Smile

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #629796 is a reply to message #629791]

Tue, 16 December 2014 03:39

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

Ok,
Thank you. So, I should do the following steps:
1- create pre-insert trigger Block Level
2- the code as the following:

declare
v_no number;
Begin
select SERIAL_NUMBER.nextval into v_no from Buyer_Entery_table;
:BUYER_ENTERY_TABLE.serial_number:=v_no;
end;

Note that : Buyer_entery_table is the block name.

It is giving me errors. " You must declare SERIAL_NUMBER.nextval "

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #629797 is a reply to message #629796]

Tue, 16 December 2014 03:42

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

It is giving me errors. " You must declare SERIAL_NUMBER.nextval "

Hi you have to create Sequence on SERIAL_NUMBER(in the Database)
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #629800 is a reply to message #629797]

Tue, 16 December 2014 03:55

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

And the original select was from the dual table for a reason.
You run that code (assuming the sequence exists) you'll get a too_many_rows error.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630202 is a reply to message #629800]

Mon, 22 December 2014 23:47

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

Hi guys,
I created the sequence, but it gives me this error
ORA-00600: internal error code, arguments:
[17069], [101534824], [], [], [], [], [], []

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630203 is a reply to message #630202]

Mon, 22 December 2014 23:54

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi ,first connect the Data base in the form

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630204 is a reply to message #630203]

Tue, 23 December 2014 00:07

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

It is connnected

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630205 is a reply to message #630204]

Tue, 23 December 2014 00:09

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

The block level trigger is :
PRE-Insert
_________________________________
declare
v_no number;
Begin
select customers_seq.nextval into v_no from Dual;
:Buyer_Entery_table.serial_number := v_no;
end;

_______________________

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630207 is a reply to message #630202]

Tue, 23 December 2014 00:52

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink/MOS and/or call Oracle support
Have a look at alert.log and trace files.
You can also read this article: Troubleshooting Internal Errors.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630213 is a reply to message #630207]

Tue, 23 December 2014 03:31

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Move code from a form (i.e. PRE-INSERT you used) into a stored function; then call that function from a form.

Which is, obviously, a workaround. If you created a database trigger I suggested ages ago, you'd already have it running.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630237 is a reply to message #630213]

Tue, 23 December 2014 07:07

Adeel Qadir
Messages: 48
Registered: November 2013
Location: Pakistan

Member

Simply create Pre Insert Trigger on Block Level and paste this code.

begin
select nvl(max(AID),0)+1 into :AID from ACCOUNTS;
end;

AID is your Database field name and :AID is filed name where you show auto number and ACCOUNTS is Database Table .

When user Commit the form ID automatically generated,

If you want to generate Auto ID when user open form then Paste code to
When new form Instance trigger.

That's it!

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630242 is a reply to message #630237]

Tue, 23 December 2014 08:00

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And if 2 sessions do it at the same time they will get the same number.
So, that's not it.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630551 is a reply to message #630205]

Tue, 30 December 2014 02:50

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

Quote:

select customers_seq.nextval into v_no from Dual;

use sys.dual instead of dual, this may help you.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630552 is a reply to message #630551]

Tue, 30 December 2014 02:54

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How?
Explain.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630554 is a reply to message #630552]

Tue, 30 December 2014 03:00

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

once i had same problem, i wanted to display date and time on login form.
i was using 'select column from dual'. i got same error i.e ora-00600.
then i used sys.dual after that the problem was solved.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630558 is a reply to message #630554]

Tue, 30 December 2014 03:17

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Was it the same exact version than OP?
Were the ORA-600 parameters same than OP's ones?

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630562 is a reply to message #630558]

Tue, 30 December 2014 03:27

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

no only error code and ist paramter is same but i think it should work , i am attaching screenshot of same error which i got using dual.
/forum/fa/12395/0/

/forum/fa/12395/0/

i will attach sample .fmb file in next reply as we know only 1 attachment is allowed per reply.

Attachment: ora00600.JPG
(Size: 37.96KB, Downloaded 7512 times)

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630564 is a reply to message #630562]

Tue, 30 December 2014 03:28

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

i have attached sample .fmb file , run it using form builder , you will get the same error. then add
sys.dual instead of dual on block level trigger .then run the form you will get no error.

Attachment: ORA00600.fmb
(Size: 40.00KB, Downloaded 2161 times)

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630568 is a reply to message #630564]

Tue, 30 December 2014 04:18

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

DUAL itself works just fine for me (Forms 10g connected to 10.2.0.3, running on Windows XP):

/forum/fa/12398/0/

/forum/fa/12398/0/

Form runs well with DUAL as well.

Therefore, it might (but doesn't have to) be the issue in this case.

Attachment: no_ora_00600.jpg
(Size: 141.18KB, Downloaded 7477 times)

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #630569 is a reply to message #630568]

Tue, 30 December 2014 04:29

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

@lf But it gave me error on D2K6i , lets wait for OP Reply , only he can tell it works for him or not.

Report message to a moderator

Re: Auto Generate for a serial Number Field [message #664486 is a reply to message #630569]

Thu, 20 July 2017 01:51

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

Thank You. Problem solved.

Report message to a moderator

Previous Topic:	Messages
Next Topic:	Error ORA-00600

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 03:49:39 CDT 2025