Email Us :
Free Training > SQL PL / SQL Forms Reports Oracle Applications
< Previous Next >

Forms - 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, lets 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.
We can see the database error by using Help Display error

Using Views
Data blocks can be created by using views.

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

Lets 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.


< Previous Next >