Oracle Forms provides a feature called “Query/Where” that allows you to modify existing SQL statements on the Smart Materials screens. This feature is quite useful for power-users but also potentially dangerous because any Forms user can execute any SQL statement. In addition, you are not limited to the fields that are shown on the form itself.
Because of the potential danger of SQL injection, Oracle has turned off this functionality by default. To enable the Query/Where feature, you must set the FORMS_RESTRICT_ENTER_QUERY=FALSE in the default.env file and restart forms.
The use of this feature requires a detailed knowledge of the underlying database structure and of the SQL query language.
You can use this Query/Where feature on each screen. Open the screen, and switch to query mode using the Enter Query button. Now place a colon (:) or ampersand (&) character into one of the fields, and click the Run Query button to execute the query.
The Query/Where dialog box opens as shown in the screenshot below.
Enter a valid where clause, and click the OK button to perform the query. Click Cancel to close the dialog box without performing the query. After entering a where clause, you can use the Search button to search and/or replace strings in the clause.
For example, if you enter the where clause displayed in the picture below on the A.20.06.02 User Securities for Projects screen, the query retrieves all records with RS in the User field and with a role assigned in the Role field that includes the ADMIN module.
If you have entered the colon (:) in the field, you can use the colon as a variable instead of the column name. The picture below shows an example with variables (:A and :B) used in the Company and Country Code fields.
The query condition in the next picture uses both variables.
Additionally, an order by statement was added to change the sort order of the displayed records. The result is displayed in the picture below.
As with the ‘normal’ search, you can search for:
empty fields (… is NULL)
fields that are not empty (… is NOT NULL)
You can use logical operators like:
< (less than)
> (greater than)
<= (less or equal)
>= (greater or equal)
In text fields, you can use:
LIKE - in connection with wildcards; for example: like ‘%tech%’ finds all values with the string ‘tech’ somewhere in it.
SUBSTR - to find values having a particular string at a specific place in the value; for example: substr(company_code,1,3)=’111’ finds all companies starting with 111.
INSTR - to find values having a particular string anywhere in it; for example: instr(company_code,’111’)>0 finds all companies starting with 111.
If your SQL statement is not correct like ‘proj_id=RS’, Oracle returns the error ‘FRM-40505: Oracle error: unable to perform query.’ as shown in the screenshot below.
You can obtain a detailed error message by clicking Help > Display Error or by pressing SHIFT+F1.