What Is Lexical Reference? How Can It Be Created?
Answers
Lexical reference is place_holder for text that can be embedded in a SQL statements. A lexical reference can be created using & before the column or parameter name.Oracle BI Publisher supports lexical parameters for Oracle Fusion Applications and Oracle E-Business Suite. Lexical parameters enable you to create dynamic queries.
In BI Publisher, lexical parameters are defined as:
Lexical – PL/SQL packaged variable defined as a data model parameter.
Key Flexfield (KFF) – Lexical token in a data set query. KFF creates a "code" made up of meaningful segment values and stores a single value as a code combination id. Key Flexfields always return as a single column when used in SELECT / SEGMENT METADATA type or condition when used in WHERE clause. Key Flexfields execute at run time to extract the lexical definition and then are substituted in the SQL query.
Descriptive Flexfields (DFF) – Customizable expansion space to track additional information that is important and unique to the business. DFFs can be context sensitive, where the information stored in the application depends on the other values of the user input. Unlike Key Flexfields, Descriptive Flexfields can have multiple context-sensitive segments.
When you define any lexical, name the lexical to match the usage so that when the editor dialog pops up it will be easier to enter the default values for the SQL query. For example, if you are using a lexical in a SELECT clause, use "_select" as a suffix. The default values must be valid to get metadata.
The following example demonstrates the usage of a lexical:
When you create the data set query for the select columns, specify column alias
When you save the query, a pop-up dialog prompts you for the default values. To get SQL metadata at design time you must specify the default values that can form a valid SQL query. For example,