Background to this project
The client (a UK city council) had a substantial array of bespoke reports, designed to support Carefirst use, written in SQL, pl/SQL and Oracle 10G Reports. Most of the SQL and pl/SQL was in the form of database packages and procedures. It was the client´s strategy to move reporting from legacy Oracle reporting products to Business Objects XI.
The Aim
To enable the move to BOXi whilst preserving the business logic in the Oracle database, thereby making best use of the technology available and protecting client´s existing investment.
Technical Architecture
The problem was to enable BOXi to call stored database procedures and read the results from a session table. BOXi has a limited SQL interface so everything had to be invoked within the format of a standard SQL SELECT expression. We were able to use a combination of database functions (callable via a select), DBMS_SQL (to pass procedure names and parameters) and Oracle pipes (to transfer the results back to BOXi), to call existing procedures and gather the results for display in BOXi. Via application launchers in Carefirst we were then able to make these directly callable from CF menus.