Solved

Multirow validation on Oracle Forms database block

Posted on 2008-10-17
3
1,141 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Pl/SQL Query 31 61
listing all functions in JavaScript 19 103
Error executing command from server 6 26
SQL Retrieve Values 4 42
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

705 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

17 Experts available now in Live!

Get 1:1 Help Now