How to Use EXECUTE_QUERY in Oracle Forms • Vinish.Dev

When you need fresh records from the database in Oracle Forms, EXECUTE_QUERY is the workhorse. It fetches rows into the current data block based on search criteria, default WHERE clauses, and block properties. Used well, it keeps your screens responsive and your users confident that they are working with up-to-date information. In this tutorial, you will learn what EXECUTE_QUERY actually does, how it interacts with triggers, practical ways to filter results, and patterns to avoid performance pitfalls.

On This Page
Show More

What EXECUTE_QUERY Does

EXECUTE_QUERY runs a query for the current block and populates its records. If the block participates in a master–detail relationship, child blocks refresh automatically according to the defined relation.

Key behavior:

Fetches records from the base table or view defined for the block.
Honors the block’s DEFAULT_WHERE, ORDER_BY, and other query-related properties.
Uses values entered in Enter-Query mode as search criteria.
Fires standard query-related triggers in the expected order.

Syntax and Where to Call It

EXECUTE_QUERY;

You typically call it from:

WHEN-BUTTON-PRESSED triggers on a “Refresh” or “Search” button.
KEY-EXEQRY customizations (if you override the default key behavior).
POST-INSERT / POST-UPDATE / POST-DELETE follow-ups to refresh related blocks.
WHEN-NEW-BLOCK-INSTANCE to auto-populate a block when the user navigates to it (use with care to avoid surprise queries).

Trigger Firing Order (At a Glance)

When EXECUTE_QUERY runs, Oracle Forms typically fires:

PRE-QUERY on the block (prepare criteria, add WHERE fragments, set bind values).
ON-ERROR if something goes wrong (SQL errors, etc.).
POST-QUERY for each fetched record (format/derive display-only values, lookup descriptions).
Navigation triggers like WHEN-NEW-RECORD-INSTANCE and WHEN-NEW-ITEM-INSTANCE as the cursor lands on results.

Tip: Use PRE-QUERY to add or adjust conditions, and POST-QUERY for per-row display logic that should not be stored in the table.

Three Core Ways to Filter the Query

1) End-User Criteria with Enter-Query Mode

Let users type search values directly, then run EXECUTE_QUERY.

BEGIN
  ENTER_QUERY;
  -- User types criteria in items
  -- e.g., :EMP.EMPNO := 7369;
  EXECUTE_QUERY;
END;

Good for: Self-service search screens and ad hoc filtering.

2) Programmatic Filtering via DEFAULT_WHERE

Build criteria in code without entering query mode.

DECLARE
  v_where VARCHAR2(4000);
BEGIN
  v_where := 'deptno = :ctrl.dept_filter';
  SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, v_where);
  EXECUTE_QUERY;
END;

Good for: Guided search UIs (drop-downs, checkboxes) and reusable query buttons.

3) Mixed Approach: Seed Criteria, Then Let Users Refine

Seed a sensible default, then let users refine in Enter-Query mode.

BEGIN
  SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, 'deptno = 10');
  ENTER_QUERY;           -- user adds more criteria if needed
  EXECUTE_QUERY;
END;

Good for: Dashboards where a default view appears quickly, but power users can narrow results.

Practical Patterns You Will Use

Pattern A: “Refresh This Block” Button

BEGIN
  -- Optional: ensure no pending changes block the query
  IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN
    -- Ask or rollback, depending on your UX policy
    ROLLBACK;
  END IF;

  EXECUTE_QUERY;
END;

Pattern B: Master–Detail Refresh After Changes

When a parent record changes, refresh the child:

BEGIN
  GO_BLOCK('ORDERS');          -- master
  EXECUTE_QUERY;               -- re-fetch orders
  GO_BLOCK('ORDER_LINES');     -- detail
  EXECUTE_QUERY;               -- detail follows master state
END;

Pattern C: Conditional WHERE Based on Controls

DECLARE
  v_where VARCHAR2(4000) := '1=1';
BEGIN
  IF :CTRL.SHOW_ACTIVE = 'Y' THEN
    v_where := v_where || ' AND status = ''ACTIVE''';
  END IF;

  IF :CTRL.DEPTNO IS NOT NULL THEN
    v_where := v_where || ' AND deptno = ' || TO_CHAR(:CTRL.DEPTNO);
  END IF;

  SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, v_where);
  EXECUTE_QUERY;
