APEX 5.0 : dynamic pivot 'in' clause returning no data

mikeTel

Hello Gurus,

I have the following dynamic query for classic report based on function.

DECLARE

  l_sql     varchar2(32767);

  l_sql2    varchar2(32767);

  l_query   varchar2(32767);

  l_count   pls_integer := 0;

  l_count2   pls_integer := 0;

  l_pivot_column varchar2(32767);

  l_column_no pls_integer := 1;

BEGIN

   l_sql := 'WITH DGRID AS (';

   --

   l_sql := l_sql || ' ' || 'select distinct';

   l_sql := l_sql || ' ' || ',lead_name';

   l_sql := l_sql || ' ' || ',seq';

   l_sql := l_sql || ' ' || ',count(lead_name) over () fcount';

   l_sql := l_sql || ' ' || 'from ';

   l_sql := l_sql || ' ' || 'from tablA';

   l_sql := l_sql || ' ' || 'where trim(lead_name) is not null';

   l_sql := l_sql || ' ' || 'and group_id = :P7_CURRENT_GROUP';

   l_sql := l_sql || ' ' || ')';

   --

   l_sql := l_sql || ' ' || 'SELECT * FROM dgrid';

   l_sql := l_sql || ' ' || 'PIVOT';

   l_sql := l_sql || ' ' || '(';

   l_sql := l_sql || ' ' || 'max(fcount) FOR lead_name in (%PTL%))';

    l_sql:=replace(l_sql,'%PTL%',:P7_ITEM_NAMES);

    return l_sql;

END;

When running it, it gives an error saying "report error : no data found".

I ran it in debug mode and found the following error:

But when I tried to ran it using generic columns, there is no issue.

Any ideas what am I missing?

Thanks in advance

fac586

mikeTel wrote:I have the following dynamic query for classic report based on function.DECLARE l_sql varchar2(32767); l_sql2 varchar2(32767); l_query varchar2(32767); l_count pls_integer := 0; l_count2 pls_integer := 0; l_pivot_column varchar2(32767); l_column_no pls_integer := 1;BEGIN l_sql := 'WITH DGRID AS ('; -- l_sql := l_sql || ' ' || 'select distinct'; l_sql := l_sql || ' ' || ',lead_name'; l_sql := l_sql || ' ' || ',seq'; l_sql := l_sql || ' ' || ',count(lead_name) over () fcount'; l_sql := l_sql || ' ' || 'from '; l_sql := l_sql || ' ' || 'from tablA'; l_sql := l_sql || ' ' || 'where trim(lead_name) is not null'; l_sql := l_sql || ' ' || 'and group_id = :P7_CURRENT_GROUP'; l_sql := l_sql || ' ' || ')'; -- l_sql := l_sql || ' ' || 'SELECT * FROM dgrid'; l_sql := l_sql || ' ' || 'PIVOT'; l_sql := l_sql || ' ' || '('; l_sql := l_sql || ' ' || 'max(fcount) FOR lead_name in (%PTL%))'; l_sql:=replace(l_sql,'%PTL%',:P7_ITEM_NAMES); return l_sql;END;

Concatenation and quotes should be avoided when working with dynamic SQL. They are invariably a source of error, but more importantly this approach is potentially more vulnerable to SQL injection attack and may have adverse performance implications. Instead:

• Implement the query template as a literal string using your standard SQL formatting rules. This is infinitely more readable than multiple concatentations, and enables the query to be easily copied from and pasted to other tools for development and testing.
• Use the alternative quoting mechanism to avoid the need to escape string quotes.
• Use text replacement to implement lexical substitution of object names.
• Always sanitise inputs using dbms_assert API methods.
• Always use bind variables to supply predicate values.
• Return a valid query when the input parameter values are absent using suitable default substitutions.
• Use debug tools to log the generated SQL for troubleshooting purposes.

declare  subtype t_sql_text is varchar2(32767);  l_sql t_sql_text := q'{with dgrid as (      select          distinct          lead_name        , seq        , count(lead_name) over () fcount      from          tablA      where          trim(lead_name) is not null      and group_id = :P7_CURRENT_GROUP)select    *from    dgridpivot (    max(fcount) for lead_name in (%pivot%))}';begin  l_sql := replace(l_sql, coalesce(:p7_item_names, 'ITM1'));  apex_debug.message('P7 pivot SQL = %s', l_sql);  return l_sql;end;

mikeTel

btw, P7_ITEM_NAMES has this value :  'ITM1', 'ITM2'

fac586

