Oracle Forms on-button-pressed trigger to solve three scenarios

Question

Hello, I'm writing a when-button-pressed trigger on a save button for an Oracle Forms 6i form, and it has to fulfill a couple of scenarios.

Here's some background information: the fields we're primarily concerned with are:

n_number,
alert_id,
end_date

For all three scenarios we are comparing candidate records against the following records in the database (for the sake of argument, let's assume they're the only records in the database so far):

alert_id|| n_number|| end_date
-------------------------------------
       1||        5||      _______

       2||        6||    10/25/2009

Scenario 1: The user enters a new record:

alert_id 1
n_number 5
end_date NULL

Objective: prevent the user from committing duplicate rows

Scenario 2: The user enters a new record:

alert_id 1
n_number 10
end_date NULL

Objective: Notify the user that this alert_id already exists, but allow the user the ability to commit the row, if desired.

Scenario 3: The user enters a new record:

alert_id 2
n_number 6
end_date NULL

Objective: Notify the user that this alert_id has occurred in the past (i.e. it has a not-null end_date), but allow the user to commit the row, if desired.

I've written the code, which seems to comply with the first two scenarios, but prevents me from fulfilling the third.

Issues: When I enter the third scenario case, I am prompted to commit the record, but when I attempt this, the "duplicate_stop" alert pops up, preventing me.

Issues: I'm getting the following error: ORA-01843: not a valid month. While testing the code for the third scenario in Toad (hard-coding the values, etc) things seemed to be fine. Why would I encounter these problems at run-time?

Help is very much appreciated.

Thank you

Answer 1

Without knowing what code you have in the trigger it is a bit hard for us to diagnose. But I'll have a go: i'm feeling telepathic.

Your code for scenario #1 is checking for uniqueness based on (alert_id, n_number). I don't know how you have implemented this check but you need to include a filter for end_date is null. This would then allow the change from scenario #3 to trickle through to that warning.

As for your other problem, that points to some inconsistency between the client and database definitions for NLS_DATE_FORMATs. I'm afraid it has been a looooong time since I worked with Forms, but I seem to recall it had its own places to define such things. If you don't know how to check have a peek in the Forms Builder docs. It should be in there soemwhere.