Home » Developer & Programmer » Forms » Auto Generate for a serial Number Field (Oracle developer 6i)
Auto Generate for a serial Number Field [message #629772] Tue, 16 December 2014 00:41 Go to next message
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.
Re: Auto Generate for a serial Number Field [message #629774 is a reply to message #629772] Tue, 16 December 2014 00:48 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi,

In the PRE-INSERT on Block level
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"

Re: Auto Generate for a serial Number Field [message #629777 is a reply to message #629774] Tue, 16 December 2014 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 Go to previous messageGo to next message
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?
Re: Auto Generate for a serial Number Field [message #629789 is a reply to message #629779] Tue, 16 December 2014 03:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Auto Generate for a serial Number Field [message #629791 is a reply to message #629789] Tue, 16 December 2014 03:10 Go to previous messageGo to next message
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
Re: Auto Generate for a serial Number Field [message #629796 is a reply to message #629791] Tue, 16 December 2014 03:39 Go to previous messageGo to next message
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 "
Re: Auto Generate for a serial Number Field [message #629797 is a reply to message #629796] Tue, 16 December 2014 03:42 Go to previous messageGo to next message
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
Re: Auto Generate for a serial Number Field [message #629800 is a reply to message #629797] Tue, 16 December 2014 03:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Auto Generate for a serial Number Field [message #630202 is a reply to message #629800] Mon, 22 December 2014 23:47 Go to previous messageGo to next message
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], [], [], [], [], [], []
Re: Auto Generate for a serial Number Field [message #630203 is a reply to message #630202] Mon, 22 December 2014 23:54 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi ,first connect the Data base in the form
Re: Auto Generate for a serial Number Field [message #630204 is a reply to message #630203] Tue, 23 December 2014 00:07 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
It is connnected
Re: Auto Generate for a serial Number Field [message #630205 is a reply to message #630204] Tue, 23 December 2014 00:09 Go to previous messageGo to next message
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;

_______________________
Re: Auto Generate for a serial Number Field [message #630207 is a reply to message #630202] Tue, 23 December 2014 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Auto Generate for a serial Number Field [message #630213 is a reply to message #630207] Tue, 23 December 2014 03:31 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: Auto Generate for a serial Number Field [message #630237 is a reply to message #630213] Tue, 23 December 2014 07:07 Go to previous messageGo to next message
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!

Re: Auto Generate for a serial Number Field [message #630242 is a reply to message #630237] Tue, 23 December 2014 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Auto Generate for a serial Number Field [message #630551 is a reply to message #630205] Tue, 30 December 2014 02:50 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

Quote:
select customers_seq.nextval into v_no from Dual;


use sys.dual instead of dual, this may help you.
Re: Auto Generate for a serial Number Field [message #630552 is a reply to message #630551] Tue, 30 December 2014 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How?
Explain.

Re: Auto Generate for a serial Number Field [message #630554 is a reply to message #630552] Tue, 30 December 2014 03:00 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

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.
Re: Auto Generate for a serial Number Field [message #630558 is a reply to message #630554] Tue, 30 December 2014 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Auto Generate for a serial Number Field [message #630562 is a reply to message #630558] Tue, 30 December 2014 03:27 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

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/

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 6581 times)
Re: Auto Generate for a serial Number Field [message #630564 is a reply to message #630562] Tue, 30 December 2014 03:28 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

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 1710 times)
Re: Auto Generate for a serial Number Field [message #630568 is a reply to message #630564] Tue, 30 December 2014 04:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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/

Form runs well with DUAL as well.

Therefore, it might (but doesn't have to) be the issue in this case.
Re: Auto Generate for a serial Number Field [message #630569 is a reply to message #630568] Tue, 30 December 2014 04:29 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@lf But it gave me error on D2K6i , lets wait for OP Reply , only he can tell it works for him or not.
Re: Auto Generate for a serial Number Field [message #664486 is a reply to message #630569] Thu, 20 July 2017 01:51 Go to previous message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Thank You. Problem solved.
Previous Topic: Messages
Next Topic: Error ORA-00600
Goto Forum:
  


Current Time: Fri Mar 29 09:57:23 CDT 2024