Press "Enter" to skip to content

Populating Tabular Data Block Manually Using Cursor in Oracle Forms

Suppose you want to populate a non-database data block with records manually in Oracle forms. This task can be done using a cursor. With Populating tabular data block manually using cursor in Oracle Forms, I mean is to loop through a data block to populate the data.
Below is the example given for hr.job_history table, where user input the employee id in upper block and click on the fetch button to populate the records from hr.job_history table into the lower tabular data block.

The following is the screen shot for this example: (you can also download the form from this link Job_History.fmb)

Follow to get notifications for free source code in future, thanks.
For the employee id field of upper block write the When-Validate-Item trigger code as below:
Begin
Select first_name||’ ‘||last_name into :ctrl.ename from hr.employees
  where employee_id = :ctrl.empid;
exception
when no_data_found then
   message(‘Employee id does not exists’);
   raise form_trigger_failure;
End;

For the Fetch button write the When-Button-Pressed trigger code as below:

Declare
Cursor C_jobs
is
Select employee_id, start_date, end_date,
job_id, department_id
from hr.job_history
where employee_id = :ctrl.empid;
Begin
go_block(‘job_history’);
— first clear the block if it contains any records
clear_block(no_validate);
— move control to first record;
first_record;
— open the cursor and populate the block
for cur in C_jobs loop
:job_history.employee_id := cur.employee_id;
:job_history.start_date := cur.start_date;
:job_history.end_date := cur.end_date;
:job_history.job_id := cur.job_id;
:job_history.department_id := cur.department_id;
— move control to next record;
next_record;
end loop;
— again after completion move control to first record
first_record;
End;

See also: If Value exists then query else create new in Oracle Forms

4 Comments

  1. ala min ala min September 22, 2016

    Thanks

  2. Unknown Unknown December 29, 2016

    great exhibition of knowledge…Keep it up man.

Comments are closed.