Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Sunday, 12 February 2017

Create Oracle Report with Lexical Parameter

            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 PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter.


          Lexical parameters are used to substitute multiple values at run time and identified by a preceding ‘&’. Lexical can consist of as little a one line where clause to an entire select statement

Lexical Parameters are used to execute query dynamically.

  Example: An example of a lexical parameter usage in a select statement is as follows
Select * from XX_table, XX_table 2 &where.

           In the properties of the ‘where’ user parameter, make sure that the data type of the ‘where’ user parameter is set as character. If you know the maximum length that your where clause is going be, you can set the width of the where parameter to be slightly greater than that number. Otherwise, set it to some number like 100.

       If your lexical parameter (’where’) width is not enough to hold the where condition assigned to it, you will receive one of the following errors depending on your Reports version.

REP-0450 - Unhand led exception,
ORA-6502- PL/SQL numeric or value error.
REP-1401 - Fatal PL/SQL error in afterptrigger
ORA-6502-PL/SQL numeric or value error.

We Will build report base on following SQL statement.


1.     Create data Model
2.     Set Parameter width 
3.     Create Paper Layout  

1.     Create data Model Datamedel icon

Create SQL statement report builder sql

lexical para query

Click on OK

2.     Set Parameter width

lexical para query

3.     Create Paper Layout 

·        Create main frame
·        Create repeating frame inside of main frame 
·        Create Labels in main frame
·        Create Fields in repeating frames

Main frame properties setups:
Vertical Elasticity: Expand
Horizontal Elasticity: Expand

Repeating frame properties setups:
Source: G_EMPNO
Vertical Elasticity: Expand
Horizontal Elasticity: Expand

Field properties setups:
Source: EMPNO
Vertical Elasticity: Expand
Horizontal Elasticity: Expand

Note: Repeat same steps for remain fields  

Share this article :


  1. Thank you Very much for helping ...

  2. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  3. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.