Create Oracle Report with Lexical Parameter

Sunday, 12 February 2017

Create Oracle Report with Lexical Parameter

Posted by Dayakar at 02:35 Labels: ORACLE REPORTS

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.

Syntax:

SELECT Col1,Col2 FROM &ATABLE

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.

SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP &P_WHERE

1. Create data Model

2. Set Parameter width

3. Create Paper Layout

1. Create data Model