How Create a report with Lexical Parameter in oracle Reports

Lexical parameter is used to replace a specific ‘String’ in sql statement at runtime.

• It can be created with ‘&’ at runtime.
• String to  replace can be handled in ‘After Parameter Form’trigger.
• Used in SQL only.
• Used in all Clauses.

In this example we are going to retrieve the employees who hired in certain period of time i.e.
Step 1: Open Reports builder and connect the database, go to the data model of a new report and write a query as bellow

Press OK, system will show that ‘Query has created the lexical parameter’

Step 2: Now create report using this query. To do this right click on ‘Q_1’ and select ‘Report Wizard’ shown below









Step 3: Provide the dynamic parameter value as bellow


you can also use WHERE hirdate > ’01-Jan-2012′ OR WHERE hirdate = ’01-Jan-2012′ WHERE hirdate BETWEEN ’01-Jan-2012′ AND ’01-Jan-2013′

About these ads

Lexical parameter

These are used to replace a value dynamically at run time. Lexical parameters can dynamically replace clauses in the Select statement in the data model and even the whole select statement. It replaces any part of a SELECT statement, such as column names, the FROM clause, the WHERE clause, the ORDER BY clause. 

To create a lexical reference in a query, prefix the parameter name with an ampersand (&)

Lexical parameter has a PREFIX ampersand (&)

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.

SELECT Col1,Col2

FROM &ATABLE

select empno,ename from emp &X 

At run time we can pass the entire where condition like 
where deptno=30 and sal>3000 etc....    
where sal>6000












  parameter

We can create lexical parameter & bind parameter in the report in the data model query. The bind parameter is used as well and lexical parameter used for ex:

Bind parameter has a PREFIX colon (:)

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.

SELECT ename,deptno FROM emp WHERE empno=:empno

Difference between Bind and Lexical parameter?

Bind Parameter	Lexical Parameter
Bind parameter are used to pass a single value in sql, pl/sql	lexical parameter may be used to replace expressions in select, where, group, order by, having, connect by, start with cause of queries.
It is started with Colon (:)	It  is started with Amparsign (&)
It is used to accept the value when executing the query	It is used to build report queries dynamically
It is used in (select, where, order by group by ) but not in from.	It is used in all clause.
select * from emp where emp_no=:emp_no;	select * from emp where &condition;
It is used both sql & pl/sql.	It is used only in sql.

Both bind and lexical 

SELECT &deptno,ename,deptno FROM emp WHERE empno=:empno