Following on from the last post, given that we were automatically creating hidden items to support the grid filters we still needed to add the conditions to the SQL query. I toyed with the idea of putting the required additional query structure in a table, out to a file, or to the screen, but I thought: Why don’t I just update the query source myself dynamically. So next comes the problem, is there an API call to do this or will I have to update an APEX table…..

Initially I though I found my answer, wwv_flow_api.set_plug_source, but after implementing the code to do the update I was getting ORA errors relating to LOB(s) given that the API call requires a varchar2 for plug_source but the underlying table column used a CLOB I came to the conclusion that either I had the wrong API call or it was simply deprecated/not in use.

So the solution I adopted was to update the APEX table directly (yeah I know bit of hack and not supported but come on it’s not like I’m going to break anything, well unless my code is wrong which happened during development on multiple occasions. Remember always ensure you have a good backup policy in place! Oh and don’t forget to ammend this when you upgrade APEX in the future!): “apex_030200.WWV_FLOW_PAGE_PLUGS”

Here’s the dependency SQL…

grant select,update on apex_030200.WWV_FLOW_PAGE_PLUGS to USERNAME
/
create synonym USERNAME.WWV_FLOW_PAGE_PLUGS for apex_030200.WWV_FLOW_PAGE_PLUGS
/

Before I get to the underlying PLSQL code, as this is a bit of a hack and only required in development I decided that I wanted to code it in such a way that the grant will only be provided in the development database however the PLSQL package it resides in, will be migrated to production, therefore I’d use “EXECUTE IMMEDIATE” to perform the update so that the package would still be valid even if he grant wasn’t applied.

Here’s the PLSQL source extract…

.............
          --
          -- For dates we will apply the defined format otherwise we will rely on
          -- the application format default
          --
          IF c1.data_type = 'DATE' AND c.format_mask IS NOT NULL THEN
            v_qry_filter := v_qry_filter ||' AND (('||c.column_alias||' <= '
                                         ||'to_date(:'||v_item_name||','''||c.format_mask||''')) OR (:'|| v_item_name
                                         ||' IS NULL))'||CHR(10)||CHR(13);
          ELSE
            v_qry_filter := v_qry_filter ||' AND (('||c.column_alias||' <= :'
                                         ||v_item_name||') OR (:'|| v_item_name
                                         ||' IS NULL))'||CHR(10)||CHR(13);
          END IF;
..........
    IF v_items_added THEN

      --
      -- Allocate memory (temp table space) for the clob.
      --
      dbms_lob.createtemporary
      ( lob_loc  => v_qry_source
      , cache    => TRUE
      ) ;

      --
      -- Lets make sure we have the grant to update the table
      --
      FOR c_has_tab_priv IN (
        SELECT table_name
        FROM   user_tab_privs
        WHERE  table_name = 'WWV_FLOW_PAGE_PLUGS'
        AND    owner      = wwv_flow.g_flow_schema_owner
        AND    privilege  = 'UPDATE'
      ) LOOP

        --
        -- We need to update the query definition with our new filters
        --
        FOR c IN (
          SELECT region_id
          ,      region_source
          FROM   apex_application_page_regions v
          WHERE  application_id  = v('APP_ID')
          AND    page_id         = v('APP_PAGE_ID')
          AND    template IN ('Ext.grid','Ext.grid.edit')
        ) LOOP
          --
          -- To simplify adding our filters to an exisiting query we will simply
          -- wrap the query in an inlinve view and perform the filters on the inline view
          --
          dbms_lob.write
          ( lob_loc     => v_qry_source
          , amount      => length('SELECT * FROM (')
          , offset      => 1
          , buffer      => 'SELECT * FROM ('
          );

          dbms_lob.append
          ( dest_lob     => v_qry_source
          , src_lob      => c.region_source
          );

          dbms_lob.write
          ( lob_loc     => v_qry_source
          , amount      => length(') WHERE 1=1 '||v_qry_filter)
          , offset      => DBMS_LOB.getlength(v_qry_source)+1
          , buffer      => ') WHERE 1=1 '||v_qry_filter
          );

          --
          -- Lets parse the SQL query before saving it, and we won't save it if it's not valid
          --
          c_id := dbms_sql.open_cursor;
          dbms_sql.parse(c_id, v_qry_source, dbms_sql.native);
          dbms_sql.close_cursor(c_id);

          --
          -- Lets using EXECUTE IMMEDIATE rather than a explicit update statement
          -- in case people may not want to apply the grant on  WWV_FLOW_PAGE_PLUGS
          --
          EXECUTE IMMEDIATE 'UPDATE WWV_FLOW_PAGE_PLUGS '||
                            'SET    plug_source = :b1 '||
                            'WHERE  id          = :b2 '||
                            'AND    flow_id     = :b3 '||
                            'AND    page_id     = :b4'
          USING IN v_qry_source, c.region_id, v('APP_ID'), v('APP_PAGE_ID');

          --
          -- We need to erase the lob in case there is more than 1 grid on the page
          --
          v_source_length := DBMS_LOB.getlength(v_qry_source);

          dbms_lob.erase
          ( lob_loc     => v_qry_source
          , amount      => v_source_length
          , offset      => 1
          );
        END LOOP;
      END LOOP;

      --
      -- Free the temporary LOB
      --
      dbms_lob.freetemporary(v_qry_source);

    END IF;

The end result is that we can create a normal APEX report via the wizard, set the template to our Ext.grid template, update the column attributes to enable/disable filtering, and we’re done. It takes like 2 minutes to complete the report in full, so we get a big productivity boost by dynamically generating the items and appending the query source with our additional filters.