Double Dynamic Apex

After my last Apex post i'm now on a roll and have coded what is perhaps the most fiddly block of plsql ever attempted to create a data source for a page......

So what the hell was i doing you ask - well let me tell you about the screen i was trying to create so you can see why this got so fiddly.

The application i'm trying to replicate has a screen where you can choose a database name - once selected a list is provided below of all the queue tables in that remote database (which on it's own isn't that tricky) but with the added twist of showing the current count of records in that table.....

So the logic i need to produce is something like:

Select DB -> produce a list of queue tables that exist in the referenced db -> for each queue table produce a count

This is far from trivial.....

The bottom half of the Apex screen has to build an initial dynamic query based on the value selected in the top half of the screen - the values returned from this query then need to be used to build a second dynamic query that actually fetches the counts from the table - the apex screen then needs to show the results of executing that dynamic-dynamic query (from now on knows as a double D query.....)

So how to go about this?

I did some quite hard thinking and managed to come up with a method to do it in a single block of pl/sql (well relying on some types i had to create in the database first) that i could just paste in the source of a "plsql function body returning query" statement.

First let me show you what the end result screen looks like as it's easier then to try and link what I'm saying to the end result

So in the screen shot above you can see i have a simple select list at the top which is derived from a list of the current database links in the apex schema.

When i choose a db name (sorry all blurred out here) an initial dynamic query is built to fetch the owner and queue table(shown as stage 1 above) that exist by querying over the database link to build that list.

So the initial dynamic query is something like (v_sql in the actual code below)

select owner,queue_table from all_mviews@DB_NAME_I_CHOSE_ON THE_TOP_OF_SCREEN

From the list returned and again re-using the database link from the top of the screen we then combine all those bits of information in stage 2 to build a statement that queries each of the queue tables in turn (using union all) to give the SQL that the page will run to populate the data grid.

The secondary dynamic query is then something like (v_runthis in the actual code below)

select 'owner from dynamic 1 row 1','queue table from dynamic 1 row 1', count(*) 
from owner_from_dynamic1_row1.queue_table_from_dynamic1_row1@DB_NAME_I_CHOSE_ON THE_TOP_OF_SCREEN
union all
select 'owner from dynamic 1 row 2','queue table from dynamic 1 row 2', count(*) 
from owner_from_dynamic1_row2.queue_table_from_dynamic1_row2@DB_NAME_I_CHOSE_ON THE_TOP_OF_SCREEN
etc
etc

up to number of rows from results of dynamic query 1

Still with me....?

So to build this query magically up that is totally dynamic (so if i add a new db in the drop down it can cope with querying anything at all from it) i need to add 2 types into the apex schema as follows:

create or replace type queue_details as object (
   vname varchar2(100),
   vowner varchar2(100),
   vqueue varchar2(100)
)
/


create or replace type t_queue_details as table of queue_details
/

This is to allow me to use them in the plsql in the apex screen

And the plsql ends up looking like this (included in pic and text form) as the pic is easier to read (but harder to cut and paste.......)

declare 
  v_sql varchar2(4000);
  v_qrows  t_queue_details ;
v_runthis varchar2(4000);
begin 

v_sql := 'select queue_details(name, owner, queue_table) 
            from   dba_queues@'||:QDB_SELECTOR||'
            where  owner not in ('||''''||'SYS'||''''||','||''''||'SYSTEM'||''''||') and queue_type='||''''||'NORMAL_QUEUE'||'''';
  APEX_DEBUG_MESSAGE.LOG_MESSAGE(v_sql,p_level=>1);
  
execute immediate v_sql bulk collect into v_qrows;

FOR indx IN 1 .. v_qrows.count
     LOOP
     IF indx = v_qrows.count
     then
     v_runthis := v_runthis ||' select '||''''||v_qrows(indx).vowner||''''||','||''''||v_qrows(indx).vname||''''||',count(*) from '||v_qrows(indx).vowner||'.'|| v_qrows(indx).vqueue||'@'||:QDB_SELECTOR;-- v_qrows(indx).vqueue;
     else
v_runthis := v_runthis ||' select '||''''||v_qrows(indx).vowner||''''||','||''''||v_qrows(indx).vname||''''||',count(*) from '||v_qrows(indx).vowner||'.'|| v_qrows(indx).vqueue||'@'||:QDB_SELECTOR||' union all';-- v_qrows(indx).vqueue;
     end if;
     END LOOP;
  
  APEX_DEBUG_MESSAGE.LOG_MESSAGE(v_runthis,p_level=>1);
  return v_runthis;
end;
     
Not the prettiest code in the world and the amount of single quotes is enough to make you cry but it works and is actually quite neat (well i would say that of course)

The amount of times you'd need to do something like this are pretty rare i guess but it's nice to know that Apex can actually do this relatively easily with a little bit of effort.......

With a slight amendment you could get the same screen to connect to a remote database and produce a real time count from every table that exists there - again debatable if that's actually of any use but it gives you an idea of what this could do.

It's solved my problem anyway and i'm currently very smug (until someone tells me there is an easier way or i have a fatal flaw in what I've built......)