Add Record and assign default ID number

[bookouri]

I want a button to Add a record and at the same time generate an ID number. (or ideally have the form come up with a new record ready for append) I dont like (dont know how to use) the autonumber field, so Id planned a select max(idnumber)+ 1 from target_table. I want to press a ADD record button, replace the blank contents of the idnumber with the new generated ID number and let the user edit the other fields and save their record.


any help would be appreciated....

 

[sem]

Using select max(idnumber)+ 1 is not a good idea for many reasons untill you're not planning to port your application from Oracle to wherever else. Use sequence instead: select somesequence.nextval into :block.item from dual in pre-insert trigger.

 

[bookouri]

thanks,

Ill have to look up sequence. I dont remember what "sequence" is in Oracle.

 

[sem]

Sequence is an oracle object generating unique numbers. The simpliest sequence may be created by

create sequence sequence_name;

(sequence_name must be replaced by sequence name you choose).

By default you'll get a sequence incremented by 1, uncycled, not cached and so on.

You may get a new (next) unique value by select sequence_name.nextval from dual. The latest value selected in your session is select sequence_name.currval from dual. You may also use it in sql statements as:

insert into to_table (field1, field2...fieldn)
select sequence_name.nextval, field2, .. fieldn from from_table.

Sequences make all blockings automatically and need much less resources than tables.

The only disadvantage (besides the possible loss of numbers) is the lack of portability.
But the efficiency is much higher.

 

[bookouri]

thanks, I created a sequenc, but I cant seem to make it work on my form.

Ive tried making the text item default :sequence.mysequenc.nextval but it doesnt work

I tried putting the sql select nextval into tempvar from dual and assigning that value... formobject.idnumber := select seq.nextval

I finally tried a button with the sql in it:

declare

tempseq varchar2;

begin

select max(idnumber) into tempseq from mylist;

:myform.idnumber := tempseq;

end;

no matter what I try, I cant seem to get the value into my text field on my form. It seems like the text item default value is MADE for this purpose but it simply will not work. When I open the form, I get an error...
:sequence.myseq.nextval as the default in my text box will give me: error record not create due to sequence number generation error, as soon as I try to open the form and the form will not open

If I want to click a button on a form and have a value placed in a text box on the form what would I have to do?

thanks for any more suggestions...

 

[sem]

Suppose your sequence name is SEQ and item name is block.item:

You may:
1. Set :sequence.SEQ.nextval as a default value for your item. The simpliest way but you will lose numbers every time navigating to a new record in this block;

2. Create pre-insert trgger on block with the following code:
select seq.nextval into :block.item from dual;

You may also create before insert row level (database) trigger on your target table and populate block.item in post-insert trigger