Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Database sequence in field (Apex, )
Database sequence in field [message #364640] Tue, 09 December 2008 07:07 Go to next message
Derek N
Messages: 80
Registered: September 2002
Member
Hi

We are currently learning to use Apex.

I built a tabular form based on a database table called Increase_values. There are just two fields in this table ie. Increase_id number(4) and Increase_value number(7,4).

I have created a database sequence, the value of which would be inserted into the field Increase_id each time a record is created in this table.

Where in Apex do I set this field to use the database sequence on inserting a record.

[Updated on: Tue, 09 December 2008 07:08]

Report message to a moderator

Re: Database sequence in field [message #364711 is a reply to message #364640] Tue, 09 December 2008 14:33 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
As I recall (in Apex 3.1.2) when you select "form on table" from the new page wizard, there are 3 ways to populate the primary key. One of them allows you to pick the sequence number generator. All Apex does is create some PL/SQL that it calls to retrieve the sequence number and use that value when inserting a new row into the table.

you can auto-populate the PK via a table level trigger, but it's advisable to let Apex handle it because it's all built in and you avoid the possibility of Apex clashing with your trigger.

create sequence X_seq;
create trigger X_trigger before insert on X for each row
begin
   if ( :new.pk is null ) then
      select X_seq.nextval into :new.pk from dual;
   end if;
end;
/

-- An insert statement can also include a seq directly
-- but doesn't help if inserts happen from more than 1 piece of code.

insert into X ( pk, ... ) values ( X_seq.nextval, .... );


BTW - I'm unsure what options a Tabluar Form offers...

[Updated on: Tue, 09 December 2008 14:34]

Report message to a moderator

Re: Database sequence in field [message #364746 is a reply to message #364640] Tue, 09 December 2008 22:50 Go to previous messageGo to next message
Derek N
Messages: 80
Registered: September 2002
Member
Thanks Andrew. Would try the trigger.
Re: Database sequence in field [message #364824 is a reply to message #364711] Wed, 10 December 2008 03:15 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
do not restrict the increase_id precision as 4 simply declare as number without precision. since people use to populate the same id sequence on many fields of different table due to laziness and in your case restricting number precision would bounce as error after some time. That time you may not be able to visualise the reasons behind it. it is also one of my experience.
yours
dr.s.raghunathan
Re: Database sequence in field [message #365205 is a reply to message #364824] Thu, 11 December 2008 01:49 Go to previous message
Derek N
Messages: 80
Registered: September 2002
Member
Thanks for that tip, much appreciated.
Previous Topic: Apache logs into one file
Next Topic: Windows user name from Oracle Apex
Goto Forum:
  


Current Time: Sat Dec 03 01:26:47 CST 2016

Total time taken to generate the page: 0.12117 seconds