Lexical & Bind Parameters in Reports

Lexical Parameters: Lexical references are placeholders for text 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.

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.  Look at the example below.

You create a lexical reference by entering 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:

Ø  Before you create your query, define a column or parameter in the data model for each lexical reference in the query.  For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value.  Report Builder uses these values to validate a query with a lexical reference.

Ø  Create your query containing lexical references.

Bind Parameters: Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date.  Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.  Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.

You create a bind reference by entering a colon (:) followed immediately by the column or parameter name.  If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.