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),
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’));
SQL> select count(*) from table(chart_pkg.generate(‘D’));
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.