APEX Report with checkboxes (advanced)

There will be many requirements to build a report with check boxes. Once the user selected all the rows, they would click submit and the application would process the rows. Sounds pretty simple and straight forward but there might be some extra requirements:

- There may be up to 10,000 records in the report

- When the user "scrolls" through the report (i.e. uses pagination), if they checked off a box it should remain checked the entire time (i.e. if they check an row in the 1st 15 rows, then view rows 16~30, then go back to rows 1~15 it should remain checked).

Below are the detailed steps on how to do it.

Step 1:

Create an application item F_EMPNO_LIST. Note you can use a page item as well...

Setup your report:

SELECT apex_item.checkbox (1,
                           empno,
                           'onchange="spCheckChange(this);"',
                           :f_empno_list,
                           ':'
                          ) checkbox,
empno, ename, job
FROM emp;

Once the report is setup set the row display to 5 (you'll need it for this example)

Step 2:

Add an HTML region and add the following code: (Note: the jQuery call is not needed... for now)

<script src="http://www.google.com/jsapi"></script>
<script>
  // Load jQuery
  google.load("jquery", "1.2.6", {uncompressed:true});

  function spCheckChange(pThis){
    var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=CHECKBOX_CHANGE',$v('pFlowStepId'));
    get.addParam('x01',pThis.value); //Value that was checked
    get.addParam('x02',pThis.checked ? 'Y':'N'); // Checked Flag
    gReturn = get.get();
    
    $x('checkListDisp').innerHTML=gReturn;
  }
  
</script>
CHECKBOX List:

<div id="checkListDisp">&F_EMPNO_LIST.</div>

Step 3:
Now create an application process (on Demand) called: CHECKBOX_CHANGE
-- Application Process: CHECKBOX_CHANGE
-- On Demand...

DECLARE
  v_item_val NUMBER := apex_application.g_x01;
  v_checked_flag VARCHAR2 (1) := apex_application.g_x02;
BEGIN
  IF v_checked_flag = 'Y' THEN
    -- Add to the list
    IF :f_empno_list IS NULL THEN
      :f_empno_list := ':' || v_item_val || ':';
    ELSE
      :f_empno_list := :f_empno_list || v_item_val || ':';
    END IF;
  ELSE
    -- Remove from the list
    :f_empno_list := REPLACE (:f_empno_list, ':' || v_item_val || ':', ':');
  END IF;

  -- Just for testing
  HTP.p (:f_empno_list);
END;

Step 4:
On the post page create a query to view data (you can process how you need/want)

select *
from emp
where instr(:F_EMPNO_LIST, ':' || empno || ':') > 0

Then you can try out in your report checking the check boxes and paginating. Your previously checked ones in the report will still be checked once you paginate back.



   

q Function Inside a q Function:

Reference: http://www.talkapex.com/2009/03/q-function-escape-single-quotes.html 

Last month I wrote about how to escape single quotes in string using the q function:

I recently had an requirement where I wanted to use a q function inside another q function. Here's what I tried:

DECLARE
  l_code varchar2(4000);

BEGIN
  l_code := q'!BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END; !';
  
  dbms_output.put_line(l_code);
  
  execute immediate l_code;
  
END;
/

ERROR:
ORA-01756: quoted string not properly terminated
You'll notice that the above code doesn't run. The reason it doesn't work is that I'm using the same character (!) as the quote delimiter. To fix this, each "Q" block must have it's own unique quote delimiter. Below is a copy of the same code but using two different quote delimiters (# and !).
   
DECLARE
  l_code varchar2(4000);

BEGIN
  l_code := q'#BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END; #';
  
  dbms_output.put_line(l_code);
  
  execute immediate l_code;
  
END;
/

BEGIN dbms_output.put_line(q'!It's cold in Calgary!'); END;
It's cold in Calgary

PL/SQL procedure successfully completed.
For more information on the q function (it's real name is "Q-quote mechanism") read the Oracle documentation: http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#BABECADE and scroll down to the "Quoting Character Literals" section.

Recover deleted Page with APEX and Firebug

Today a very important Page of an important APEX Application has accidentally been deleted. We didn’t want to step back to yesterdays backup, because right before the deletion many changes took place and we didn’t want to code that again. Oracle APEX includes the really nice feature of exporting an Application “As of x minutes ago”.

But there is also a dialog where you can export a single page, without the whole application overhead:

But this dialog lists only currently existing Pages and doesn’t include our deleted Page.
Here comes our Firebug to the rescue using which we manipulate the HTML-Code of the Page-Select-List and add our deleted Page 99 which then exports easily “As of 30 minutes ago”.

1. Use Firebugs “Inspect Element” on the Page-Select-List

2. Edit the HTML and insert a new <option>

3. Pick the newly added entry from the select-list, enter 30 as number of minutes and press the export button

4. Go to the import menu and import the Page-Export File.

And there you go..... You can find your page which was accidentally deleted.