Currently we have the BOXI 3.1.8 Universe in oracle database and we are planning to migrate to SQL server 2008.And the universe contains few oracle functions and user defined functions in the universe objects.
In the way of migration we are planning to have both Oracle and SQL Server database connecting to single universe. To make the universe generic we have identified the objects using user defined functions and trying to push it to the database.
Since there are a few differences in the way oracle and SQL Server functions are invoked, we have decided to edit the .PRM files available in the path D:\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\jdbc , so that we have a common way of invoking the functions.
Invoking functions:
SQL Server : .
Oracle:
1.Is there any problem if I edit the .PRM?
2.Whether these changes will work fine?
3.Whether the reports will run fine if I use those objects in the reports?
Any custom functions built on the database do not have to be included in the .prm files in order to work. As long as the SQL is correct the object will parse and function fine.
As far as I know, the .prm files were only used to provide help to the developer (showing syntax) and also to support user defined objects (which are now obsolete as Deski has been retired). So you don’t have to enter any custom functions in these files to be able to use them in object definitions, it’s just a convenience.
Thanks for your reply Dave.I agree with your point.
We have database functions applied on the @prompt values, for validation. Now we are trying to push these functions to database, so that we have a generic universe, which can be used with both SQL Server and Oracle.
To reiterate, the issue is that, we cannot have the function calls in the definition of @prompt. We have it as fn(@prompt) in oracle, but with sql server we need to use something like dbo.fn(@prompt).
We have tried creating a package as dbo in oracle and include the functions in it. This works for user defined functions, but not for system functions like to_date.
So we are looking at a solution which will enable us to either use the functions in the prompt in a way it works for both SQL server and Oracle or to push the validation to database.
(BOB member since 2002-06-06)