How to get result table from Region Source SQL Select Statement in Plugin SQL Source Code?

Question

I am currently developing an APEX Plug-In for Region. It executes some JavaScript code with SELECT result transformed into string. But for testing reasons the SELECT query is temporarily written into Source PL/SQL Code of the Plug-in.

The source code looks something like this:

FUNCTION f_render (p_region in apex_plugin.t_region, p_plugin in apex_plugin.t_plugin)
RETURN apex_plugin.t_region_render_result IS 
  v_js VARCHAR(512);
  v_data VARCHAR(1024);
BEGIN
  SELECT '[' || c_matrix || ']' INTO v_data FROM (
    SELECT listagg(c_row, ',') WITHIN GROUP (ORDER BY c_row) AS c_matrix FROM (
      -- next line should be re-written 
      -- for compatibility with any possible origin or target string 
      -- values and numbers, but it's another question
      SELECT '[' || "'a'" || ',' || "'b'" || ']' AS c_row FROM ( 
        -- actual test table query that should be in p_region.source
        WITH t0 AS ( 
          SELECT 'a' origin, 'a' target, 11 amount FROM dual UNION ALL
          SELECT 'a', 'b', 21 FROM dual UNION ALL
          SELECT 'b', 'a', 12 FROM dual UNION ALL
          SELECT 'b', 'b', 22 FROM dual
        ), t1 AS (
          SELECT * FROM t0
          PIVOT ( sum(amount) for target in ('a','b'))
          ORDER BY origin
        )
        SELECT * FROM t1
      )
    )
  ); 
  v_js := q'[
    console.log("@DATASTRING@");
  ]';
  v_js := REPLACE(v_js, '@DATASTRING@', v_data);
  apex_javascript.add_onload_code(p_code => v_js, p_key => null);
  RETURN NULL;
END f_render; 

The "actual test table" SELECT returns this t1 table:

|ORIGIN|'a'|'b'|
|------|---|---|
| a    | 11| 21|
| b    | 12| 22|

console.log prints a string like this: [[11,21],[12,22]]. And, If I tweak some things, this plugin even returns this JS Array in browser console as... a JS Array, as it should.

These are required and correct results for now. But if I change "actual test table" SELECT to p_region.source and put this SELECT in Source: SQL Query in Page Designer, it doesn't work at all, and APEX doesn't want to save this plugin due to some errors.

p_region.source should be able to use any SELECT, even something simple like SELECT * FROM table_A, where table_A is a pivoted matrix similar to the "actual test table" t1.

THE QUESTION: How to correctly reference result of a query in p_region.source to make it all work? AFAIK, it could be done with APEX_PLUGIN_UTIL.GET_DATA or .GET_DATA2. But I don't know what it returns and how and where to test it. I'd like to play with it in something like dbfiddle to learn what it does.

PS: I'm a frontend junior, but my boss has given me this task regardless of my qualification. Also Oracle Documentation is not very helpful in this situation.

Answer 1

First of all, what will help you to develop in general. When you are developing a Region Plug-in, there are two ways to see debug info: usual data logging into a table or output directly on the page. For the first way (logging into a table), you need to create a table (with any structure you need) and insert lines there from your PL/SQL code. The second way uses htp.p procedure to output data directly in the web page. For example, if you put the following code into your render plug-in function:

select some_value
  into v_variable
  from table1
 where ... ;

htp.p('Variable contains: ' || v_variable || '<br />');

This code will create something like:

<div> <!-- open and close div tags are generated by APEX engine -->
  Variable contains: 123<br />
</div>

You need to create a page, create a region with your plug-in type on it, and then open this page in the browser. After that you will see the output.

Next, how to process SQL queries. APEX_PLUGIN_UTIL.GET_DATA returns a value with type apex_plugin_util.t_column_value_list, which is simply a collection of collections of strings. The main reason why you have to use this function is that it can also bind any bind variables in the code of SQL. For example, if you have a page item P1_VALUE and want to use its value in the query, you can put in region's source:

select *
  from table
 where column1 = :P1_VALUE

APEX_PLUGIN_UTIL.GET_DATA will automatically bind an item's value here.

How to process the result. The following code will output a simple html table:

  ...
  v_data apex_plugin_util.t_column_value_list;
begin
  ...
  v_data := apex_plugin_util.get_data(p_region.source, 1, 10, p_region.name);

  htp.p('<table><tbody>');

  for i in v_data.first .. v_data.last loop
    htp.p('<tr>');

    for j in v_data(i).first .. v_data(i).last loop
      -- your code to process data
      htp.p('<td>' || v_data(i)(j) || '</td>');
    end loop;

    htp.p('</tr>');
  end loop;
  htp.p('</tbody></table>');
  ...
end;

Note, that this function returns (for some reason) a transposed table. So, if you have a table with two columns and three rows:

COL1  COL2
----------
abc    123
def    456
ghi    789

The output of the code above will be:

<table>
  <tbody>
    <tr>
      <td>abc</td><td>def</td><td>ghi</td>
    </tr>  
    <tr>
      <td>123</td><td>456</td><td>789</td>
    </tr>
  </tbody>
</table>