Query Reporter - Substitution Variables


Very often your reports will require some additional input before they can run. Let's assume that you want create a report for all employees of a specific department. In that case you need supply a substitution variable in the SQL text:

   select * from emp
   where deptno = &department
   order by ename

When this report is run, the user is prompted for a department before the select statement is executed. This is of course not very foolproof. What if the user enters nothing? The statement would lead to a ORA-00936: missing expression exception. What if the user enters a value that is not a number? Or a number that does not exist in the dept table? To prevent these situations, you can use many powerful substitution variable options. These options must be specified between brackets, just like attributes in an HTML or XML document.

The example above is a report that displays all columns of a table. The end-user needs to supply the table owner and name, for which two substitution variables are defined. The owner can be selected from all database users. The table name can be selected from all tables owned by that user.