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());
    try
  {
  Connection aConn;
  if (ConnectString != "")
  {
     Class.forName("oracle.jdbc.driver.OracleDriver");
     aConn = DriverManager.getConnection(ConnectString);
  }
  else
  {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();
  rs.next();
  resultstring=rs.getString(1);
  }
  catch(Exception exception) { resultstring="exception"+exception.toString()+", SQL Command: "+completesql; }

  return(resultstring);

}
}


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


<?namespace:bipext=http://www.oracle.com/XSL/Transform/java/oracle.bip.extensions.sql?>


 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!


Comments:

Tim, 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. Thanks, Glen

Posted by Glen on August 05, 2009 at 07:53 AM MDT #

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

Posted by tony montana on June 27, 2010 at 10:20 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today