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.

Follow @foxinfotech

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;

Populating Tabular Data Block Manually Using Cursor in Oracle Forms

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

4 Comments