Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

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.

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 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:
Name: EMPNO
Source: EMPNO
Vertical Elasticity: Expand
Horizontal Elasticity: Expand

Note: Repeat same steps for remain fields  


Share this article :

2 comments: