l_ajax_id gets invalid characters from apex_plugin.get_ajax_identifier

[darshanputtaswamy]

After migrating our apex, the plugin was broken upon inspection, I found the l_ajax_id contains invalid characters

We need to change

  l_ajax_id                      varchar(2000) :=  replace(apex_plugin.get_ajax_identifier, '-', '_');
  l_data_name                    varchar2(2000) := 'pivot_data_' || l_ajax_id; 

to

  l_ajax_id                      varchar(2000) := replace(replace(apex_plugin.get_ajax_identifier, '-', '_'),'~~/','_');
  l_data_name                    varchar2(2000) := 'pivot_data_' || l_ajax_id; 

After making the above change it started working properly

Regards Darshan

[mortenbra]

Thanks for reporting this. See also https://twitter.com/mortenbraten/status/1347864483444699136

To be fixed during the next refresh of the Pivot plugin.

[darshanputtaswamy]

Thank you !!

[Jefry-nolastname]

l_ajax_id varchar(2000) := replace(replace(apex_plugin.get_ajaxidentifier, '-', ''),'~~/','_'); Change the bold text with whatever the "Unexpected token" was. In this case, it was "~" p.s. the code located in plugin PLSQL source code

[mortenbra]

The fix to be added to the plugin is described here: https://github.com/mortenbra/apex-plugins/issues/8#issuecomment-862628040

[MikiRosemberg]

