Solved

Multirow validation on Oracle Forms database block

Posted on 2008-10-17
3
1,158 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

730 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