END;

Working with POST-QUERY for Display-Only Fields

Use POST-QUERY to compute values not stored in the table:

-- Block: EMP  Trigger: POST-QUERY
BEGIN
  :EMP.SAL_GRADE := get_sal_grade(:EMP.SAL);
  :EMP.DEPT_NAME := get_dept_name(:EMP.DEPTNO);
END;

Guideline: Do not perform heavy DML here; keep it fast and idempotent since it runs per record.

Avoiding “No Records Found” Surprises

Users often think nothing happened when no rows are returned. Provide feedback:

DECLARE
  v_count NUMBER;
BEGIN
  EXECUTE_QUERY;
  v_count := TO_NUMBER(:SYSTEM.CURSOR_RECORD);
  IF v_count = 0 THEN
    MESSAGE('No records match your criteria.');
    MESSAGE(' ', NO_ACKNOWLEDGE); -- flush to status bar
  END IF;
END;

Performance Tips That Make a Real Difference

Limit the result set early. Use DEFAULT_WHERE to restrict rows by date ranges, status, or key filters.
Make indexed searches. Align your WHERE clause with database indexes to avoid full scans.
Avoid unbounded first load. Run EXECUTE_QUERY only after the user supplies criteria, or seed conservative defaults.
Keep POST-QUERY light. Heavy computations per row will make large fetches feel sluggish.
Paginate thoughtfully. Use RECORDS_DISPLAYED (layout) and sensible block QUERY array size settings to balance fetch and UI cost.
Clear and reset WHERE when needed. Before building a new WHERE string, you may reset the previous one:
SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, '');

Handling Pending Changes Before Query

EXECUTE_QUERY is blocked if the block has uncommitted edits. Decide your policy:

Ask to save
Prompt and commit, then re-query.
Discard
Roll back changes to allow the query.

IF :SYSTEM.BLOCK_STATUS = 'CHANGED' THEN
  -- Example policy: discard
  ROLLBACK;
END IF;

EXECUTE_QUERY;

Comparing EXECUTE_QUERY with Similar Built-ins

SYNCHRONIZE: Only refreshes the UI; does not fetch data.
ENTER_QUERY: Puts the block in query mode so users can type criteria; you still need EXECUTE_QUERY to fetch.
DO_KEY('QUERY_CLEAR'): Clears query criteria; useful when resetting a search form.
LAST_RECORD / FIRST_RECORD: Navigate within fetched results; they do not run a new query.

Common Troubleshooting Checks

“Why is my WHERE clause ignored?” Ensure you set DEFAULT_WHERE on the correct block before calling EXECUTE_QUERY.
“My derived fields are blank.” Put the population logic in POST-QUERY (not WHEN-VALIDATE-ITEM).
“Query is slow.” Review the generated SQL (use :SYSTEM.LAST_QUERY if available) and tune the database indexes/joins.
“No rows, but I expected rows.” Confirm item datatypes, item CASE_SENSITIVE_QUERY settings, and that criteria match the column types.

Best Practices Checklist

Add/adjust criteria in PRE-QUERY, derive display values in POST-QUERY.
Use DEFAULT_WHERE (programmatic) or Enter-Query (user-driven) thoughtfully—avoid mixing them accidentally.
Provide clear No data found feedback after the query.
Keep POST-QUERY fast and side-effect free.
Require minimal mandatory filters for large tables to avoid heavy, unbounded fetches.
Reset the block’s WHERE when switching filter modes.

Conclusion

EXECUTE_QUERY is central to reliable data refresh in Oracle Forms. It respects your filtering strategy, plays nicely with PRE-QUERY and POST-QUERY, and keeps master–detail blocks synchronized. By combining user-friendly criteria entry, disciplined use of DEFAULT_WHERE, and lightweight per-row formatting, you deliver fast queries, clear feedback, and a professional experience. Treat EXECUTE_QUERY as the culmination of your search logic—and make every refresh predictable, performant, and easy to understand.