We can retrieve data by using the Query Where Dialog Box. This box is used to complex search criteria. First of all, we need to knowledge of raw SQL for this process. Basically Query/Where Dialog Box is used for complex search conditions, queries with OR predicates, order the result of a query.
We need to change environment variable to run the Query Where Dialog Box. Now, I am showing details below how to change environment variable to active this box.
- Click on desired drive where Oracle Forms Builder installed.
- Click on DevSuiteHome_1
- Click on forms folder
- Click on server folder
- Right click on default.env file and open with notepad.
- We will see FORMS_RESTRICT_ENTER_QUERY, that is initially set to TRUE. We must set this
environment variable to FALSE to invoke the Query/Where dialog box. So remove TRUE and write FALSE here. - After changing, it will be FORMS_RESTRICT_ENTER_QUERY=FALSE then save the notepad.
Now our Query Where Dialog Box is active to complex search conditions.
Example of Using Query/Where Dialog Box:
================================
1/ Create a Data Block to select any table from Oracle Forms Builder. I am using employees table and its item to give an example.
2/ After creating Data Blocks, run this block with web browser.
3/ Click on the Query then select Enter from default menu.
4/ Enter a colon (:) or an ampersand (&) with a unique character variable name in one or more items.
Example:
employee_id = $e or employee_id = :e (Here e is a unique character variable name)
5/ Click on the execute query button from menu toolbar. The Query/Where dialog box is displayed.
6/ Enter the search criteria by using variables, SQL, and logical operators then click OK button.
Example:
&e between 150 and 200
7/ Above condition will show data from employee_id 150 to 200. We can check this data by using previous or next button from menu toolbar.