Solved

validate field oracle form

Posted on 2016-10-30
3
51 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 32

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 35

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

792 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