Record Groups in Oracle Forms — What They Are and How to Use Them

If you have worked with Oracle Forms LOVs or populated list items dynamically, you have already used Record Groups — even if you did not know it. Record Groups are one of those foundational Oracle Forms objects that operate quietly behind the scenes, powering some of the most important data-driven features in your forms.

In this guide, we break down exactly what Record Groups are, the different types available, and how to use them effectively in real-world Oracle Forms applications.

What Is a Record Group?

A Record Group is an internal Oracle Forms object that stores tabular data — rows and columns — in memory. Think of it as a temporary, in-memory result set that your form can use for various purposes.

Record Groups are used to:

• Power LOVs (every LOV has a Record Group as its data source)
• Populate list items (drop-down lists) at runtime
• Store intermediate query results for processing
• Pass data between form blocks or between forms
• Perform custom multi-record operations without database commits

Types of Record Groups

Oracle Forms supports three types of Record Groups, each serving a different purpose:

1. Query Record Group

The most common type. It is based on a SQL SELECT statement. When you populate a Query Record Group, Oracle Forms runs the SQL query against the database and loads the results into memory.

-- Defined at design time in the Record Group properties
SELECT department_id, department_name
FROM   departments
WHERE  active_flag = 'Y'
ORDER BY department_name

The query is fixed at design time but can use bind variables that reference form items, allowing the results to be filtered dynamically at runtime.

2. Static Record Group

A Static Record Group contains data that you define manually at design time — no database query involved. You specify the columns and rows directly in Forms Builder.

Use this for small, fixed lists of values that will never change, such as:

• Status codes (Active, Inactive, Pending)
• Priority levels (High, Medium, Low)
• Yes/No lists

Static Record Groups load instantly because they do not require a database round trip.

3. Non-Query Record Group

A Non-Query Record Group is created entirely through PL/SQL code at runtime. You define the structure (columns and types) and add rows programmatically. This is the most flexible type and gives you complete control over the data.

Working with Record Groups in PL/SQL

Record Groups can be manipulated entirely through PL/SQL built-in functions. Here are the most important ones:

Finding a Record Group

DECLARE
  rg_id RECORDGROUP;
BEGIN
  rg_id := FIND_GROUP('DEPT_LOV_RG');
  IF ID_NULL(rg_id) THEN
    MESSAGE('Record Group not found.');
  END IF;
END;

Populating a Query Record Group

DECLARE
  rg_id RECORDGROUP;
  err   NUMBER;
BEGIN
  rg_id := FIND_GROUP('DEPT_LOV_RG');
  err   := POPULATE_GROUP(rg_id);
  
  IF err <> 0 THEN
    MESSAGE('Error populating record group: ' || TO_CHAR(err));
  END IF;
END;

Creating a Non-Query Record Group at Runtime

DECLARE
  rg_id   RECORDGROUP;
  col_id  GROUPCOLUMN;
  err     NUMBER;
BEGIN
  -- Create the group structure
  rg_id := CREATE_GROUP('TEMP_GROUP');
  col_id := ADD_GROUP_COLUMN(rg_id, 'CODE', CHAR_COLUMN, 10);
  col_id := ADD_GROUP_COLUMN(rg_id, 'LABEL', CHAR_COLUMN, 50);
  
  -- Add rows
  err := ADD_GROUP_ROW(rg_id, END_OF_GROUP);
  SET_GROUP_CHAR_CELL('TEMP_GROUP.CODE',  1, 'A');
  SET_GROUP_CHAR_CELL('TEMP_GROUP.LABEL', 1, 'Option A');
  
  err := ADD_GROUP_ROW(rg_id, END_OF_GROUP);
  SET_GROUP_CHAR_CELL('TEMP_GROUP.CODE',  2, 'B');
  SET_GROUP_CHAR_CELL('TEMP_GROUP.LABEL', 2, 'Option B');
END;

Reading Values from a Record Group

