How to Loop Through Records in Oracle Forms?

How to Loop Through Records in Oracle Forms?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

If you are working in Oracle Forms having tabular data block or creating a new tabular data block, then you might require to loop through the records due to many reasons. Maybe you need to write a text file by looping through all records, or you need to perform some validations. Below I am giving an example of a loop through records in Oracle Forms, you can use it for different purposes.

Loop Through Records in Oracle Form's Tabular Data Block

In the following example, it will execute the query in the EMP block and will loop through all the records, to calculate the total salary.

DECLARE
   l_sal   NUMBER;
BEGIN
   GO_BLOCK ('emp');
   EXECUTE_QUERY;
   -- move pointer to first record
   FIRST_RECORD;
   l_sal := 0;

   LOOP
      l_sal := l_sal + :emp.sal;

      IF :SYSTEM.LAST_RECORD = 'TRUE'
      THEN
         EXIT;
      END IF;

      NEXT_RECORD;
   END LOOP;

   MESSAGE ('Total Salary: ' || l_sal);
   FIRST_RECORD;
-- move pointer to first record
END;

See also: