Skip to Main Content

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

approve_workflow.JPG

And here is the same page in the Apex system

approve_apex.JPG

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;"

reject_note.JPG

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

note.JPG

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

dialog.JPG

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.

books.JPG

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

Comments

onkar.nath

V$SQL should give you the query based on session id.

If the table is used so much then you should consider having an index on the same. you should also consider tuning the query in question but first get the query. If you can enable trace on the session for the given period of time then try enable 10046 trace which should give you the exact query and its plan.

Onkar

observer_83

Hi Smohib,

If you already located suspected query and want to extract the plan based on the partial query text, use below

select sql_text from v$sqltext where hash_value='SQL_HASH_VALUE' order by piece;

sql_hash_value, you can query from v$session as below

select sql_hash_value,prev_hash_value /* prev_hash_value is historic value of the same sql */ from v$session where sid='&sid';

then, extract the plan of the query and share ..

Smohib
Thanks observer_83 & onkar.nath for the tips, will look into them once I get remote connection from client, mean while I just want to know as to what is the below statement about.
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; BEGIN begin PROCMRRSTOCK; end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
This is the query in AWR report which takes 51% of database time to get executed(ONCE), I will have to tune this process.
Smohib

Hi,

I found out that I cant generate explain plan for "declare" statement... it says "SELECT" keyword missing....

the 2nd query which I told, has "UPDATE MOHIBSTOCK SET MRRNO=:B2 WHERE MRRID=:B1" in AWR report, I doubt I will get sql_fulltext for this too..

also please let me know in AWR which sections I need to check which will lead to slow performance?

Thanks,

Mohib

Harmandeep Singh

To get the full query

select sql_text from dba_hist_sqltext where sql_id='<text>';

1. Query which has very high executions , multiple times a day, create the necessary index so that you have less load on buffer cache , which in turns improves overall application performance.

2. Generate the trace for the reports which are running slow to further analyze the issue.

It seems like at the start of day at 6:30 AM, since there is no data in cache for report it is taking time.

In evening , since most of report data is cached , it is running fast.

At 10, it is slow, for the same reasons of caching as well as overall load on box.

3. Check the buffer cache/ SGA advisories too , to see if increase in SGA is required.

Thanks,

Harman

Smohib

Hi,

@Harmandeep/ observer_83 / onkar.nath

Thanks for the tip, but unfortunately I got the same output from dab_hist_sqltext i.e.; "UPDATE MOHIBSTOCK SET MRRNO=:B2 WHERE MRRID=:B1"

Anyways I successfully created a index on the table "MOHIBSTOCK" & its better , the report is being generated in about 10-30 mins will have to collect more information from client after observation for a few more days.

Yes will look into Buffer Cache & SGA too, but here there is issue about RAM...very less RAM is configured & client is not ready to upgrade RAM for time being...anyways I will look into it.

I never wanted to bring Listener or anything related to DB down, hence brought down the application server down (restarted) so that users who have not ended their session are disconnected & hence the table is free & I will create index, but that dint happen checked from v$session & killed the process using "MOHIBSTOCK" table(used by SYS) & then successfully created index on the table.

Although things are working fine, just want to know what I did was correct? killing a process run by SYS?

any link where in I can find what happens if SYS process is killed? how it has impact on other processes of database?

Please help me on this.

Thanks all for ideas

Mohib

observer_83

Smohib,

Normally index creation in production is done when application server is down or when there is no activity on that table, If you are sure that no user are logged in and changing that table properties then you may create index online too.

Here creating index on "MRRID" will be helpful.Once the necessary RAM is added you can alter SGA,buffer cache and shared pool accordingly.

Thanks

GV

observer_83

Also killing any user defined(Known) process on session level is safe. if it is an application user then you should create index with that user and not with "sys".

Thanks

GV

Smohib
Answer

GV,

Yes we brought down the application server, still I could find ONLY SYS using the table, hence killed that process & created index.

had tried creating online index too..but got error "resource busy" thats the reason opted above steps....

yes had created index logging in as user, not as sys as it was created by developer will confirm again.

Mohib

Marked as Answer by Smohib · Sep 27 2020
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 15 2018
Added on May 16 2018
34 comments
5,134 views