Saving Checkbox data in a DB column

Question

I created an item P1_checkbox in Oracle APEX, using LOV(select ename, empno from emp WHERE ename LIKE 's%';) which gives multiple checkboxes.
When I check the boxes, I want to INSERT the checked boxes data into another table "emp_selected".

Then, when I login into the application again, the application now should have the checkboxes ticked(checked) based on the data inserted into the table "emp_selected" along with unchecked boxes from table "emp" and when I uncheck some of pre-selected checkboxes and check the new checkboxes, it should be updated accordingly in "emp_selected".

My Oracle APEX version is 4.2.

Tom · Accepted Answer · 2015-04-17T05:34:12.710

2

An apex checkbox with multiple checkboxes will save its state as all selected values concatenated to eachother with a colon, eg One:Two:Three.
So you will have to seperate those values, loop over them, and save them.
And you will need to configure your checkbox source correctly.
See the sample page I set up (apex_demo/demo)

All my setup is on that page too. For the sake of the example I'm using an apex collection to simulate a table which stores the values. The principle is the exact same - you'll simple need to adapt to your table with your selections.

Checkboxes LOV SQL:

select ename, empno from emp

Checkboxes Source settings:

Source: Always - SQL Query return colon seperated value

select c001
from apex_collections
where collection_name = 'CHECKBOX_EXAMPLE'

This will take all the values selected and concatenate them to eachother with a colon. And the checkbox will take those values and mark them as being selected.

An after submit process:

DECLARE
    l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  apex_collection.create_or_truncate_collection('CHECKBOX_EXAMPLE');
    l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(:P37_CHECKBOXES);
    FOR z IN 1..l_vc_arr2.count LOOP
        apex_debug.message(l_vc_arr2(z));
        apex_collection.add_member(p_collection_name => 'CHECKBOX_EXAMPLE', p_c001 => l_vc_arr2(z));
    END LOOP;
END;

edited Apr 17 '15 at 05:34

answered Apr 16 '15 at 09:43

Tom

6,988
1
26
40

Hi @Tom, Thanks for the reply. I have one question though, After selection of checkboxes, I want the value of checkboxes to be stored on another table 'emp_selected' and then when someone login again, I want to display the checkboxes from emp but the checkboxes should be ticked whose value stored in 'emp_selected' and If I uncheck some boxes, and save the data again, 'emp_selected' table should be updated accordingly. – Neurotic Apr 17 '15 at 07:35
@DOC That is why I used a collection. Pretty much all you would have to do is to change the collection calls with sql which manipulates your table. Eg add_member would be an `INSERT` statement into your `emp_selected` table. The source of the item would not be a select from the collections view, but from your own table. That is pretty basic sql and plsql. – Tom Apr 17 '15 at 07:56
I tried using the Insert Statement > Insert into emp_selected (eno, ename, dname, creation_date) values(l_vc_arr2(z), :P1_NAME, :P1_DEPT, SYSDATE); in place of > apex_collection.add_member(p_collection_name => 'CHECKBOX_EXAMPLE', p_c001 => l_vc_arr2(z)); and In source I used > select eno from emp_selected where eno = 'CHECKBOX_EXAMPLE' ; Instead of > select c001 from apex_collections where collection_name = 'CHECKBOX_EXAMPLE' But I am getting an error **ORA-01722: invalid number** – Neurotic Apr 17 '15 at 10:47
You don't need the where clause in your source. That where clause only served for the apex collection. Since you're comparing a number column with a string, it'll try to convert the string and you get that error. – Tom Apr 17 '15 at 12:23
Hi @Tom, I edited the where clause before, The error was gone, But the data is not INSERTING into the table 'emp_selected'. An after submit process code, > DECLARE l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2; BEGIN apex_collection.create_or_truncate_collection('CHECKBOX_EXAMPLE'); l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(:P1_SALES); FOR z IN 1..l_vc_arr2.count LOOP apex_debug.message(l_vc_arr2(z)); Insert into emp_selected (empno, ename, dname, creation_date) values(l_vc_arr2(z), :P1_NAME, :P1_DEPT, SYSDATE); END LOOP; END; – Neurotic Apr 20 '15 at 05:57
Okay. Do you receive any errors then? Have you tried to debug your page? What version of apex do you run? Also remove the unnecessary lines: apex_collection and apex_debug. Is there any data in your emp_selected table or not? – Tom Apr 20 '15 at 08:33
I removed unnecessary lines and I am getting success message but the selected checkboxes data has not saved into the table `emp_selected`. I am using ORACLE APEX 4.2 – Neurotic Apr 20 '15 at 09:20
You know data has not been saved because you checked the table data from eg the sql workshop or because you don't see checked checkboxes appearing? This back and forth guessing is pretty hard. Provide full DDL statements so I can recreate everything as exact as possible, or create a workspace on apex.oracle.com, set up your page there, and then share some developer credentials so I can look there. – Tom Apr 20 '15 at 09:49

score 0 · Answer 2 · answered Apr 15 '15 at 08:01

0

If you want to insert data on specific check event then you can use Ajax Call back with Run on demand process. which will get call from JavaScript code. You can refer this blog : http://warp11.nl/2011/04/execute-page-process-with-ajax-callback/

answered Apr 15 '15 at 08:01

Amol

429
4
22

1

Bear in mind you can implement an AJAX call on an application process in a much cleaner way that this. e.g. by using `apex.server.process`. – Drumbeg Apr 15 '15 at 12:22
OP wants to save checkbox state. I don't understand how you leapt to an ajax process to perform this. – Tom Apr 16 '15 at 09:08

Saving Checkbox data in a DB column

2 Answers2