Oracle Apex Master Detail Report and Detail report with Radio Button Option to select

Question

I am looking for advice to build a simple Quiz type application in Oracle Apex.
I don't need to build any Create/Update/Delete screens for base tables since I will populate my tables with data using SQL statements.

I have two tables

Create Table Question_Bank
(
Question_Id              Number (5)   Primary Key ,
Question_Description     Varchar2(1000)
)
;

Question_Choice_Id is like 1,2,3,4 ( it is like no of quiz option choices)

Create Table  Question_Choices
(
Question_Choice_Pk            Number (5)   Primary Key,
Question_Id                   Number(5) References Question_Bank(Question_Id),
Question_Choice_Id            Number(1),
Question_Choice_Description   Varchar2(200),
Is_Correct                    Varchar(1) Default 'N',
Explanation                   Varchar2(500)
);

So application should be displaying questions and options like this.
I would prefer one scrollable page of some numbers on question on one page.

This is Question No 1

Option 1
Option 2
Option 3
Option 4


This is Question No 2

Another Option 1
Another Option 2
Another Option 3
Another Option 4


This is Question No 3

Q No 3  Option 1
Q No 3  Option 2
Q No 3  Option 3
Q No 3  Option 4

So far I have created Master/Detail form which is kind of report (not editable) and I can see questions and possible choices (I have select question and choices are shown in 2nd column) but this is not what I want.
I am using Free Oracle Apex online account version 20.2.

If you need sql file for Table and data, drive.google.com/file/d/1ZGTNr5RnHpT_ljllTuaVteFoZ4VAjNws/… — Sanjay

Damian Damian · Accepted Answer · 2021-01-08T00:32:21

You can try dynamicly build page with questions and choices. You have to add a new PL/SQL Dynamic Region region to page. In Source > PL/SQL Code region attribute put sample code below.

FOR l_question IN (
    SELECT QUESTION_ID, QUESTION_DESCRIPTION
    FROM QUESTION_BANK
    order by QUESTION_ID
) LOOP
    HTP.P('<h3>' || APEX_ESCAPE.HTML(l_question.QUESTION_DESCRIPTION) || '</h3>');
    HTP.P(
        APEX_ITEM.HIDDEN(
            p_idx   => 1, -- Values stored in APEX_APPLICATION.g_f01 table.
            p_value => l_question.QUESTION_ID
        )
    );
    HTP.P('<fieldset id="qfs' || l_question.QUESTION_ID || '">');
    HTP.P(
        APEX_ITEM.TEXT(
            p_idx           => 2, -- Values stored in APEX_APPLICATION.g_f02 table.
            p_item_id       => 'qh' || l_question.QUESTION_ID,
            p_attributes    => 'style="display:none"'
        )
    );
    
    FOR l_choice IN (
        SELECT QUESTION_CHOICE_ID, QUESTION_CHOICE_DESCRIPTION
        FROM QUESTION_CHOICES
        WHERE QUESTION_ID = l_question.QUESTION_ID
    ) LOOP
        HTP.P('<input type="radio" value="' || l_choice.QUESTION_CHOICE_ID || '" name="' || l_question.QUESTION_ID || '" onchange="document.getElementById(''qh' || l_question.QUESTION_ID || ''').value = this.value">');
        HTP.P('<label for="' || l_question.QUESTION_ID || '">' || APEX_ESCAPE.HTML(l_choice.QUESTION_CHOICE_DESCRIPTION) || '</label>');
    END LOOP;
    
    HTP.P('</fieldset>');
END LOOP;

This PL/SQL block iterates through all questions, then prints HTML markup with question description and fieldset with radio buttons. As you can see I use APEX_ITEM package to generate two columns - QUESTION_ID and QUESTION_CHOICE_ID. Each iteration creates new record with these two columns. If you checked the APEX documentation you may wonder why I didn't use APEX_ITEM.RADIOGROUP for building radio buttons. The trick is this procedure doesn't fit for this case, because its behaves different then developers expects. So insted of using this procedure i build radio group buttons manually. I also put simple onchange event for every input to save selected choice in every question. This event sets item in second column.

Each column is stored in collection APEX_APPLICATION.G_Fxx, where xx is between 01 and 50. There is p_idx parameter which determines index of collection for store column values. After page submit, APEX submit values in those collections. You can handle this, creating new process (Processing > Processes or Processing > After Submit) and pasting code below.

DECLARE
    v_question_count    NUMBER := APEX_APPLICATION.g_f01.COUNT;
    v_question_answer_id QUESTION_ANSWERS.QUESTION_ANSWER_ID%TYPE := QUESTION_ANSWERS_SEQ.NEXTVAL;
BEGIN
    FOR i IN 1..v_question_count LOOP
        INSERT INTO QUESTION_ANSWERS (QUESTION_ANSWER_ID, QUESTION_ID, QUESTION_CHOICE_ID)
        VALUES (v_question_answer_id, APEX_APPLICATION.g_f01(i), APEX_APPLICATION.g_f02(i));
    END LOOP;
END;

There is another iteration through collections (both has the same number of members) and insert values into some table.

My code generates form without fancy styling, so you have to handle this yourself :)

Oracle Apex Master Detail Report and Detail report with Radio Button Option to select

2 Answers