Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie question re Forms 6.0
Process id (sequence) and date generation in an PRE-INSERT trigger on
the block in question.
For example:
BEGIN
:block1.created_on := :system.effective_date;
SELECT Nvl(Max(application_no), 0) + 1
INTO :block1.application_no
FROM source_table_name;
END;
By using the PRE-INSERT trigger, these statements will be processed
just before Forms commits the changed records to the database.
You may have to specify date format on :system.effective_date. The default format is DD-MON-YYYY HH24:MI:SS, and will only be valid when assigned to items of data type DATETIME. As I guess you've used the data type DATE, you should reformat the date returned with a date mask matching your requirements, for example:
:block1.created_on := To_Char(To_Date(:system.effective_date, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-RRRR');
(Replace 'DD-MON-RRRR' with your own format mask specified in the Format property of the item. By using RRRR in the format mask instead of YYYY, century- and millenium sensitive dates are handled correctly.)
Also, set the form items created_on and application_no to NOT DISPLAYED (null-canvas) or set their ITEM TYPE to Display item. That way, Forms won't ask you to enter any value upon validation.
Hope this helps.
af
On Fri, 03 Mar 2000 15:24:00 +0000, Kevin Hughes <j.k.hughes_at_bangor.ac.uk> wrote:
>Hi,
> First of all my apologies if this is not the correct group to post this
>question but only this group and comp.database.oracle are in my newsfeed.
>
>This is my problem. I am attempting to insert a record into a table. On
>insert i need to generate a timestamp (created_on)and a sequential number
>(application_no) which are also inserted into the same table. Both these
>attributes are designated as 'not null' in the table.
>The only way in which i've got it to work is to create non navigable text
>items for the two auto generated fields, populate them
>on-new-form-instance, fill in the other text items, then insert the record
>into the table. This isn't what I want though because the timestamp is when
>i opened the form and if someone decides not to insert a record then my
>sequence will have gaps.
>Ideally, I need to create the sequence and timestamp on commit and also not
>have them on the form.
>
>Can anyone suggest a way in which I can do this?
>
>Thanks in advance for any help.
>--
>J. Kevin Hughes j.k.hughes_at_bangor.ac.uk
>Administrative Computing
>University of Wales - Bangor
>Adeilad Deiniol
>Ffordd Deiniol / Deiniol Road
>Bangor LL57 2UX
>Tel: (01248) 382373 Fax: (01248) 383826
Received on Fri Mar 03 2000 - 16:01:14 CST