Follow us: Connect on YouTube 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 :

2 comments:

  1. Greetings Mate,


    This is indeed great! But I think perhaps you are generally referring #topic which is getting unsustainable.


    I need some help from you experts on this stored procedure..

    There are 4-5 batch procedures that will call this stored procedure. The batch will call the stored procedure with 'I' parameter in the beginning of the batch process. The batch will call the stored procedure with 'O' parameter at the end of the batch process. The stored procedure should write the details into the auditing table.






    But great job man, do keep posted with the new updates.


    Shukran,

    ReplyDelete
  2. Hi Dayakar,

    I’ve often thought about this Create Report with Bind Parameter . Nice to have it laid out so clearly. Great eye opener.


    There are 3 big files, 1GB (file_a.txt), 10GB (file_b.txt) and 1TB (file_c.txt);
    - The format of these 3 files: each line with a random string in the file;
    - There is only 100MB memory could be used, disk usage is not limited;
    -
    Assumption:
    IF AND ONLY IF string A appears within all 3 files, we need to count the total appearing times of this A. Such as, A appears 2 times within file_a.txt, appears 10 times within file_b.txt, appears 100 times within file_c.txt, then we count the total appearing times of A as 2 + 10 + 100 = 112 times.

    Question: please write a program to output the strings with TOP 10 and LAST 10 appearing times in descending order.







    Super likes !!! for this amazing post. I thinks everyone should bookmark this.


    Many Thanks,
    Ajeeth

    ReplyDelete