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.