Building Web Applications with Oracle HTML DB, Part 2, Page 2
- In your application, navigate to the form that updates employee information.
- Click Show Edit Links from the bottom of the page. Click the edit option on the delete button.
- Scroll down. In the Target is a field, select the 'URL' option.
Adding complex validations
Sometimes, even the simplest of applications requires validation processing that is more complex in nature. For example, if you are entering data into the database, SQL lookups may be required in some validation code to ensure that certain business rules are enforced. Oracle HTML DB allows you to write custom PL/SQL code so developers can customize as necessary. Our first example uses PL/SQL to check the value of multiple fields in a form.
- Click Show Edit Page from the bottom of the employee update page.
- In the Page Processing section, under Validations, click Create.
- For Name, type 'CHECK_JOB_AGAINST_SALARY'. Leave Validation Type as 'PL/SQL Expression'. Click Next.
- In the Type field, select 'Function Returning Error Text'.
- In Validation, enter the following PL/SQL code:
BEGIN --Only Presidents can make the big bucks: IF :P4_JOB <> 'PRESIDENT' AND :P4_SAL > 75000 THEN RETURN 'Only the President can make this much money!!!!'; ELSE RETURN NULL; END IF; EXCEPTION WHEN OTHERS THEN RETURN SQLERRM; END;
Attempt to update an employee who is not the 'President' by giving them a salary greater than $75,000. When you do so, this validation code will execute and the error will be displayed next to the salary field.
As mentioned previously, you may be required to access the database during form validation. Add another validation by the name of 'CHECK_FOR_MULTIPLE_MANAGERS' that enforces a business rule that states for any given department, there can be at most one manager. Here is the PL/SQL validation code we will use:
DECLARE mgr_cnt PLS_INTEGER := 0; BEGIN --Only 1 Manager per department: IF :P4_JOB = 'MANAGER' THEN SELECT COUNT(1) INTO mgr_cnt FROM emp WHERE deptno = :P4_DEPTNO AND empno <> :P4_EMPNO AND job = 'MANAGER'; IF mgr_cnt > 0 THEN RETURN 'Department '||:P4_DNAME||' already has a Manager!!!!'; END IF; END IF; RETURN NULL; EXCEPTION WHEN OTHERS THEN RETURN SQLERRM; END;
In our application, there is a department called 'Research'. In this department, employee Ford has the job title of 'Manager' and employee Jones is an 'Analyst'. According to business rules, Jones can't be promoted to Manager while Ford already holds the title of Manager. Figure 2 shows the results of attempting to do so: a repopulated form with a descriptive error message.
Figure 2: Update Failure on Employee Form
Hopefully, these articles have demonstrated how to construct an HTML DB application with functionality that most business applications require. It is not possible for me to step through all of the functionality that Oracle HTML DB offers. Experimentation is the best way to learn how this tool can help you build robust web-enabled applications, in a minimal amount of time. Visit Oracle's HTML DB site for more information. There, you will find, among other things, a fairly thick User's Guide that will serve as a nice reference while you work.
About the Author
Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.