How to Use Enter_Query in Oracle Forms

Working with Oracle Forms often involves searching for specific records from a database table. The ENTER_QUERY built-in is one of the most important tools for this purpose. It allows developers and end-users to put a form into query mode so that specific conditions can be entered to fetch data. In this article, you will learn what ENTER_QUERY is, how it works, its syntax, practical examples, and best practices to use it effectively.

What is ENTER_QUERY in Oracle Forms?

The ENTER_QUERY built-in is used to switch a block into query mode. When a block is in query mode, all current data displayed in that block is cleared from the form, and the user can enter search criteria. Once conditions are provided, the EXECUTE_QUERY built-in retrieves records from the database based on those criteria.

This is extremely useful when dealing with large data sets, as it allows filtering of results instead of displaying all records.

Key Features of ENTER_QUERY

• Clears all the records currently in the block.
• Switches the block into query mode.
• Accepts search conditions entered by the user.
• Works in combination with EXECUTE_QUERY.
• Can be restricted using the ON-INSERT, ON-UPDATE, or WHEN-NEW-FORM-INSTANCE triggers for tighter control.

Syntax of ENTER_QUERY

The basic syntax is very simple:

ENTER_QUERY;

Optionally, you can specify whether to clear criteria:

ENTER_QUERY(NO_VALIDATE);

• NO_VALIDATE ensures that validations like mandatory items, data type checks, or triggers are not fired when the form switches into query mode.

Steps to Use ENTER_QUERY

Here’s how you typically use ENTER_QUERY in Oracle Forms:

1. Invoke ENTER_QUERY
   • Place the form block into query mode by calling ENTER_QUERY.
2. Enter Search Criteria
   • The user types conditions into items (fields) within the block.
   • For example: EMP_NAME = 'SMITH' or SALARY > 5000.
3. Execute the Query
   • Call EXECUTE_QUERY to fetch matching rows from the database.

Practical Example

Imagine you have a block named EMP_BLOCK based on the EMPLOYEES table. You want to let users search employees by department and salary range.

Example Code in a Button Trigger

BEGIN
   ENTER_QUERY;
END;

When this button is clicked, the block enters query mode. The user can then type conditions like:

• DEPARTMENT_ID = 50
• SALARY > 3000

After entering criteria, the user presses F8 or triggers:

BEGIN
   EXECUTE_QUERY;
END;

This fetches only the matching records.

Example with NO_VALIDATE

If you want to allow entering query mode without validating required fields, you can use:

BEGIN
   ENTER_QUERY(NO_VALIDATE);
END;

This is especially helpful if your block has mandatory fields defined, but you don’t want them enforced when searching.

Where to Use ENTER_QUERY

1. Custom Search Buttons
   • Instead of relying only on function keys like F7, you can create a “Search” button that calls ENTER_QUERY.
2. Conditional Queries
   • In triggers such as WHEN-BUTTON-PRESSED, you can combine ENTER_QUERY with EXECUTE_QUERY to streamline searches.
3. Pre-Defined Criteria
   • You can programmatically place a value in an item after ENTER_QUERY to pre-fill query conditions.

Example: Pre-Filled Query Condition

Suppose you want to search for employees in department 10 by default:

BEGIN
   ENTER_QUERY;
   :EMP_BLOCK.DEPARTMENT_ID := 10;
   EXECUTE_QUERY;
END;

This code automatically filters employees belonging to department 10 without requiring user input.

Best Practices for ENTER_QUERY

• Use with EXECUTE_QUERY: Always pair ENTER_QUERY with EXECUTE_QUERY for complete functionality.
• Avoid Mandatory Validations: Use NO_VALIDATE where mandatory fields might cause issues.
• User Guidance: Provide tooltips, labels, or instructions so end-users understand query mode clearly.
• Restrict Queries: Use triggers like PRE-QUERY or POST-QUERY to control what data users can fetch.
• Security Considerations: Limit access to sensitive data fields that can be queried.

Common Errors and Troubleshooting

1. ORA-41002: Record already locked
   • Happens if you try to query while a record is being updated. Always save or clear the record before using ENTER_QUERY.
2. No Data Returned
   • Verify if the entered criteria are valid and match existing database records.
3. Form Stuck in Query Mode
   • Ensure you follow ENTER_QUERY with EXECUTE_QUERY or cancel query mode with CLEAR_BLOCK.

Difference Between ENTER_QUERY and EXECUTE_QUERY

• ENTER_QUERY places the block into query mode for specifying conditions.
• EXECUTE_QUERY actually runs the query based on those conditions and retrieves rows.

They must be used together to get meaningful results.

Conclusion

The ENTER_QUERY built-in in Oracle Forms is an essential feature for efficient data retrieval. It empowers users to filter large datasets by entering specific conditions before executing a query. By combining it with EXECUTE_QUERY, developers can create powerful search functionalities in forms. Using features like NO_VALIDATE and pre-filled criteria, you can enhance usability and provide a smoother experience. Always remember to apply best practices and error handling for secure and reliable query operations.