Rowset calculations in PL/SQL

Hi,

I would like to know if (and how) the following problem can be solved in PL/SQL (using Oracle 9i and above):

For a data warehouse we need the following functionality: Depending on a drilldown-dimension (previously selected by the user) a SQL-statement has to be generated dynamically. For the resultset, a summary row has to be computed; both has to be available as an OUT Ref-Cursor of some function. This function could for example have the following signature:

function drilldown(p_dimension      varchar2,
                    p_result     out sys_refcursor,
                    p_summaryrow out sys_refcursor) return number

The function has to be able to be called from different frontends like NET-Clients or JAVA-Webapplications.

The following example shows how a resultset can principally look like:

division employees sales

division a            20     100,000.00
division b            15      70,000.00
division c             8      30,000.00

The computation of each column-summary (count, sum, avg, min, ...) is given by a metadata repository and depends on the column name or the column alias respectively.

The summary row might be computed as count, sum, sum:

3                     43      200,000.00

Is this somehow possible in PL/SQL?

Because of the possibly long runtimes of the queries, I have to avoid in any case that the statement runs twice.

Thus the following solution is not desirable:

[...]

• dynamically generated SQL-Statement l_query := 'SELECT division, employees, sales FROM table'; OPEN p_result FOR l_query;
• dynamically generated SQL-Statement for the summary row l_query_sum := 'SELECT COUNT(*), SUM(employees), SUM(sales)' ||' FROM table'; OPEN p_summaryrow FOR l_query_sum;

[...]

Can anybody help?

Thank you in advance,
Sven Received on Thu Jan 25 2007 - 15:46:04 CST