Solved

validate field oracle form

Posted on 2016-10-30
3
30 Views
Last Modified: 2016-11-20
hi i what to validate field based on this condition
If empInd = A and/or  deptControl = Y , then the RegCode may not be "R" or "N".
0
Comment
Question by:chalie001
3 Comments
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points (awarded by participants)
ID: 41866769
Which is it "and" or "or"? The following would provide records that violate the criteria.-
where empId = 'A' and deptControl = 'Y' and RegCode in ('R','N')
or
where empId = 'A' or deptControl = 'Y' and RegCode in ('R','N')
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 250 total points (awarded by participants)
ID: 41866897
I agree with awking00, that your question isn't clear, so we don't which of the two suggestions he provided is what you need.  I'm guessing that his second suggestion (with "or") is what you need.

If this is an Oracle Forms application, you also need to decide which trigger(s) to put this check in.  One option is the when-validate-record trigger for the block, but that is not my first choice.  I would prefer to put this in a when-validate-item trigger on the last one of these fields that the user normally enters.  To be safe with this approach, you also need logic in the when-validate-item triggers on the other two fields to copy the contents of the next field to itself (to make sure that the later when-validate-item triggers fire if the user ever uses the mouse or a back-tab to go back and change an earlier value).

For example, if the order of these fields in your form is:
1. empId
2. deptControl
3. RegCode

and your block is named "myblock" then the "when-validate-item" triggers on these three fields should look like this:
1. (any validation you need for the empld value) plus: :myblock.deptControl := :myblock.deptControl;
2. (any validation you need for the deptControl values) plus: :myblock.RegCode := :myblock.RegCode;
3. if :myblock.RegCode in ('R','N') and (:myblock.empId = 'A' or :myblock.deptControl = 'Y') then
      display an alert with an error message (or use whatever mechanism you normally use to inform the user of an error) and: raise_form_trigger failure...
  end if;

Of course, you could put this logic in both the pre-insert and pre-update triggers, but I like catching this problem as early as possible (so that's why I don't recommend the "when-validate-record" trigger) and I try to avoid putting this logic into multiple triggers.
1

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now