How to Clear All Records from a Block in Oracle Forms

When working with Oracle Forms, there are times when you need to remove all records from a data block. This is common when refreshing data, resetting forms for new input, or discarding records fetched from the database. Oracle Forms provides several built-ins that make it easy to Clear All Records from a Block in Oracle Forms without writing complex logic.

In this article, we will cover the different approaches available, demonstrate code examples, and highlight best practices to ensure smooth and efficient record clearing.

Why Clear All Records from a Block?

Clearing records is useful in many real-world scenarios, such as:

• Resetting a form to its initial state for fresh data entry.
• Discarding old records before fetching new ones.
• Removing temporary or unsaved data from memory.
• Clearing a child block before displaying details of a new parent record.

Without clearing records properly, users might see outdated data or accidentally overwrite existing records.

Built-in Procedures for Clearing Records

Oracle Forms provides several built-in procedures to manage block data. The most important for clearing records are:

• CLEAR_BLOCK – Removes records from the current block.
• CLEAR_RECORD – Clears only the current record.
• EXECUTE_QUERY – Fetches records again, effectively replacing old ones.
• DELETE_RECORD – Deletes a single record (when used repeatedly, can clear multiple).

For clearing all records at once, the best option is CLEAR_BLOCK.

Using CLEAR_BLOCK

The CLEAR_BLOCK built-in is designed to clear all records from the current block. By default, it prompts the user if there are unsaved changes.

Example: Simple Clear Block

BEGIN
   CLEAR_BLOCK;
END;

This removes all records from the block where the cursor is currently located.

Clearing Without Validation Prompt

If you want to clear all records without Oracle Forms asking for confirmation (even if there are unsaved changes), you can pass the NO_VALIDATE option.

Example:

BEGIN
   CLEAR_BLOCK(NO_VALIDATE);
END;

• NO_VALIDATE ignores validation and removes all records immediately.
• Use this option carefully, as it discards unsaved data.

Clearing a Specific Block

Sometimes you want to clear records from a block other than the current one. In such cases, you can first navigate to that block using GO_BLOCK, then apply CLEAR_BLOCK.

Example:

BEGIN
   GO_BLOCK('ORDERS_BLOCK');
   CLEAR_BLOCK;
END;

This code moves to ORDERS_BLOCK and clears all its records.

Clearing Multiple Blocks at Once

When a form has several related blocks (like a master-detail form), you may want to clear them together. This can be done by calling CLEAR_BLOCK for each block in sequence.

Example:

BEGIN
   GO_BLOCK('CUSTOMERS');
   CLEAR_BLOCK;
   
   GO_BLOCK('ORDERS');
   CLEAR_BLOCK;
   
   GO_BLOCK('ORDER_ITEMS');
   CLEAR_BLOCK;
END;

This ensures all three blocks (CUSTOMERS, ORDERS, and ORDER_ITEMS) are cleared.

Difference Between CLEAR_BLOCK and DELETE_RECORD

It is important to understand the difference between these two:

• CLEAR_BLOCK – Removes all records from memory (does not delete from the database).
• DELETE_RECORD – Deletes the current record both from the block and, if committed, from the database.

So if your goal is simply to refresh or reset the block, CLEAR_BLOCK is the correct choice.

Using CLEAR_BLOCK with Triggers

Often, you may want to clear records when a specific event occurs. For example:

• WHEN-BUTTON-PRESSED: A button that resets the block.
• WHEN-NEW-FORM-INSTANCE: Clearing blocks when the form opens.

Example Button Code:

BEGIN
   GO_BLOCK('EMPLOYEES');
   CLEAR_BLOCK(NO_VALIDATE);
END;

This code can be attached to a "Clear All" button for user convenience.

Best Practices for Clearing Records

• Validate business needs: Ensure you really want to discard unsaved data before using NO_VALIDATE.
• Provide user feedback: Show a message after clearing records so the user knows the block is empty.
• Use conditional clearing: In some cases, check conditions before clearing, such as confirming with the user.
• Combine with EXECUTE_QUERY: If you intend to reload fresh data after clearing, use EXECUTE_QUERY immediately afterward.

Common Errors When Clearing Blocks

• FRM-40654: Record has been updated. Requery block to see changes.
  This happens when the block contains uncommitted changes. Use NO_VALIDATE if you want to bypass it.
• FRM-40102: Record must be entered or deleted first.
  Occurs if the current record is incomplete. Again, NO_VALIDATE can help.
• Unintended data loss
  If you use NO_VALIDATE carelessly, users may lose important changes. Always use with caution.

Conclusion

Clearing records is a simple but important part of Oracle Forms development. By using the CLEAR_BLOCK built-in, developers can quickly and safely Clear All Records from a Block in Oracle Forms. Whether you need a full reset, a refresh before fetching new data, or a way to discard temporary values, CLEAR_BLOCK is the most efficient solution.

Always consider user experience, data integrity, and proper validation before clearing blocks. With careful use, this feature can make your forms cleaner, faster, and more user-friendly.