Oracle Apex Cards View with PL/SQL Parsed at Runtime

Question

I am trying to get the cards report view working with Oracle Apex, but due to a bug mentioned in this SO question I am trying to work with a query that is parsed at runtime.

"Use Generic Column Names (parse query at runtime only)" is selected for the query.

Unfortunately the cards view requires the right column headers to work. Here is my code:

DECLARE

  l_query VARCHAR2(4000);
  l_app number := v('APP_ID');
  l_session number := v('APP_SESSION');

  --Bug happens on the ':11:' part, page 1 works fine
  l_url VARCHAR2(500) := (APEX_UTIL.PREPARE_URL(
           p_url => 'f?p=' || l_app || ':11:' || l_session || '::NO:::'
  ));
BEGIN
  l_query:= 
    'SELECT 
       post_id,
       user_id as "CARD_SUBTEXT",
       image as "CARD_IMAGE",
       title as "CARD_TITLE",
       ''' || l_url || ''' as "CARD_LINK",
       text as "CARD_TEXT"
     FROM posts';

  IF v('P1_TEXT_SEARCH') IS NOT NULL THEN
    l_query := l_query||' '||'
    WHERE 
    (   
     CONTAINS(title, ''$' || :P10_TEXT_SEARCH || ''') > 0 
    ) OR
    (
     CONTAINS(text, ''$' || :P10_TEXT_SEARCH || ''') > 0
    )
   ';
  END IF; 

  htp.p(l_query);
  RETURN l_query;
END;

Which looks like the following:

Any ideas how I can get this to work?

In that case, you could copy the Cards report template and change the column references #CARD_TEXT# etc. to #COL01# etc. as appropriate in the new template? — Tony Andrews
Thank you, this seems to be the right direction. But, I copied the Cards Report template then edited it (it's not possible for me to edit the master templates). Now it's coming up with "no data found" in the report. Perhaps another SO question is needed — HedgepigMatt

HedgepigMatt HedgepigMatt · Accepted Answer · 2015-12-22T14:24:28

As pointed out by Tony Andrews I can copy and edit the template to set the values as #COL1#, #COL2# ...

Oracle Apex Cards View with PL/SQL Parsed at Runtime

1 Answers