mikeTel wrote:Hello Gurus,I have the following dynamic query for classic report based on function.DECLARE l_sql varchar2(32767); l_sql2 varchar2(32767); l_query varchar2(32767); l_count pls_integer := 0; l_count2 pls_integer := 0; l_pivot_column varchar2(32767); l_column_no pls_integer := 1;BEGIN l_sql := 'WITH DGRID AS ('; -- l_sql := l_sql || ' ' || 'select distinct'; l_sql := l_sql || ' ' || ',lead_name'; l_sql := l_sql || ' ' || ',seq'; l_sql := l_sql || ' ' || ',count(lead_name) over () fcount'; l_sql := l_sql || ' ' || 'from '; l_sql := l_sql || ' ' || 'from tablA'; l_sql := l_sql || ' ' || 'where trim(lead_name) is not null'; l_sql := l_sql || ' ' || 'and group_id = :P7_CURRENT_GROUP'; l_sql := l_sql || ' ' || ')'; -- l_sql := l_sql || ' ' || 'SELECT * FROM dgrid'; l_sql := l_sql || ' ' || 'PIVOT'; l_sql := l_sql || ' ' || '('; l_sql := l_sql || ' ' || 'max(fcount) FOR lead_name in (%PTL%))'; l_sql:=replace(l_sql,'%PTL%',:P7_ITEM_NAMES); return l_sql;END;When running it, it gives an error saying "report error : no data found".

I ran it in debug mode and found the following error:

But when I tried to ran it using generic columns, there is no issue.

Any ideas what am I missing?

These ORA-01403 report error issues are notoriously difficult to isolate. They may be caused by the APEX engine referencing metadata for columns that appeared in an earlier instance of the report created using different parameters but do not exist now. You may want to check whether any user preferences exist for region using the report in the admin tools.

Frequently the quickest or only way to resolve this is to recreate the page from scratch.

However, for dynamic pivots the best approach is probably to use generic columns. Set the report Headings Type attribute to PL/SQL, and return the required headings as a colon-delimited string from an anonymous function body in the Function returning colon delimited headings report attribute.

fac586

mikeTel wrote:I have the following dynamic query for classic report based on function.DECLARE l_sql varchar2(32767); l_sql2 varchar2(32767); l_query varchar2(32767); l_count pls_integer := 0; l_count2 pls_integer := 0; l_pivot_column varchar2(32767); l_column_no pls_integer := 1;BEGIN l_sql := 'WITH DGRID AS ('; -- l_sql := l_sql || ' ' || 'select distinct'; l_sql := l_sql || ' ' || ',lead_name'; l_sql := l_sql || ' ' || ',seq'; l_sql := l_sql || ' ' || ',count(lead_name) over () fcount'; l_sql := l_sql || ' ' || 'from '; l_sql := l_sql || ' ' || 'from tablA'; l_sql := l_sql || ' ' || 'where trim(lead_name) is not null'; l_sql := l_sql || ' ' || 'and group_id = :P7_CURRENT_GROUP'; l_sql := l_sql || ' ' || ')'; -- l_sql := l_sql || ' ' || 'SELECT * FROM dgrid'; l_sql := l_sql || ' ' || 'PIVOT'; l_sql := l_sql || ' ' || '('; l_sql := l_sql || ' ' || 'max(fcount) FOR lead_name in (%PTL%))'; l_sql:=replace(l_sql,'%PTL%',:P7_ITEM_NAMES); return l_sql;END;

Concatenation and quotes should be avoided when working with dynamic SQL. They are invariably a source of error, but more importantly this approach is potentially more vulnerable to SQL injection attack and may have adverse performance implications. Instead:

• Implement the query template as a literal string using your standard SQL formatting rules. This is infinitely more readable than multiple concatentations, and enables the query to be easily copied from and pasted to other tools for development and testing.
• Use the alternative quoting mechanism to avoid the need to escape string quotes.
• Use text replacement to implement lexical substitution of object names.
• Always sanitise inputs using dbms_assert API methods.
• Always use bind variables to supply predicate values.
• Return a valid query when the input parameter values are absent using suitable default substitutions.
• Use debug tools to log the generated SQL for troubleshooting purposes.

declare  subtype t_sql_text is varchar2(32767);  l_sql t_sql_text := q'{with dgrid as (      select          distinct          lead_name        , seq        , count(lead_name) over () fcount      from          tablA      where          trim(lead_name) is not null      and group_id = :P7_CURRENT_GROUP)select    *from    dgridpivot (    max(fcount) for lead_name in (%pivot%))}';begin  l_sql := replace(l_sql, coalesce(:p7_item_names, 'ITM1'));  apex_debug.message('P7 pivot SQL = %s', l_sql);  return l_sql;end;

mikeTel

Thank you for your guidance.

I have opted to use generic columns as suggested.