Hi Morten, the pivot table plugin is not working in apex 22, it just shows plain json as text, nothing shows in the inspection either. Please help! ;(

[mortenbra]

Preliminary notes after investigating on APEX 22.2:

The plugin uses the (undocumented and now broken) procedure apex_util.json_from_sql to generate the JSON contents that the pivot Javascript component needs. See this thread for more info:

https://community.oracle.com/tech/developers/discussion/4497338/bug-in-apex-22-1

After updating the code in the plugin with the supported and documented API, I received another error related to the l_ajax_id (which can now also contain slashes, it appears...). Easy enough to fix with a workaround, but needs a better (permanent) solution.

Still, having fixed the above 2 issues, I now get the error message "An error occurred rendering the PivotTable UI." when the component is rendered on the page. There is really no other error message, so hard to know where to go from here, but it's probably an easy fix for someone more versed in Javascript than me.

[Viorel28]

Hi Morten, I was wondering if you managed to solve the incompatibility with APEX 22. I saw your above comment but no new commit. Based only on your error message described above I suppose that it is triggered by "pivot.min.js". Probably this is caused by some changes to the JS/CSS part of APEX engine but I can not see an obvious reason just by looking at that file.

[mortenbra]

I have not investigated further. As mentioned in my last update, even if the plugin (PL/SQL) code is fixed, the JS code fails, and I'm not really "a Javascript guy" 🤷‍♂️

[Michael-Weinberger]

Hi Morten,

I think I fixed it.

The problem is a slight difference between the results from apex_util.json_from_sql and apex_json. You have to add an js object wrapper around the js array: p_clob => '{"row":'||apex_json.get_clob_output( p_free => true )||'}',

[MikiRosemberg]

Hi Morten,

I think I fixed it.

The problem is a slight difference between the results from apex_util.json_from_sql and apex_json. You have to add an js object wrapper around the js array: p_clob => '{"row":'||apex_json.get_clob_output( p_free => true )||'}',

Hi Michael, can you please post the complete line to be replaced? Sorry, i'm not a JS guy too XD

[MikiRosemberg]

Hi Morten, I think I fixed it. The problem is a slight difference between the results from apex_util.json_from_sql and apex_json. You have to add an js object wrapper around the js array: p_clob => '{"row":'||apex_json.get_clob_output( p_free => true )||'}',

Hi Michael, can you please post the complete line to be replaced? Sorry, i'm not a JS guy too XD

I get it! And it works!

[Marko-Goljak]

Hi Miki

can you explain in which source where the replacement should be done or better paste the image where you made the replacement

Tnx

[MikiRosemberg]

Hi Marko, simply paste the following code in the plugin (shared components / Plugins / Pivot Table / Source-PL/SQL Code)

function render_pivot (p_region in apex_plugin.t_region, p_plugin in apex_plugin.t_plugin, p_is_printer_friendly in boolean ) return apex_plugin.t_region_render_result as l_returnvalue apex_plugin.t_region_render_result;

l_element_name varchar2(2000) := p_region.attribute_01; l_columns varchar2(2000) := replace(upper(p_region.attribute_02), ',', '","'); l_rows varchar2(2000) := replace(upper(p_region.attribute_03), ',', '","'); l_enable_ui varchar2(2000) := p_region.attribute_04; l_renderer varchar2(2000) := p_region.attribute_05; l_enable_charts varchar2(2000) := p_region.attribute_06; l_enable_export varchar2(2000) := p_region.attribute_07;

--l_ajax_id varchar(2000) := replace(apex_plugin.get_ajaxidentifier, '-', ''); l_ajax_id varchar(2000) := lower(dbms_random.string('x', 32)); l_data_name varchar2(2000) := 'pivotdata' || l_ajax_id; l_options_name varchar2(2000) := 'pivotoptions' || l_ajax_id; l_renderers_name varchar2(2000) := 'pivotrenderers' || l_ajax_id; l_onload_code varchar2(32000); l_context apex_exec.t_context;

begin

apex_css.add_file ( p_name => 'pivot.min', p_directory => p_plugin.file_prefix);

-- note: this file is also found in /i/libraries/jquery-ui/1.10.4/ui/minified/ but is not included by default by APEX apex_javascript.add_library ( p_name => 'jquery.ui.sortable.min', p_directory => p_plugin.file_prefix);

apex_javascript.add_library ( p_name => 'pivot.min', p_directory => p_plugin.file_prefix);

if l_enable_charts = 'Y' then apex_javascript.add_library ( p_name => 'plotly-latest.min', p_directory => 'https://cdn.plot.ly/'); apex_javascript.add_library ( p_name => 'plotly_renderers.min', p_directory => p_plugin.file_prefix); end if;

if l_enable_export = 'Y' then apex_javascript.add_library ( p_name => 'export_renderers.min', p_directory => p_plugin.file_prefix); end if;

l_context := apex_exec.open_query_context(
     p_location   => apex_exec.c_location_local_db,
     p_sql_query  => p_region.source );
apex_json.initialize_clob_output;
apex_json.write_context( l_context );

htp.p('

Loading, please wait...

');

htp.p('');

if l_enable_ui = 'Y' then if (l_enable_export = 'Y') or (l_enable_charts = 'Y') then l_onload_code := 'var ' || l_renderers_name || ';'; if (l_enable_export = 'Y') then l_onload_code := l_onload_code || l_renderers_name || ' = $.extend($.pivotUtilities.renderers, $.pivotUtilities.export_renderers);'; end if; if l_enable_charts = 'Y' then l_onload_code := l_onload_code || l_renderers_name || ' = $.extend($.pivotUtilities.renderers, $.pivotUtilities.plotly_renderers);'; end if; l_onload_code := l_onload_code || 'var ' || l_options_name || ' = {cols: ["' || l_columns || '"], rows: ["' || l_rows || '"], renderers: ' || l_renderers_name || ', rendererName: "' || l_renderer || '"};'; else l_onload_code := 'var ' || l_options_name || ' = {cols: ["' || l_columns || '"], rows: ["' || l_rows || '"], rendererName: "' || l_renderer || '"};'; end if; l_onload_code := l_onload_code || '$("#' || l_element_name || '").pivotUI(' || l_data_name || '.row, ' || l_options_name || ');'; else l_onload_code := 'var ' || l_options_name || ' = {cols: ["' || l_columns || '"], rows: ["' || l_rows || '"]};'; l_onload_code := l_onload_code || '$("#' || l_element_name || '").pivot(' || l_data_name || '.row, ' || l_options_name || ');'; end if;

apex_javascript.add_onload_code (l_onload_code);

return l_returnvalue; apex_exec.close( l_context );

end render_pivot;

And that's all..

Hope it works for you, now i tested on apex 23.1 and it works too!

El dom, 11 jun 2023 a la(s) 03:50, Marko-Goljak @.***) escribió:

Hi Miki

can you explain in which source where the replacement should be done or better paste the image where you made the replacement

Tnx

— Reply to this email directly, view it on GitHub https://github.com/mortenbra/apex-plugins/issues/7#issuecomment-1586061002, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOCRUD3AEEVIKKX2EMBDQZLXKV2DPANCNFSM4V3HDHPQ . You are receiving this because you commented.Message ID: @.***>

-- Lic. Miguel A. Rosemberg F. Analista de Sistemas Informáticos

Cel. 0974-111000 LB. 021-280662

[MikiRosemberg]

If you have any doubts...there goes the screenshot [image: image.png]

El jue, 15 jun 2023 a la(s) 10:34, Miguel Rosemberg ( @.***) escribió:

Hi Marko, simply paste the following code in the plugin (shared components / Plugins / Pivot Table / Source-PL/SQL Code)

function render_pivot (p_region in apex_plugin.t_region, p_plugin in apex_plugin.t_plugin, p_is_printer_friendly in boolean ) return apex_plugin.t_region_render_result as l_returnvalue apex_plugin.t_region_render_result;

l_element_name varchar2(2000) := p_region.attribute_01; l_columns varchar2(2000) := replace(upper(p_region.attribute_02), ',', '","'); l_rows varchar2(2000) := replace(upper(p_region.attribute_03), ',', '","'); l_enable_ui varchar2(2000) := p_region.attribute_04; l_renderer varchar2(2000) := p_region.attribute_05; l_enable_charts varchar2(2000) := p_region.attribute_06; l_enable_export varchar2(2000) := p_region.attribute_07;

--l_ajax_id varchar(2000) := replace(apex_plugin.get_ajaxidentifier, '-', ''); l_ajax_id varchar(2000) := lower(dbms_random.string('x', 32)); l_data_name varchar2(2000) := 'pivotdata' || l_ajax_id; l_options_name varchar2(2000) := 'pivotoptions' || l_ajax_id; l_renderers_name varchar2(2000) := 'pivotrenderers' || l_ajax_id; l_onload_code varchar2(32000); l_context apex_exec.t_context;

begin

apex_css.add_file ( p_name => 'pivot.min', p_directory => p_plugin.file_prefix);

-- note: this file is also found in /i/libraries/jquery-ui/1.10.4/ui/minified/ but is not included by default by APEX apex_javascript.add_library ( p_name => 'jquery.ui.sortable.min', p_directory => p_plugin.file_prefix);

apex_javascript.add_library ( p_name => 'pivot.min', p_directory => p_plugin.file_prefix);

if l_enable_charts = 'Y' then apex_javascript.add_library ( p_name => 'plotly-latest.min', p_directory => 'https://cdn.plot.ly/'); apex_javascript.add_library ( p_name => 'plotly_renderers.min', p_directory => p_plugin.file_prefix); end if;

if l_enable_export = 'Y' then apex_javascript.add_library ( p_name => 'export_renderers.min', p_directory => p_plugin.file_prefix); end if;

l_context := apex_exec.open_query_context(
     p_location   => apex_exec.c_location_local_db,
     p_sql_query  => p_region.source );
apex_json.initialize_clob_output;
apex_json.write_context( l_context );

htp.p('

Loading, please wait...

');

htp.p('');

if l_enable_ui = 'Y' then if (l_enable_export = 'Y') or (l_enable_charts = 'Y') then l_onload_code := 'var ' || l_renderers_name || ';'; if (l_enable_export = 'Y') then l_onload_code := l_onload_code || l_renderers_name || ' = $.extend($.pivotUtilities.renderers, $.pivotUtilities.export_renderers);'; end if; if l_enable_charts = 'Y' then l_onload_code := l_onload_code || l_renderers_name || ' = $.extend($.pivotUtilities.renderers, $.pivotUtilities.plotly_renderers);'; end if; l_onload_code := l_onload_code || 'var ' || l_options_name || ' = {cols: ["' || l_columns || '"], rows: ["' || l_rows || '"], renderers: ' || l_renderers_name || ', rendererName: "' || l_renderer || '"};'; else l_onload_code := 'var ' || l_options_name || ' = {cols: ["' || l_columns || '"], rows: ["' || l_rows || '"], rendererName: "' || l_renderer || '"};'; end if; l_onload_code := l_onload_code || '$("#' || l_element_name || '").pivotUI(' || l_data_name || '.row, ' || l_options_name || ');'; else l_onload_code := 'var ' || l_options_name || ' = {cols: ["' || l_columns || '"], rows: ["' || l_rows || '"]};'; l_onload_code := l_onload_code || '$("#' || l_element_name || '").pivot(' || l_data_name || '.row, ' || l_options_name || ');'; end if;

apex_javascript.add_onload_code (l_onload_code);

return l_returnvalue; apex_exec.close( l_context );

end render_pivot;

And that's all..

Hope it works for you, now i tested on apex 23.1 and it works too!

El dom, 11 jun 2023 a la(s) 03:50, Marko-Goljak @.***) escribió:

Hi Miki

can you explain in which source where the replacement should be done or better paste the image where you made the replacement

Tnx

— Reply to this email directly, view it on GitHub https://github.com/mortenbra/apex-plugins/issues/7#issuecomment-1586061002, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOCRUD3AEEVIKKX2EMBDQZLXKV2DPANCNFSM4V3HDHPQ . You are receiving this because you commented.Message ID: @.***>

-- Lic. Miguel A. Rosemberg F. Analista de Sistemas Informáticos

Cel. 0974-111000 LB. 021-280662

-- Lic. Miguel A. Rosemberg F. Analista de Sistemas Informáticos

Cel. 0974-111000 LB. 021-280662