APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Best way to stop execution and pop-up a message if validation fails.

TinkerBoyMay 16 2018 — edited May 18 2018

Hi all.

We are busy with a POC to see if we will be able to replace our Oracle Workflow system that has been running with a Forms front end for about 18 years. The system still work 100%. We just want to replace the forms front end so that it can run on any platform including mobile.

So we have been converting one of the processes that is not that complicated to prove that it will work.

We are using Apex 5.1 with the Universal Theme.

The process has a submit screen that does all the validation and once everything is valid then the page will kick off the Oracle Workflow process. This works 100%.

Now I am busy with the approval screen. As you can see there are a couple of buttons at the top that the manager can click. Each one of these buttons have different business rules and validations behind them.

Here is the Oracle Forms page

And here is the same page in the Apex system

For example If you click a button like Reject, the manager has to give a reason why. This reason is only required on a reject, he can enter a note anyway on any of the other buttons by clicking the notes button that I highlighted and then enter the note before he makes his selection, but is is only mandatory on Reject. On the forms app we force the user to enter the reason by displaying a error message and then stop execution by firing a "RAISE FORM_TRIGGER_FAILURE;"

Then the form will open a dialog so the manager can enter the reason.

I know how to call a dialog and pass data to and from it so the add the note is not the problem.

I do all the validation in PL/SQL in an dynamic action. But how do I stop execution of that action so that the page is not submitted or processed further like I do with a RAISE FORM_TRIGGER_FAILURE; if there is something wrong (not just a format of a field or item required validation, the validation can be a lookup of a project code and a lot more, it is complex validations, this is just a sample)

And also how do I popup a message like we do in forms, So that the user will know what to do ?

I have read 2 books so far that are recommended and have looked at a number of tutorial, none show a way to do this. What is the best way to get this working in a similar way to the Forms version.

I am sorry if I left out anything that you need more. I am new in this community. Been using Oracle for 20 years but on the Forms and DB side.

Kind Regards.

This post has been answered by Pierre Yotti on May 18 2018

Jump to Answer

780914

Hi,

RANK is the function to rank records based on a key column, while row_number is to give a unique number to a row irrespective of any column value. Check the below query and output to get better distinction between RANK, DENSE_RANK and ROW_NUMBER.

WITH T AS (
 SELECT 'EMP1' NAME, 1000 SAL FROM DUAL
 UNION
 SELECT 'EMP2', 200 FROM DUAL
 UNION 
 SELECT 'EMP3', 500 FROM DUAL
 UNION
 SELECT 'EMP4', 1000 FROM DUAL
 
)
SELECT NAME,
       SAL,
       RANK() OVER (ORDER BY SAL DESC ) "rank",
       DENSE_RANK() OVER (ORDER BY SAL DESC ) "DENSE_rank",
       ROW_NUMBER() OVER (ORDER BY SAL DESC ) "ROW_NUMBER"
FROM   T;



NAME        SAL       rank DENSE_rank ROW_NUMBER
---- ---------- ---------- ---------- ----------
EMP1       1000          1          1          1
EMP4       1000          1          1          2
EMP3        500          3          2          3
EMP2        200          4          3          4

1 person found this helpful

Vivek L

Thanks SBH, but if row_number does not depend on any column value, then why do we need a Window Clause?? woudn't it work same as ROWNUM then?

also, how does ROW_NUMBER decide to give serial number to different rows in an even of tie?

In the given example

EMP1       1000          1          1          1
EMP4       1000          1          1          2

why EMP1 is given ROW_NUMBER as 1 and EMP4 as 2?

780914

Answer

Yeah, with row_number, i meant to say that it would not describe any analytic property of a column but would generate unique number for complete record. Windowing clause would just serve as the basis of numbering.

ROW_NUMBER ignores the event of tie, but numbers the rows in the output.

"why EMP1 is given ROW_NUMBER as 1 and EMP4 as 2? "
Because numbering was done for the ranked records. Check the below query, rank function is commented and output gives the correct numbering

WITH T AS (
 SELECT 'EMP1' NAME, 1000 SAL FROM DUAL
 UNION
 SELECT 'EMP2', 200 FROM DUAL
 UNION 
 SELECT 'EMP3', 500 FROM DUAL
 UNION
 SELECT 'EMP4', 1000 FROM DUAL
 
)
SELECT NAME,
       SAL,
       --RANK() OVER (ORDER BY sal DESC ) "rank",
       --DENSE_RANK() OVER (ORDER BY SAL DESC ) "DENSE_rank",
       ROW_NUMBER() OVER (ORDER BY 1) "ROW_NUMBER"
FROM   T;


NAME        SAL ROW_NUMBER
---- ---------- ----------
EMP1       1000          1
EMP2        200          2
EMP3        500          3
EMP4       1000          4

Marked as Answer by Vivek L · Sep 27 2020

Vivek L

first two lines of your response cleared my doubts.. thanks SBH

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 15 2018

Added on May 16 2018

#apex-discussions

34 comments

4,953 views

APEX

Best way to stop execution and pop-up a message if validation fails.

Comments

Post Details