DECLARE
  rg_id    RECORDGROUP;
  row_cnt  NUMBER;
  code_val VARCHAR2(10);
BEGIN
  rg_id   := FIND_GROUP('TEMP_GROUP');
  row_cnt := GET_GROUP_ROW_COUNT(rg_id);
  
  FOR i IN 1..row_cnt LOOP
    code_val := GET_GROUP_CHAR_CELL('TEMP_GROUP.CODE', i);
    -- Process each row
    DBMS_OUTPUT.PUT_LINE('Row ' || i || ': ' || code_val);
  END LOOP;
END;

Deleting a Record Group

DELETE_GROUP('TEMP_GROUP');

Always delete dynamically created Record Groups when you are done with them to free memory. Groups created at design time persist for the life of the form session.

Populating a List Item from a Record Group

One powerful use of Record Groups is dynamically populating a drop-down list item. This is useful when the available options depend on runtime context.

DECLARE
  rg_id RECORDGROUP;
  err   NUMBER;
BEGIN
  -- Use a predefined Query Record Group
  rg_id := FIND_GROUP('STATUS_LIST_RG');
  err   := POPULATE_GROUP(rg_id);
  
  IF err = 0 THEN
    -- Populate the list item from the record group
    POPULATE_LIST('BLOCK.STATUS_ITEM', rg_id);
  END IF;
END;

The POPULATE_LIST built-in replaces all existing list item values with the contents of the Record Group. The first column becomes the display label and the second column becomes the return value.

Using Bind Variables in Record Group Queries

Query Record Groups support bind variables that reference form items. This allows the same Record Group to return different data based on the current form context:

-- Record Group query with bind variable
SELECT product_id, product_name, unit_price
FROM   products
WHERE  category_id = :ORDERS.CATEGORY_ID
AND    active = 'Y'
ORDER BY product_name

When you call POPULATE_GROUP, Oracle Forms substitutes the current value of :ORDERS.CATEGORY_ID automatically. The results are specific to the currently selected category in the ORDERS block.

Modifying the Query at Runtime

Sometimes you need to change the SQL query of a Record Group dynamically at runtime. Use POPULATE_GROUP_WITH_QUERY:

DECLARE
  rg_id  RECORDGROUP;
  err    NUMBER;
  v_sql  VARCHAR2(1000);
BEGIN
  rg_id := FIND_GROUP('CUSTOM_RG');
  
  -- Build a dynamic query
  v_sql := 'SELECT emp_id, full_name FROM employees WHERE dept_id = ' 
            || TO_CHAR(:DEPT_BLOCK.DEPT_ID);
  
  err := POPULATE_GROUP_WITH_QUERY(rg_id, v_sql);
  
  IF err <> 0 THEN
    MESSAGE('Query failed.');
  END IF;
END;

Security note: Be careful with dynamic SQL in Record Groups, especially if any part of the query is derived from user input. Always use bind variables where possible to avoid SQL injection risks.

Common Mistakes with Record Groups

• Not populating before using: A Query Record Group defined at design time is empty until you call POPULATE_GROUP. If you use it in an LOV without populating it, the LOV will show no data.
• Forgetting to delete dynamic groups: Creating Record Groups in frequently called triggers without deleting them leads to memory accumulation.
• Column count mismatch in POPULATE_LIST: The Record Group must have at least 2 columns for POPULATE_LIST. The first is the label (displayed to user) and the second is the value (stored in the item).
• Bind variable not resolving: If a bind variable in the Record Group query refers to a NULL item, the query may return no rows. Add a check before calling POPULATE_GROUP.

Summary

Record Groups are the backbone of dynamic data presentation in Oracle Forms. Whether you are powering an LOV, populating a dropdown list, or managing intermediate result sets in complex business logic, understanding Record Groups is essential.

The three types — Query, Static, and Non-Query — cover almost every scenario you will encounter, and the PL/SQL built-in functions give you complete programmatic control. Once you are comfortable with them, you will find Record Groups appearing naturally in your solutions to data management challenges.