Solved

Multirow validation on Oracle Forms database block

Posted on 2008-10-17
3
1,150 Views
Last Modified: 2013-12-18
I have a database block on one form and on that block there is one field which needs "multirow validation".

This field "USE_ORDER" is defined so that one of the records on that block needs to have value 1 on it and only one record.

I cannot check this on field validation nor cannot I check this on record level commit processing as there might be a situation like:
Row1: User has changed value 1 -> 0
Row2: User has changed value 0 -> 1

I do not want to allow to user to continue before this situation has been solved.

How this kind of multirow validations are recommended to be handled in Oracle Forms?
0
Comment
Question by:joulaa
  • 2
3 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 22740083
Hi joulaa, so it is possible that the user enters a value into this field for every record in the block, but you don't want this to be validated until they commit the data at which point it should be rejected because only one record can have a value of 1 in that field?

Does the field USE_ORDER only exist for the purpose of selecting an order that is currently displayed in the block?  Is there anything that relates the rows that are displayed in the block, for which only one can be identified as having a value of 1 in USE_ORDER?  Is USE_ORDER implemented as a checkbox (1 = checked, 0 = unchecked)?

Does this field control some other processing that occurs in the form or is it simply used to update a column in a table?

Sorry for all the questions but I'm trying to understand your requirement as there are a number of ways to do this, but it depends on what you are intending to do with USE_ORDER.
0
 

Author Comment

by:joulaa
ID: 22747363
Hi Milleniumaire,

First, the USE_ORDER is just a field in this table, rows are not orderred on that block by this field (the field is used elsewhere in the process as an order how these records are handled - so this block is just for insert/update/delete for basic data).

The field is length 1 number (accepted values 0,1,2). Value 1 needs be on one of the rows and also only one of the rows can have value 1.

I think it cannot be validated on this field nor it can be validated "after record". The problem is also that user might have used Forms searching/filtering so that in block there is not value 1 shown, but it is in database.

Hope I did answer all the questions...
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 250 total points
ID: 22755908
Hi joulaa,

Based on the additional information you provide it sounds like you need to put the validation on the POST-FORMS-COMMIT trigger (at form level).  This will fire after all PRE and POST commit triggers have fired, but just before the data is commited to the database (so it can still be rolled back by failing the trigger).
This trigger will be okay if you can write generic code to check all data in the table to ensure not more than one "whatever grouping applies" has the USE_ORDER flag set.
Alternatively, you could put this code on a database trigger on the table containing the USE_ORDER flag, but again it will need to be generic code checking for data integrity.  In this case, an after insert/update statement level trigger would be used.  If you use a database trigger, you will need to build code in the form to handle the error that may be raised by the trigger when it fails.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

770 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