X

An Oracle blog about BI Publisher

BIP 11g Dynamic SQL

Back in the 10g release, if you wanted something beyond the standard query for your report extract; you needed to break out your favorite text editor. You gotta love 'vi' and hate emacs, am I right? And get to building a data template, they were/are lovely to write, such fun ... not! Its not fun writing them by hand but, you do get to do some cool stuff around the data extract including dynamic SQL. By that I mean the ability to add content dynamically to your your query at runtime.

With 11g, we spoiled you with a visual builder, no more vi or notepad sessions, a friendly drag and drop interface allowing you to build hierarchical data sets, calculated columns, summary columns, etc. You can still create the dynamic SQL statements, its not so well documented right now, in lieu of doc updates here's the skinny.

If you check out the 10g process to create dynamic sql in the docs. You need to create a data trigger function where you assign the dynamic sql to a global variable that's matched in your report SQL. In 11g, the process is really the same, BI Publisher just provides a bit more help to define what trigger code needs to be called. You still need to create the function and place it inside a package in the db.

Here's a simple plsql package with the 'beforedata' function trigger.

Spec

create or replace PACKAGE BIREPORTS AS 
whereCols varchar2(2000);
FUNCTION beforeReportTrig return boolean;
end BIREPORTS;

Body

create or replace PACKAGE BODY BIREPORTS AS
  FUNCTION beforeReportTrig return boolean AS
  BEGIN
      whereCols := ' and d.department_id = 100';
    RETURN true;
  END beforeReportTrig;
END BIREPORTS;

you'll notice the additional where clause (whereCols - declared as a public variable) is hard coded. I'll cover parameterizing that in my next post. If you can not wait, check the 10g docs for an example.

I have my package compiling successfully in the db. Now, onto the BIP data model definition.

1. Create a new data model and go ahead and create your query(s) as you would normally.

2. In the query dialog box, add in the variables you want replaced at runtime using an ampersand rather than a colon e.g. &whereCols.

select     d.DEPARTMENT_NAME,
...
 from    "OE"."EMPLOYEES" e,
    "OE"."DEPARTMENTS" d
 where   d."DEPARTMENT_ID"= e."DEPARTMENT_ID"
&whereCols

Note that 'whereCols' matches the global variable name in our package. When you click OK to clear the dialog, you'll be asked for a default value for the variable, just use ' and 1=1' That leading space is important to keep the SQL valid ie required whitespace. This value will be used for the where clause if case its not set by the function code.

3. Now click on the Event Triggers tree node and create a new trigger of the type Before Data. Type in the default package name, in my example, 'BIREPORTS'. Then hit the update button to get BIP to fetch the valid functions.
In my case I get to see the following:


Select the BEFOREREPORTTRIG function (or your name) and shuttle it across.

4. Save your data model and now test it. For now, you can update the where clause via the plsql package.

Next time ... parametrizing the dynamic clause.

Join the discussion

Comments ( 3 )
  • guest Saturday, September 6, 2014

    Hi, when I tried this with proxy authentication switched on the function wasn't available to select. The function is in the same database schema as the connection.


  • guest Wednesday, October 29, 2014

    Just found this BLOG and am very thankful and impressed so far.

    I wish to parameterize the where clause ( SQL is dynamically built in Java OAF application on a search Page, which then generates the result set to operate against.. and now I want to do a Bi Publisher output with a Chart and summarization against the result set .... )

    In this BLOG posting, you ended with the hook....

    Next time ... parametrizing the dynamic clause.

    ... but I can not find a blog post where anyone completed posting of this topic? Any chance of adding this? thanks.


  • kkirk Wednesday, October 29, 2014

    The Blog entry ended with the hook

    Next time ... parametrizing the dynamic clause.

    and am waiting with baited breath.. it is something

    that I need to implement in our production system... any

    change of adding a post completing the topic?

    TIA


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.