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

Pages

Monday, 13 February 2017

Create Report with Bind Parameter

            Bind references are used to replace a single value in SQL or PL/SQL. 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.

Syntax:
SELECT Col1,Col2 FROM XX_Table WHERE Col1 = :P_col1;

Bind parameters are used to substitute single value at runtime for evaluation and are identified by a preceding ‘:’

An example of a bind parameter in a select statement is provided below,where :P_Name is the bind parameter reference.

     Select Name,Value From XX_Table Where Name= :P_Name


These are used as tokens while registering concurrent program (In Oracle Applications)

We Will build report base on following SQL statement.

SELECT EMPNO,ENAME,JOB,DEPTNO  FROM EMP WHERE DEPTNO=:P_DEPTNO



1.     Create data Model
2.     Set Parameter Data types
3.     Create Paper Layout  


1.     Create data Model Datamedel icon


Create SQL statement report builder sql


Click on OK

2.     Set Parameter Data types



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  

Note: Repeat same steps for remain fields



Share this article :

0 comments:

Post a Comment