Introduction/ Issue:
This is a walkthrough of the mini project that was built end to end in Oracle APEX – an ATS Resume Builder. It takes a user through a guided, multi-step wizard that collects their profile (education, experience, skills, certifications, socials), uses a locally-hosted AI model through Ollama to write the About Me section and resume content, renders the result into a downloadable resume, and finally scores that resume against a chosen job role the same way an Applicant Tracking System would. The app also has Google SSO and a Razorpay paywall in front of the ATS Score page, but that part is covered in a separate post – this one is about the wizard, the data model, and how the AI and scoring pieces actually work.
Why we need to do / Cause of the issue:
Two problems come up constantly when someone tries to get a resume through an Applicant Tracking System: writing a strong, concise About Me / summary section is genuinely hard for most people, and there’s no way to know in advance whether a resume will even get past the automated keyword screening most companies use before a human ever sees it. Most tools that solve this either want a subscription to a cloud AI provider, or don’t give any visibility into why a resume scored the way it did.
The goal for this project was to solve both in one guided flow: walk the user through entering their details step by step instead of one long form, generate the written content for them using AI instead of a blank text box, and then actually show a scored breakdown against a specific job role – not just a single number, but where the points came from and what’s missing. Running the AI model locally through Ollama also meant no per-generation cost and no resume data leaving the machine by default while this was being built and tested.
Preview of the app:
How do we solve:
1. The Wizard Flow
The application is built as a sequence of APEX pages rather than one long form, so the user only sees a handful of fields at a time and always knows how far through the process they are. The core steps, in order:
Step Page What it collects / does
1 Fresher or Experienced Branches the rest of the wizard based on whether the user has prior work experience, plus profile photo upload.
2 Personal Details Father’s name, preferred language, alternate email, primary and secondary phone numbers.
3 Education School and college history entered via an interactive grid (institution, qualification, year).
4 Skills & Certifications Skills list and certifications with the awarding body, also grid-based entry.
5 Socials LinkedIn, GitHub, blog, and other profile links, plus the first AI-generated About Me draft.
6 Experience Work history (role, company, dates, outcomes) and a refined AI-generated About Me.
7 Resume Template Template selection, live preview, and PDF generation (base64 stored on the page).
8 ATS Score Job role selection and the scored breakdown against that role.
Each step writes into its own set of tables as soon as the user moves forward, rather than holding everything in session state until a final submit – so nothing is lost if the wizard is abandoned partway through.
2. Generating the About Me Section with a Local AI Model
Rather than leaving the About Me field blank for the user to fill in, a page process pulls everything entered so far – name, work experience, education, skills – and builds a single prompt asking for a first-person, concise summary. The build step loops over the collected PL/SQL tables and concatenates them into readable strings before the prompt is assembled:
l_prompt :=
‘Write a professional and warm About Me section (max 100 words) for a
resume/portfolio. Write in first person. No bullet points.’ || CHR(10)
|| ‘Name: ‘ || l_user.USERNAME || CHR(10)
|| ‘Experience: ‘ || NVL(l_exp_str, ‘Not specified’) || CHR(10)
|| ‘Skills: ‘ || NVL(RTRIM(l_skills_str, ‘, ‘), ‘Not specified’);
l_ai_response := APEX_AI.GENERATE(
p_prompt => l_prompt,
p_service_static_id => ‘local_ollama’
);
APEX_UTIL.SET_SESSION_STATE(‘P15_ABOUT_ME’, l_ai_response);
The p_service_static_id points at the Generative AI Service registered against a local Ollama instance, exposed through a Cloudflare tunnel and running the qwen2.5-coder:1.5b model – the same local-AI setup covered in a separate post. The relevant part for this project is that APEX_AI.GENERATE doesn’t care whether the service behind it is OpenAI, Cohere, or a model running on my own laptop – swapping providers is a one-line change to the static ID, not a rewrite of the process.
Every AI call is wrapped in an exception handler that writes the SQLCODE and SQLERRM into COM_ERROR_LOG before falling back to an inline error message on the page, so a slow or unreachable local model degrades gracefully instead of breaking the wizard.
3. Resume Rendering
Once the profile is complete, the Resume Template step lets the user pick from a small set of layouts. The selected template is rendered to HTML on the page, and a PDF is generated and held as base64 (P31_PDF_BASE64) so it can be previewed inline through a PDF viewer region and downloaded or emailed without a round trip to disk storage.
4.The ATS Scoring Engine
This is the part that actually answers ‘would this resume get past an ATS’. On the ATS Score page, the user picks a target job role from a list (e.g. Junior Java Developer, Senior APEX Developer), and a button click runs P_CALCULATE_ATS_SCORE(:G_USER_ID, :P52_JOB_ROLE) – a PL/SQL procedure that compares the stored profile against the expectations for that role and returns a full breakdown rather than a single number:
Score component What it reflects
Skills score How many of the role’s expected skills are present in the user’s stored skills list.
Experience score Relevance and depth of the stored work history against the role.
Certifications score Whether relevant certifications for the role are on file.
Keyword score Overlap between resume content and the keywords an ATS would scan for.
Missing keywords The specific keywords the role expects that the profile doesn’t currently cover.
AI recommendations AI-generated suggestions for closing the gaps, using the same local Ollama service.
Final ATS score The combined score surfaced to the user on the results page.
The below is the procedure code that is written to get the AI response from Ollama which afterwards is set in the session for the respective variables.
SELECT NVL(LISTAGG(SKILL, ‘, ‘) WITHIN GROUP (ORDER BY SKILL), ‘None’) INTO l_skills_list FROM COM_SKILLS WHERE USER_ID = p_user_id;
SELECT NVL(LISTAGG(ROLE || ‘ at ‘ || COMPANY_NAME, ‘; ‘) WITHIN GROUP (ORDER BY EXP_START DESC), ‘None’) INTO l_exp_list FROM COM_EXP WHERE USER_ID = p_user_id;
SELECT NVL(LISTAGG(CERTIFICATE_NAME, ‘, ‘) WITHIN GROUP (ORDER BY GOT_AT DESC), ‘None’) INTO l_cert_list FROM COM_CERTIF WHERE USER_ID = p_user_id;
l_prompt :=
‘You are an ATS scoring engine. Analyze the candidate profile and return ONLY valid JSON with no extra text, no markdown, no explanation.’ || CHR(10) ||
‘Skills: ‘ || l_skills_list || CHR(10) ||
‘Experience: ‘ || l_exp_list || CHR(10) ||
‘Certifications: ‘ || l_cert_list || CHR(10) ||
‘Return this exact JSON structure:’ || CHR(10) ||
‘{‘ ||
‘”skills_score”: <number 0-100>,’ ||
‘”skills_feedback”: “<2 sentences: assessment>”,’ ||
‘”exp_score”: <number 0-100>,’ ||
‘”exp_feedback”: “<2 sentences: assessment>”,’ ||
‘”cert_score”: <number 0-100>,’ ||
‘”cert_feedback”: “<2 sentences: assessment>”,’ ||
‘”keywords_score”: <number 0-100>,’ ||
‘”keywords_feedback”: “<2 sentences: assessment>”,’ ||
‘”missing_keywords”: “<comma separated keywords>”,’ ||
‘”final_score”: <weighted average 0-100>,’ ||
‘”recommendations”: “<3 actionable sentences max>”‘ ||
‘}’;
l_ai_response := APEX_AI.GENERATE(
p_prompt => l_prompt,
p_service_static_id => ‘local_ollama’
);
l_json := l_ai_response;
l_skills_score := JSON_VALUE(l_json, ‘$.skills_score’);
l_skills_feedback := JSON_VALUE(l_json, ‘$.skills_feedback’);
l_exp_score := JSON_VALUE(l_json, ‘$.exp_score’);
l_exp_feedback := JSON_VALUE(l_json, ‘$.exp_feedback’);
l_cert_score := JSON_VALUE(l_json, ‘$.cert_score’);
l_cert_feedback := JSON_VALUE(l_json, ‘$.cert_feedback’);
l_keywords_score := JSON_VALUE(l_json, ‘$.keywords_score’);
l_keywords_feedback := JSON_VALUE(l_json, ‘$.keywords_feedback’);
l_missing_keywords := JSON_VALUE(l_json, ‘$.missing_keywords’);
l_final_score := JSON_VALUE(l_json, ‘$.final_score’);
l_recommendations := JSON_VALUE(l_json, ‘$.recommendations’);
APEX_UTIL.SET_SESSION_STATE(‘P52_BREAKDOWN_SKILLS_SCORE’, l_skills_score);
APEX_UTIL.SET_SESSION_STATE(‘P52_BREAKDOWN_SKILLS’, l_skills_feedback);
APEX_UTIL.SET_SESSION_STATE(‘P52_BREAKDOWN_EXP_SCORE’, l_exp_score);
APEX_UTIL.SET_SESSION_STATE(‘P52_BREAKDOWN_EXP’, l_exp_feedback);
APEX_UTIL.SET_SESSION_STATE(‘P52_BREAKDOWN_CERT_SCORE’, l_cert_score);
APEX_UTIL.SET_SESSION_STATE(‘P52_BREAKDOWN_CERT’, l_cert_feedback);
APEX_UTIL.SET_SESSION_STATE(‘P52_BREAKDOWN_KEYWORDS_SCORE’, l_keywords_score);
APEX_UTIL.SET_SESSION_STATE(‘P52_BREAKDOWN_KEYWORDS’, l_keywords_feedback);
APEX_UTIL.SET_SESSION_STATE(‘P52_MISSING_KEYWORDS’, l_missing_keywords);
APEX_UTIL.SET_SESSION_STATE(‘P52_FINAL_ATS_SCORE’, l_final_score);
APEX_UTIL.SET_SESSION_STATE(‘P52_AI_RECOMMENDATIONS’, l_recommendations);
5. Where SSO and Payment Fit In
The ATS Score page sits behind a paywall – the user signs in with their Google account and pays a small access fee through Razorpay before the score is unlocked. That login and payment wiring is its own topic and is written up separately; the short version here is just that it gates the last step of this wizard rather than being part of the scoring logic itself.
Conclusion:
End to end, the project takes someone from a blank profile to a formatted resume and a concrete, role-specific ATS score, with the AI-written content generated by a model running entirely on local hardware rather than a paid API. Structuring it as a step-by-step wizard instead of one long form made the data model easier to reason about too – each step owns its own tables, so the profile builds up incrementally and nothing depends on the user finishing everything in one sitting. The scoring breakdown being transparent (skills, experience, certifications, keywords, and what’s missing) rather than a single opaque number was the main goal, and it’s also what made the AI recommendations step feel useful instead of just decorative.
