Performance of Pretius Plug-in "Nested Report"

Scott Wesley

Looking with fresh eyes on this plugin for this question

I realised there is a performance issue with this plugin, and I'm wondering if there is a SQL solution without needing to re-write pretius_row_details_ajax in the plugin (I may have prefixed that function name).

Documentation shows you find the nested detail using this syntax

where  COLUMN_ID = #COLUMN_ID#

Which then substitutes in the literal values using

v_sql := replace( v_sql, '#'||v_columnNames(i)||'#', v_columnValues(i) );

so the resulting statement in the v$sqlarea will be

where column_id = 12345

Which means it's not using bind variables, and it's going to flood the shared SQL pool

I have logged this concern in the plugin's git repo

https://github.com/Pretius/apex-nested-reports/issues/5

Scott

ostrowskibartosz

Hi Scott,

thank you for your feedback. The issue needs investigation and I will inform you about results.