Press "Enter" to skip to content

If Value Exists Then Query Else Allow Create New in Oracle Forms An Example

An example is given below for Oracle Forms when a value exists then execute the query for that value to display the correspondent record else allow the user to create a new record for that value.
The following is the example given for HR schema employee table, in this example user will enter an employee id and if the employee id exists it will query the record else allow user to create a new record, the trigger written on key-next-item trigger, you can download the employees.fmb form also from the following link employees.fmb

KEY-NEXT-ITEM trigger code

v_empid employees.employee_id%type;
Select employee_id into v_empid
from hr.employees
where employee_id = :employees.employee_id;
— value exists
— set block property and execute query
clear_block(no_validate);set_block_property(’employees’, default_where, ’employee_id = ‘||v_empid);
set_block_property(’employees’, default_where, ”);
when no_data_found then
— when not then clear block and allow to add new
v_empid := :employees.employee_id;
:employees.employee_id := v_empid;

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *