A script-enabled browser is required for this page to function properly.

About lexical references

Lexical references are placeholders for columns or parameters that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. Use a lexical reference when you want to substitute multiple values at runtime.

You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL, as shown in the example below.

You create a lexical reference by typing an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:

Restrictions

Parent Query:

SELECT DEPTNO FROM EMP

Child Query:

SELECT &PARM_1 COL_1, &PARM2 COL_2
  FROM EMP
  WHERE &PARM_1 = :DEPTNO

Note how the WHERE clause makes a bind reference to DEPTNO, which was selected in the parent query. Also, this example assumes that you have created a link between the queries in the Data Model view with no columns specified.

Examples

Example 1:   SELECT clause

SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE
  FROM EMP;

P_ENAME, P_EMPNO, and P_JOB can be used to change the columns selected at runtime. For example, you could enter DEPTNO as the value for P_EMPNO on the Runtime Parameter Form. Note that in this case, you should use aliases for your columns. Otherwise, if you change the columns selected at runtime, the column names in the SELECT list will not match the Report Builder columns and the report will not run.

Example 2: FROM clause

SELECT ORDID, TOTAL
  FROM &ATABLE;

ATABLE can be used to change the table from which columns are selected at runtime. For example, you could enter ORD for ATABLE at runtime. If you dynamically change the table name in this way, you may also want to use lexical references for the SELECT clause (look at the previous example) in case the column names differ between tables.

Example 3:   WHERE clause

SELECT ORDID, TOTAL
  FROM ORD
  WHERE &CUST;

CUST can be used to restrict records retrieved from ORD. Any form of the WHERE clause can be specified at runtime.

Example 4:  GROUP BY clause

SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL
  FROM ORD
  GROUP BY &NEWCOMM;

The value of NEWCOMM can be used to define the GROUP BY clause.

Example 5:   HAVING clause

SELECT CUSTID, SUM(TOTAL) TOTAL
  FROM ORD
  GROUP BY CUSTID HAVING &MINTOTAL;

The value of MINTOTAL could, for example, be used to select customers with a minimum total of orders.

Example 6:   ORDER BY clause

SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL
  FROM ORD
  ORDER BY &SORT;

The value of SORT can be used to select SHIPDATE, ORDERDATE, ORDID, or any combination as the sort criterion. It could also be used to add on to the query, for example to add a CONNECT BY and START WITH clause.

Example 7:   CONNECT BY and START WITH clauses

Parameters in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.

Example 8:   Multiple clauses

SELECT &COLSTABLE;

COLSTABLE could be used to change both the SELECT and FROM clauses at runtime. For example, you could enter DNAME ENAME, LOC SAL FROM DEPT for COLSTABLE at runtime.

SELECT * FROM EMP &WHEREORD;

WHEREORD could be used to change both the WHERE and ORDER BY clauses at runtime. For example, you could enter the following at runtime for &WHEREORD:

WHERE SAL > 1000 ORDER BY DEPTNO

Example 9:   PL/SQL and SQL 

SELECT &BREAK_COL C1, MAX(SAL)
  FROM EMP
  GROUP BY &BREAK_COL;

BREAK_COL is used to change both the SELECT list and the GROUP BY clause at runtime. The Initial Value of the parameter &BREAK_COL is JOB. At runtime, the user of the report can provide a value for a parameter called GROUP_BY_COLUMN (of data type Character). In the Validation Trigger for GROUP_BY_COLUMN, you call the following PL/SQL procedure and pass it the value of GROUP_BY_COLUMN:

procedure conv_param (in_var IN char) is 
begin 
  if upper(in_var) in ('DEPTNO','EMPNO','HIREDATE') then 
    :break_col := 'to_char('||in_var||')' ; 
  else 
    :break_col := in_var; 
  end if; 
end;

This PL/SQL ensures that, if necessary, a TO_CHAR is placed around the break column the user chooses. Notice how in SQL, you make a lexical reference to BREAK_COL. In PL/SQL, you must make a bind reference to BREAK_COL because lexical references are not allowed in PL/SQL.

See also

About parameters

About bind references

Differences between bind and lexical references