Suppose you want to populate a non-database data block with records manually in Oracle forms. This task can be done using a cursor.

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

Follow To Get Notifications For Free Source Code

Ask Your Questions By Comment Below.

An example given for populating tabular grid block with records through cursor 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.