Johns Blog

An interesting post popped up on the Application Express forums where a user wanted to dynamically change the query that a chart used.

The comment that caught my eye was -

How about if I want to change the table and where clause as well.

Whilst it’s simple to dynamically change the where clause, it’s not so straightforward to change the table that you’re querying dynamically, since the chart relies on you specifying the query at designtime.

Fortunately, we know that the chart needs to have certain outputs from the query, for example -

SELECT
  null as link, ename as label,  sal as value
FROM
  scott.emp
WHERE
  deptno = :P101_DEPTNO

So we need a method of returning a link column, a label column and a value column dynamically for each row of data.
Fortunately Oracle has the concept of Pipelined functions which should help us to achieve our aim.

First we need to create a type which will be used as the return type of our pipelined function, i.e. this type will represent a single point on our chart

create or replace type ty_chart_entry as object (
  link varchar2(60),
  label varchar2(60),
  value number
)

Create another type which is a collection of the ty_chart_entry type, i.e. this will effectively hold the table representing all the data points on the chart.

create or replace type tbl_chart_entry as table of ty_chart_entry

Now we need to create the function itself

create or replace package chart_pkg
as
    function generate(p_type in varchar2) return tbl_chart_entry PIPELINED;
end;

create or replace package body chart_pkg as
  function generate(p_type in varchar2) return tbl_chart_entry PIPELINED is
  begin
    if (p_type = 'E') then
      for rec in (select ename, sal from emp) loop
        pipe row (ty_chart_entry(null, rec.ename, rec.sal));
      end loop;
    end if;
    if (p_type = 'D') then
      for rec in (select d.dname as name,
                  (select sum(e.sal) from emp e where e.deptno = d.deptno) as sal
                  from dept d) loop
        pipe row (ty_chart_entry(null, rec.name, rec.sal));
      end loop;
    end if;
    return;
  end;
end;

Essentially this function performs two entirely different queries depending on whether we pass in a ‘D’ (to query the DEPT table) or an ‘E’ (to query the EMP table).

SQL> select count(*) from table(chart_pkg.generate(’E'));

COUNT(*)
———-
14

SQL> select count(*) from table(chart_pkg.generate(’D'));

COUNT(*)
———-
4

So we can now create a region on a page with a select list (with submit) that returns ‘E’ or ‘D’ and use this value in a chart region.

Which nows gives us a completely dynamic chart on the EMP and DEPT tables

PIPELINED functions are pretty cool, and very very underused. Note though that I’m not sure that I would advocate heavy usage of them like this since I’m sure there’s probably more optimal ways of doing this, however they’re extremely flexible and worth checking out if you hit a similar sort of problem.