Monday, November 26, 2012
SQL Business Rules for Oracle Databases

SQL business rules are implemented in the programing language supported by application database engine.

If an Oracle database is the foundation of your data tier, then the programming language of SQL business rules is PL/SQL.

Consider Employees page displaying createForm1 in a project created straight from HR sample database.

Page 'Employees' displaying 'createForm1' view in a project created from the sample HR database available with Oracle database engines

None of the fields has a default value. Let’s assign default values to First Name, Last Name, Hire Date, and Salary.

Start Project Designer, activate Controllers tab, locate EMPLOYEES data controller and start creating a new SQL business rule.

Creating a new SQL business rule in Code On Time application generator

Configure the rule as follows:

Property Value
Command Name New
Type SQL
Phase Execute
Script

begin
    :FIRST_NAME := 'John';
    :LAST_NAME := 'Doe';
    :HIRE_DATE := sysdate;
    select avg(SALARY) into :SALARY from EMPLOYEES;
end;

Save the rule.

The script is written in PL/SQL. The highlighted field names are referenced as parameters in the script text.

image

When executed, the script will assign static values to First Name and Last Name fields.

The current system date will be assigned to Hire Date field.

An average of all employee salaries will be assigned to Salary field.

SQL business rule writtent in PL/SQL assigns default values to the field when a user starts creating a new record

The full power of PL/SQL is available to application developers when creating applications with Code On Time.