Author(s): Sarfaraz Merchant

Originally published on Towards AI.

In Part 1 of this series Guide to Your OpenRouter API Key, we grabbed our “Universal Key” from OpenRouter. Today, we are going to put that key to work.

Prerequisite:

• Must Have API Key from Previous Blog.
• Must be Having an APEX instance to Practice this demo. (if you are new to Oracle Apex then create your free workspace now :Get started with APEX.

our Target:

we will create an HRMS Application which will be having a Dashboard with charts and 4 different types of pages where we can manage the Employees, department and its salary.

If you have explored the new Generative AI Services in Oracle APEX, you probably noticed that the default options (OCI genrative AI Service, Cohere, and OpenAI) usually require a paid subscription or a complex cloud setup.

Here is the secret: Since OpenRouter uses an “OpenAI-compatible” API format, we can use a Free AI Model.

Why this matters:

• Zero Cost: No credit card required.
• Standard Learning: You learn the exact same skills as if you were using paid OCI or OpenAI services.
• Flexibility: You can switch between Llama 3, Gemini, or Mistral without changing your APEX settings.

Step 1: Configure Free AI Model in Oracle APEX

Before we build an app, we need to tell APEX where to send its AI requests.

1. Workspace Utilities: Log in to your workspace and navigate to App Builder > Workspace Utilities > Generative AI.

2. Create Service: Click Create.

3. Select Provider: Choose OpenAI. (Don’t worry, we are going to point it to OpenRouter).

4. Configuration:-:

• Name: OpenRouter_Free_AI (You can Name ANything)
• Base URL: https://openrouter.ai/api/v1
• API Key: Paste the key you created in Blog #1.
• AI Model: In the “Model” field, type the name of a free model from OpenRouter, such as “openai/gpt-oss-120b:free”.
• Click on Test Connection, you will see a connection sucessfull Message.

Note: Toggel the Use By App Builder button

Step 2: Create Your AI-Powered Data Model for Application

Now for the magic. We are going to let APEX build an Data Model for us using the service we just created.

Assume that we want to create basic HRMS System Application, so we will prompt AI to generate a Data Model with Sample data for us to have a backbone of our Application.

1. Create Data Model : Go to SQL WorkShop> Create Data Model Using AI.

Sample Prompt:
“Act as a Senior Database Architect. Design a clean, normalized HRMS Data Model using the prefix HR_.

Include the following modules and table structures:

Core HR: Tables for DEPARTMENTS (ID, Name, Location) and EMPLOYEES (ID, First_Name, Last_Name, Email, Hire_Date, Job_ID, and Manager_ID).

Leave Management: A table for LEAVE_REQUESTS linked to Employees with columns for Request_ID, Type (Sick, Vacation, Personal), Start_Date, End_Date, and Status (Pending, Approved, Rejected).

Payroll: A table for SALARY_HISTORY to track Employee_ID, Effective_Date, and Salary_Amount.

Requirements:

Generate 10 rows of realistic sample data for every table.

Include Audit Columns for every table: Created_By (default to APP_USER) and Created_Date (default to local timestamp).

Ensure all Primary Keys and Foreign Keys are explicitly defined.

Output the result as a ready-to-run Oracle SQL Script.”

2. Click on Create SQl Script and in Script Editor Name the Script and Click on Run Button.

Note: there is a possibility of getting Errors While Running this Script, i faced issues of “unknown statements”.
The Best way to resolve this is to provide this SQL Script to any of the AI Models (Gemini,ChatGPT,Claude), get the corrected version and try it will get Execute sucessfully.

3. Click on Create App button then Create Application

This Step is required to make an entry of newly created tables in metadeta. (this Application can be deleted on later stage)

Go Back to App Builder as Now we will be creating the Application Using AI.

Step 3: Create Application Using Genrative AI

1. Go to App Builde->Create-> Create Application using Genrative AI.

Prompt the AI: Tell the assistant what you want to build.

“Build a professional HRMS Portal using my existing tables: HR_DEPARTMENTS_1, HR_EMPLOYEES_1, HR_LEAVE_REQUESTS_1, and HR_SALARY_HISTORY_1.

Key Requirements:

Executive Dashboard: Create a primary dashboard with a Pie Chart showing ‘Employees per Department’ and a Bar Chart showing ‘Leave Requests by Status’.

Staff Directory: Create an Interactive Report for HR_EMPLOYEES_1 with a Faceted Search to filter by Department and Hire Date.

Leave Management: Add a Calendar View based on HR_LEAVE_REQUESTS_1 using the Start_Date and End_Date columns.

Payroll Insight: Create an Interactive Grid for HR_SALARY_HISTORY_1 so I can view and update salary records quickly.

Forms: Ensure every report has a modal form for adding or editing records in the existing tables.

Please use the existing columns and relationships to build the application logic.”

2. Click on Create Application and you will see a window where the Name is given Automatically and the Pages created as we have prompted.

3. Click on Run Application to see the Application in Action.

Conclusion

Building this HRMS portal from scratch proved that with the right data model and a ‘Universal AI Key,’ the possibilities in Oracle APEX are endless.

What are you building with AI in APEX? I’d love to hear about your challenges or successes in the comments below. If you’re stuck on a specific SQL or AI configuration, let’s solve it together!

If this helped you, please share it with your network!

Next Up: In Part 3, we’ll move beyond standard pages and build a custom AI Assistant that can actually ‘talk’ to this HRMS data.”

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI