Sponsored

An example given below for Oracle Forms, when a value exists then execute query for that value to display the correspondent record else allow 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 empoyee 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
declare
v_empid employees.employee_id%type;
Begin
 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);
    execute_query;
    set_block_property('employees', default_where, '');
    next_item;
exception
when no_data_found then
  -- when not then clear block and allow to add new
  v_empid := :employees.employee_id;
  clear_block(no_validate);
  :employees.employee_id := v_empid;
  next_item;
End;


Oracle Forms Recipes eBookDownload

Explained how to query a record for particular value if exists else allow add new in Oracle forms.

Post a Comment

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.