I have always been trying to call aMicrosoft SQL Server Stored Procedurethat delivers information in desk like file format (rows / columns) inOracle BI Author 11g(11.1.1.7).
Next, we decided to calculate the activity coefficient of each data point, and then regress a multi-parameter BIP from the activity coefficients. The results are shown below: CHEMCAD now fits the experimental data quite well! We are now able to show the effects of having both an upper and a lower critical solution temperature.
Choosing procedure contact as a data source for the data model will not work because BIP wants it to behave like a PL/SQL call to an Oracle data source instead.
Oracle developers claim this is definitely not supported by the software program.
Is certainly there any way around this restriction?
Petteri PertolaPetteri Pertola
1562 platinum badges4 gold badges18 bronze badges
1 Solution
Although not supported out-of-the-box by BI Author 11g, there is a workaround to the issue. It entails tricking the software program into thinking it can be making a regular PL/SQL contact when in truth in reality it is running a saved process on the SQL Server datasource.
1) Create sure you have got the indigenous MS SQL server library installed on your weblogic machine operating the BIP example.
It can be downloaded from MSDN here: http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx - depending on your JRE edition you'll want to use one or the various other jar file:
For JRE 1.6 and over, make use of sqljdbc4.jar. For 1.5 and below, use sqljdbc.container.
You should spot this in your $MIDDLEWAREHOMEuserprojectsdomains$yourdomainhere$lib folder and remember to restart weblogic machine soon after.
2) Inside BI Publisher administration, create a fresh JDBC datasource.
Our instance works with using attributes:
Car owner Type:Microsoft SQL Server 2008
Database Driver Class:com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection Line:jdbc:weblogic:sqlserver://hostname:slot;databaseName=data source title
Database Driver Class:com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection Line:jdbc:weblogic:sqlserver://hostname:slot;databaseName=data source title
Fill in username/pw and test link (if driver is installed correctly, this should function just great)
3) Make a fresh datamodel.
Choose SQL concern as your dataset. Right here, add in these qualities:
Data Supply:your JDBC information resource
Kind of SQL:Non-standard SQL
Line Tag Title:(choose one yourself) - for right now just write test.
Kind of SQL:Non-standard SQL
Line Tag Title:(choose one yourself) - for right now just write test.
4) Under SQL question, we today need to convince BIP that it is definitely phoning an Oracle SP when in truth it is definitely calling an present stored treatment on your Master of science SQL datasource.
This component is presuming your stored procedure delivers N amount of rows and column labels over.
Right here is definitely how we resolved it for our SP that can be called nrdartgetcustodyholdingheaderssp '2014-11-25' where the parameter is usually a date supplied by the consumer.
Here, we are declaring some SQL Machine datatypes, and setting up them as our day parameter and as our method call title making use of some innovative make use of of the ensemble function and escape heroes, before finally calling exec on the saved method.
Parameter var1 will also work if you make use of a regular BIP parameter instead of our hard-coded instance above
i.e. :userDate where :userDate is usually referring to an present parameter known as userDate in the datamodel.
Put on't worry if you put on't discover line/column brands after clicking Okay. Instead, click on 'look at data' and there you go. Rows and columns with data from your SP on Microsoft SQL Server. Now continue to conserve this as test data and design report layout as you would normally perform. For non-date variables you might need to enjoy around a little little bit with datatypes, but I don't see why you shouldn't obtain it to function with integers or varchars simply because properly.
Petteri PertolaPetteri Pertola
1562 money badges4 sterling silver badges18 bronze badges
Got a query that you can't inquire on open public Stack Overflow? Learn even more about discussing private info with Collection Overflow for Teams.