Database Constraints in Forms:-

Create the hospital table in the database.
SQL>  Create table hospital ( pat_id  number(3) primary key,
                                                Name  varchar2(20) not null,
                                                Description  varchar2(50),
                                                Fee  number(5)  check ( fee >=1000));

 

Now, let’s create the form using the above table.

Open the form builder tool Tools Datablock wizard Next Table or View Table or view – Enter hospital Refresh provide connection details Move all the columns to database items Next Data block Name – HOSPITAL Next create data block, then call layout wizard Finish

Welcome to layout wizard Next Canvas new canvas type – content Move all columns from available to displayed items Next next Select form Next Frame Title – Hospital details Next Finish

File Save as in MY_FORMS folder HOSPITAL.fmb

Program Compile module
Program Run form.

Enter the following values in the form.

Pat ID – 101
Name – Naveen
Description – Fever and cold
Fee – 200

Click on Save.

We get error FRM- 40508: ORACLE error: unable to INSER record.

To see the database error
Help Display error

 

As we have defined check constraint in the column Fee (  Fee >= 1000).
We got the check constraint violated error.
Note
We can see the database error by using Help Display error

Using Views
Data blocks can be created by using views.

Example
SQL>  Create view dept_analysis
 As  select deptno, sum(sal) totsal, min(sal) lowsal, max(sal) highsal, avg(sal) avgsal, count(*) no_emp
 From emp
 Group by deptno;

SQL>  select * from  dept_analysis;

We get the following output

Let’s build the form using dept_analysis.

Open the form builder tool
Tools datablock wizard Next Table or view Next dept_analysis Provide connection detail move all columns  from available columns to database columns Next Datablock name DEPT_ANALYSIS Next Create data block, then call layout wizard Finish

Welcome to Layout wizard Next Canvas – New canvas Next Move all items from available items Displayed Items Next Next Form Next Frame Title – Dept Info Next Finish

File Save as DEPT_ANALYSIS.fmb

Program Compile Module

Program Run Form ( Ctrl + R )

Click on Execute query

The above form does not support DML operations, as the view is complex view.