An Oracle blog about BI Publisher

Its a bouncing baby extension ...

A few weeks back I documented how to build extensions to publisher if you wanted some functionality that we did not support. Frank Menne from HSM consulting in Germany has jumped on it and built a sql extension. the extension allows you to go back to the database from the layout template layer. Its simple but it works, you are able to pass select staement values and fetch data back - if you are an Oracle Reports developer, think of Formula columns.

Now a word to wise, we do not condone doing this, its bad, so very very bad :0) - if you are going to make a trip back for every line of a report its going to hurt just like formula columns can hurt in an Oracle Report. But its a valid requirement in some cases; I have to admit I did not push Frank to see if he could have gotten the data up front, I was just pleased to see he was trying to build an extension. If you can get that data in your extract please do, its going to be worth the pain of redesigning your query(s) - your report is going to run that much faster.

So, please build extensions and if you are going to and it either, does not work as expected or you get performance problems - DO NOT LOG AN SR! Oracle Support, will quite rightly, not be interested - this really is a customization and you will be out of your support contract.

OK, warnings out of the way, lets share what Frank has been doing on his baaad trips to the database. He created the following class:

package oracle.bip.extensions;

import java.util.Hashtable;
import java.lang.reflect.Method;
import oracle.apps.xdo.template.rtf.util.XDOBarcodeEncoder;
import oracle.apps.xdo.common.log.Logger;
import oracle.apps.fnd.common.*;
import java.util.*;
import java.sql.*;
import oracle.jdbc.*;
import java.lang.reflect.Constructor;

public class sql {
   public sql() {}
   public static  String ConnectString="";

 public static final String SetConnectString (String name)
   ConnectString = name;
   return "";

 public static final String ExecSql (String s1, String s2, String s3 )
   String resultstring;
   String completesql=s1+s2+s3;
   Properties p = new Properties(System.getProperties());
  Connection aConn;
  if (ConnectString != "")
     aConn = DriverManager.getConnection(ConnectString);
  {AppsContext aCont = new AppsContext(System.getProperty("DBCFILE"));
   aConn  = aCont.getJDBCConnection();
  OraclePreparedStatement ops;
  ops = (OraclePreparedStatement)aConn.prepareStatement(s1+s2+s3);
  ops.defineColumnType(1, 2);
  ResultSet rs = ops.executeQuery();
  catch(Exception exception) { resultstring="exception"+exception.toString()+", SQL Command: "+completesql; }



Pretty straightforward stuff but you can see the potential. Those of you wondering why Frank has a SetConnectString method which seems superfluous in the ExecSQL method - if you are running inside the template builder you can not, without a lot of effort, if at all grab an Apps session hence the method to set the db connection manually.

With the class in the classpath he could then register the extension in the template


 Remember the 'oracle.bip.extensions' is from the package in class and the 'sql' is the class. Then use it.

<?sql: ExecSql('select ', ' @PICK_SLIP_NUMBER ' ,'from dual ') ?>

There was an issue that Frank hit, he wanted to use the above SQL as a test but in its current format there was a problem, the '@PICK_SLIP_NUMBER' is not evaluated by the XSL engine, its just passed as a string to the extension so the extension assembles the select statement as:

select  @PICK_SLIP_NUMBER from dual

to get the value resolved we need

<?sql: ExecSql('select ', @PICK_SLIP_NUMBER  ,'from dual ') ?>
ie select 111111 from dual - assuming the value of PICK_SLIP_NUMBER is '111111'

Now this has some issues if you actually want the sql to select a string ie select '111111' from dual.

To achieve this you either need to modify you extension to add the relevant quote marks or change the way its passed the values:

<?sql: ExecSql(concat(concat('select ?, ?&apos;?,@PICK_SLIP_NUMBER,?&apos;?, from dual ') ?>

its a little messy because we have to escape the single quotes as &apos;
But that way the quotes are inserted into the sql string and the attribute gets evaluated correctly. This also breaks the current extension but has an advantage in simplifying it, you only need a single parameter passed to the extension - the select statement is built in the template layer.

Another word to the wiser, if you are going to implement this - please think about the terrors of sql injection. Here, we are expecting a 'select' statment but you could easily abuse this and use inserts and updates. The extension is simple but needs some parsing to ensure its not being used for nefarious means.

So, there you have it, our first customer extension - if we get enough interest I'll post this and others to a section on the blog ... get extending people and share them!

Join the discussion

Comments ( 2 )
  • Glen Wednesday, August 5, 2009
    Would this type of extension work with an EBS install of XMLP? Or is this limited to just stand alone BI Publisher installs? All caveats noted, of course, just wondering if it should work with an R12 apps instance.
  • tony montana Sunday, June 27, 2010
    I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.