Introduction/ Issue:
Creating interview or assessment questions manually is a time consuming process, especially when maintaining a large question bank for multiple technical skills such as SQL, PL/SQL, Python, Java, and HTML. Although Oracle APEX Generative AI can automatically generate interview questions, the answer options are often returned in a fixed order. This can make assessments less effective by creating predictable answer patterns. This blog demonstrates how to generate AI-based interview questions, randomize the answer options while preserving the correct answer, and store them automatically in the database.
Why we need to do / Cause of the issue:
Manual question creation has several disadvantages:
- Creating a large question bank requires significant effort.
- Repeated questions reduce assessment quality.
- Fixed answer order makes it easier for candidates to guess answers.
- Manual shuffling of answer options is error-prone.
- If answer options are rearranged without updating the correct answer, incorrect answers may be stored in the database.
- Managing multiple technical skills requires additional maintenance.
How do we solve:
Step 1 – Configure the Generative AI Service
Before generating AI-based questions, configure the Generative AI service in Oracle APEX.
Navigate to:
Workspace Utilities → Generative AI Services
Create a new AI service.
Step 2 – Generate an API Key
Create an account with the AI provider (Cohere) and generate an API Key.
Store this API Key securely by creating a Web Credential in Oracle APEX.
Navigate to:
Workspace Utilities → Web Credentials
Create a Web Credential and save the generated API Key.
Note: Never store API keys directly in PL/SQL code. Always use Oracle APEX Web Credentials.
Step 3 – Configure the AI Model
Configure the AI model in the Generative AI Service.
Click Test Connection to verify the AI service configuration.
If the connection is successful, Oracle APEX is ready to generate AI-based content.
Step 4 – Create the Skill Selection
Create a Select List item to allow users to choose a technical skill.
SELECT Skill_name D, Skill_name R FROM AI_skills ORDER BY 1;
The selected skill is passed to the AI prompt.
Examples: SQL, PL/SQL, Python, Java ,etc .
Step 5 – Configure the Generate Text With AI Process
Create a Generate Text With AI process.
Provide a detailed System Prompt.
Example:
Generate one advanced and unique multiple-choice interview question for the skill #P3_SKILLS#.
Requirements:
* Generate a different question every time.
* Focus on real interview scenarios.
* Prefer topics such as:
SQL: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOINS, Subqueries, Constraints, Views, Aggregate Functions.
Java: Variables, Data Types, OOP Concepts, Constructors, Inheritance, Polymorphism, Collections, Exception Handling.
Python: Variables, Data Types, Functions, Lists, Tuples, Dictionaries, OOP, Exception Handling.
* Avoid repeating previously generated questions.
* Provide exactly 4 options. * Only one option is correct.
* Return only valid JSON.
Expected Output Example:
{
"question": "Sample Question",
"option_a": "Option A",
"option_b": "Option B",
"option_c": "Option C",
"option_d": "Option D",
"answer": "option_b"
}
IMPORTANT:
* Use easy to intermediate level interview questions.
* Do not generate advanced or expert-level questions.
* Do not use Unicode escape sequences (\uXXXX).
* Return ONLY the JSON object.
This process dynamically generates interview questions according to the selected skill.
Step 6 – AI–Generated JSON Output
Example AI response:
{
“question”: “Which Java collection maintains insertion order?”,
“option_a”: “HashSet”,
“option_b”: “ArrayList”,
“option_c”: “TreeSet”,
“option_d”: “PriorityQueue”,
“answer”: “option_b”
}
The response is stored temporarily in a page item.
Step 7 – Parse the JSON
Use the APEX_JSON package to extract the question and options.
Example:
apex_json.parse(:P3_QUESTION_AREA);
v_question := apex_json.get_varchar2('question');
l_opts(1):= apex_json.get_varchar2('option_a');
l_opts(2):= apex_json.get_varchar2('option_b');
l_opts(3):= apex_json.get_varchar2('option_c');
l_opts(4):= apex_json.get_varchar2('option_d');
This converts the JSON response into PL/SQL variables.
Step 8 – Shuffle the Answer Options
Instead of displaying the options in the original order, use DBMS_RANDOM to randomize them.
FOR i IN 1..20 LOOP v_i := TRUNC(DBMS_RANDOM.VALUE(1,5)); v_j := TRUNC(DBMS_RANDOM.VALUE(1,5)); v_temp := l_opts(v_i); l_opts(v_i) := l_opts(v_j); l_opts(v_j) := v_temp; END LOOP;
After shuffling, determine the new position of the correct answer and update the answer accordingly.
This ensures:
- Different option order every time
- Correct answer remains accurate.
- Better assessment security
Step 9 – Store the Question
Insert the generated question into the Question Bank table.
Example:
INSERT INTO AI_QUESTION_BANK (
SKILL_NAME, QUESTION_TEXT, CORRECT_ANSWER,
CREATED_DATE, CREATED_BY )
VALUES (
:P3_SKILLS, apex_json.get_varchar2('question'),
l_correct_opt, SYSDATE, :APP_USER );
Step 10 – Store the Answer Options
Store each option in a separate table.
Example:
INSERT INTO AI_QUESTION_OPTIONS ( QUESTION_ID, OPTION_LABEL,
OPTION_TEXT, IS_CORRECT )
VALUES ( l_question_id, 'A',
apex_json.get_varchar2('option_a'), 'Y' );
Repeat the same process for options B, C, and D.
Real-Time Scenario
Suppose an HR team needs 500 SQL interview questions.
Without AI:
- Questions are created manually.
- Answer options are entered manually.
- Correct answers are maintained manually.
- Considerable time is spent creating and reviewing questions.
With this solution:
1 Select SQL.
2 Click Generate Question.
3 Oracle APEX AI generates a new question.
4 Answer options are shuffled automatically.
5 The correct answer is updated.
6 Click Add.
7 The question and options are saved automatically in the database.
The same process can be repeated for Java, Python, HTML, or any other skill without changing the application logic.
Conclusion:
Oracle APEX Generative AI simplifies interview question generation by automating the creation of questions based on the selected skill. By combining AI with PL/SQL and the APEX_JSON package, we can generate, randomize, and store questions efficiently while preserving the correct answer. This solution reduces manual effort, improves assessment quality, and provides a scalable approach for building reusable question banks.
