How do you call a SQL Server Stored Procedure in B

Question

I am trying to call a Microsoft SQL Server Stored Procedure that delivers data in table like format (rows / columns) in Oracle BI Publisher 11g (11.1.1.7).

Selecting procedure call as a data source for the data model does not work because BIP expects it to behave like a PL/SQL call to an Oracle database instead.

Oracle developers claim this is not supported by the software.

Is there any way around this restriction?

Answer 1

Although not supported out-of-the-box by BI Publisher 11g, there is a workaround to the problem. It involves tricking the software into thinking it is making a standard PL/SQL call when in fact in reality it is executing a stored procedure on the SQL Server datasource.

1) Make sure you have the native MS SQL server library installed on your weblogic server running the BIP instance.

It can be downloaded from MSDN here: http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx - depending on your JRE version you'll want to use one or the other jar file:

For JRE 1.6 and above, use sqljdbc4.jar. For 1.5 and below, use sqljdbc.jar.

You should place this in your $MIDDLEWARE_HOME\user_projects\domains\$your_domain_here$\lib\ folder and remember to restart weblogic server afterwards.

2) Inside BI Publisher administration, create a new JDBC datasource.

Our example works with following properties:

Driver Type: Microsoft SQL Server 2008
Database Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection String: jdbc:weblogic:sqlserver://[hostname]:[port];databaseName=[database name]

Fill in username/pw and test connection (if driver is installed correctly, this should work just fine)

3) Create a new datamodel.

Choose SQL query as your dataset. Here, add in these properties:

Data Source: your JDBC data source
Type of SQL: Non-standard SQL
Row Tag Name: (choose one yourself) - for now just write test.

4) Under SQL query, we now need to convince BIP that it is calling an Oracle SP when in fact it is calling an existing stored procedure on your MS SQL datasource.

This part is assuming your stored procedure delivers N amount of rows and column labels over.

Here is how we solved it for our SP that is called nrdart_get_custody_holding_headers_sp '2014-11-25' where the parameter is a date supplied by the user.

declare @var1 datetime  
declare @sql varchar(255)  
set @var1 = '2014-11-25'  
set @sql = 'nrdart_get_custody_holding_headers_sp' +'''' + cast(@var1 as varchar) + ''''  
exec (@sql)  

Here, we are declaring some SQL Server datatypes, and setting them as our date parameter and as our procedure call name using some creative use of the cast function and escape characters, before finally calling exec on the stored procedure.

Parameter var1 will also work if you use a standard BIP parameter instead of our hard-coded example above

i.e. :userDate where :userDate is referring to an existing parameter called userDate in the datamodel.

Don't worry if you don't see row/column labels after clicking OK. Instead, click on "view data" and there you go. Rows and columns with data from your SP on Microsoft SQL Server. Now proceed to save this as sample data and design report layout as you would normally do. For non-date parameters you might need to play around a little bit with datatypes, but I don't see why you shouldn't get it to work with integers or varchars as well.