Converting Oracle APEX qAsk to the-Quiz App

For my sister's 50th birthday, I converted the Oracle APEX Starter app QASK into a Quiz app (which I called "The-Quiz").

You can found it here:

Why did I do this? I didn't want a commercial solution like Kahoot, and I plan to use this solution in the future for other APEX internal developers to test their knowledge and other things.

Todo this do following Steps

1) Added a new column in QASK_SESS_QUESTION_ANSWERS

ALTER TABLE QASK_SESS_QUESTION_ANSWERS ADD COLUMN POINTS NUMBER;

2) Created a new Page #123

With 2 Interactive Grids to add "Points" to the correct answers (shown on the right): It's a Master-Detail Grid, so when I select a question on the right, it shows the answer possibilities I already entered when creating a new session. (Left is on QASK_SESSION_QUESTIONS, Right is on QASK_SESS_QUESTION_ANSWERS)

3) On existing Page 120, I added 2 buttons

Two buttons: One for Single-Edit (you can imagine the usage) and one for our new Page 123 (Edit All Questions).

4) Displaying results to quiz participants

Now we have the data (0 = Wrong, 1 = Right), but we also want to display it when a quiz participant answers a question. The result should be shown as "Yes, it's correct" or "No, it's wrong."

We can do this on existing Page 21 so i do little changes on the dynamic Region &P20_QUESTION.
(which is a Legacy Region, because the qASK app is from 2012!)

The source code for this region:

if :P20_ANSWER_TYPE = 'MULTI' then
    sys.htp.p('<div class="multiple-answers">Deine Antwort:<ul>');
    for c1 in (
        select answer_text
          from qask_response_answers
         where question_id = :QUESTION_ID 
           and response_id = :RESPONSE_ID
    ) loop
        sys.htp.p('<li><span class="answer">'||apex_escape.html(c1.answer_text)||'</span></li>');
    end loop;
    sys.htp.p('</ul></div>');
else
    for c1 in (
          /* Get the Points data in this Cursor also */
        select a.answer_text, a.comment_text, q.answer_type, qa.points
          from qask_response_answers a,
               qask_session_questions q,
               qask_sess_question_answers qa 
         where a.question_id = qa.question_id 
           AND a.answer_id = qa.id
           and a.question_id = :QUESTION_ID 
           and a.response_id = :RESPONSE_ID
           and a.question_id = q.id
    ) loop
        if c1.answer_type = 'FREEFORM' then
            sys.htp.p('Deine Antwort<br/>'||apex_escape.html(c1.answer_text));
        else
            sys.htp.p('Deine Antwort<br/><span class="answer">'||apex_escape.html(c1.answer_text)||'</span>');
          /* Right or Wrong */
            if c1.points > 0 then 
            sys.htp.p('<br/>Und sie war .. RICHTIG');
            else
            sys.htp.p('<br/>Und sie war .. FALSCH');
            end if;

        end if;
        if c1.comment_text is not null then
        sys.htp.p('<br>'||apex_escape.html(c1.comment_text));

        end if;
    end loop;
end if;

That's fine for the participants.

5) Result chart for the quizmaster

When the last question is answered, we need a result chart for the quizmaster.

On Page 113, which is displayed on a large screen for participants during an interactive quiz with a quizmaster, I added a new Chart Region (Ranking List).

The top 10 are shown:

SELECT 
    session_id,
    rang,
    responder_name,
    total_points
FROM (
    SELECT 
        r.session_id,
        RANK() OVER (PARTITION BY r.session_id ORDER BY NVL(SUM(qa.points), 0) DESC, MIN(ra.created_on) ASC) AS rang,
        r.name AS responder_name,
        NVL(SUM(qa.points), 0) AS total_points
    FROM 
        qask_responses r
        LEFT JOIN qask_response_answers ra ON r.id = ra.response_id
        LEFT JOIN qask_sess_question_answers qa ON ra.question_id = qa.question_id 
                                               AND ra.answer_id = qa.id
    GROUP BY r.session_id, r.name
) ranked_responders
WHERE rang <= 10
and session_id = :P113_SESSION_ID
ORDER BY session_id, rang asc;

with a Server-Side Condition (no next question available, so we can show the ranking list).

I also added this chart to the Session Overview on Page 120. Miguel was the winner!

How long did these changes take?

It took a while to understand the behavior of this app and figure out where I could add points. I needed 45 minutes for blogging and about 1 hour for changing the app.

Cheers, Tom

Cheers Tom