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;
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
