Build a Product Catalog App in APEX

Build a product catalog app with SQL, PL/SQL validation, and a live REST API.

Introduction

30 Second Summary

Every online store, warehouse system, and internal tool runs on a product catalog. It is the backbone that connects what a business sells to how customers find and buy it.

In this project, you will build a Product Catalog application in Oracle APEX that lets you browse, add, and search products. You will design the database, build a full-featured report with search and filtering, add server-side validation, and expose your data through a live REST API.

What You'll Build

By the end, you can open your app in a browser, search for products by name or category, add new products through a form that rejects bad data, and hit a public URL that returns your product data as JSON.

By the end of this project, you'll have:

  • A working Interactive Report where you can sort, filter, and export your product data with a single click.
  • A data entry form with PL/SQL validation that catches invalid data (like negative prices) and shows a friendly error message instead of letting bad records into your database.
  • A live REST API endpoint that returns your product data as JSON when you open its URL in any browser.
  • Secret Mission: Generate 100,000 rows and optimize a slow report using indexing and pagination tuning to see a measurable speed improvement.

Are there any prerequisites?

None. You only need a web browser and a free Oracle APEX workspace (no credit card, no installation required). Step 1 walks you through signing in and running your first query.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Your Workspace and Write Your First SQL

You're about to build a Product Catalog app entirely inside Oracle APEX. Before writing any database or application code, you need to make sure your workspace is accessible and its tools are working.

In this step, you'll sign into your free workspace, locate the key development tools, and run your first SQL query to confirm everything is connected.

In this step, get ready to:
  • Sign in to your free Oracle APEX workspace.
  • Run your first SQL query in SQL Commands.
  • Confirm Object Browser loads and is ready for your tables.
Sign in to your Oracle APEX workspace

The Oracle APEX workspace is your development environment. It runs entirely in the browser and gives you tools to write SQL, build applications, and manage database objects.

  • Open your browser and navigate to oracleapex.com.
  • On the Sign In page, enter your Workspace name, Username, and Password.
  • Click Sign In.

You should now see the Workspace home page with App Builder, SQL Workshop, and Gallery displayed as large icons.

Don't have a workspace yet?

Go to oracleapex.com and click Get Started to request a free workspace. No credit card or installation is required.

What are these tools?

App Builder is where you create and manage APEX applications. SQL Workshop is where you write SQL, browse database objects, and manage RESTful services.

Gallery contains pre-built sample applications you can install for reference. You'll spend most of your time in SQL Workshop and App Builder for this project.

Run your first SQL query in SQL Commands

SQL Commands is the tool inside SQL Workshop where you write and execute SQL statements directly against the database. This is where you'll create tables, insert data, and test queries throughout the project.

  • From the Workspace home page, click SQL Workshop.
  • Click SQL Commands.

You should see a text editor area with a Run button above it. This is where you'll type SQL.

Before you run your first query, what do you think a query that selects a string value will return?

  • Type the following query into the editor area and click Run:
SELECT 'Hello APEX' AS greeting FROM dual

You should see the text Hello APEX returned in the results panel below the editor.

What does this query do?

dual is a built-in Oracle table with exactly one row. It exists so you can run SELECT statements that evaluate expressions without needing a real table.

AS greeting gives the output column a readable name. This query simply proves your workspace can execute SQL and return results.

Not seeing any results?

Make sure you typed the query exactly as shown, including the single quotes around 'Hello APEX'. Oracle SQL uses single quotes for string literals.

If you see a session timeout error, sign in again at oracleapex.com and navigate back to SQL Workshop then SQL Commands.

help me debug my SQL Commands query in Oracle APEX

Confirm Object Browser loads

Object Browser is the visual tool for viewing your database tables, columns, and constraints. After you create tables in the next step, this is where you'll verify they exist.

  • Click SQL Workshop in the top navigation bar.
  • Click Object Browser.

The Object Browser page loads. It may show an empty list or a message indicating no objects exist yet. That is expected because you have not created any tables.

Why is Object Browser empty?

Your workspace has a fresh database schema with no tables, views, or other objects in it yet. In the next step, you'll create the CATEGORIES and PRODUCTS tables, and they'll appear here automatically.

Your workspace is set up and your SQL tools are working. Next up, you'll design and build the database tables that power your Product Catalog app.

Design and Build Your Database

In Step 1, you confirmed that SQL Commands and Object Browser are ready to go. Now it is time to build the foundation your app will sit on.

Every Oracle APEX application is backed by database tables. Before you can build any screens or forms, you need a proper relational database schema with constraints and relationships that protect your data from day one.

In this step, get ready to:
  • Create CATEGORIES and PRODUCTS tables with constraints and sample data.
  • Verify the table relationship with a JOIN query.
  • Create a reusable view that joins both tables for the app to use.
Create the CATEGORIES and PRODUCTS tables

Your product catalog needs two tables. A CATEGORIES table holds the category names, and a PRODUCTS table holds individual products that each belong to one category.

  • Return to SQL Commands by clicking SQL Workshop, then SQL Commands.
  • Create the CATEGORIES table and insert sample data by pasting the following SQL into the command editor and clicking Run:
-- Create CATEGORIES table
CREATE TABLE categories (
    category_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_name VARCHAR2(100) NOT NULL
);

You should see "Table created" in the Results pane.

  • Clear the editor, then paste and run the following INSERT statements one at a time (or select all and click Run):
-- Insert sample categories
INSERT INTO categories (category_name) VALUES ('Electronics');
INSERT INTO categories (category_name) VALUES ('Clothing');
INSERT INTO categories (category_name) VALUES ('Food');

What does this code do?

The CATEGORIES table uses NUMBER GENERATED ALWAYS AS IDENTITY to auto-generate a unique ID for each row. The NOT NULL constraint prevents anyone from inserting a category without a name.

Each INSERT adds one category. The database assigns category_id values 1, 2, and 3 automatically.

You should see "1 row(s) inserted" after each INSERT statement runs.

Seeing an error on the INSERT statements?

If you get a "table or view does not exist" error, the CREATE TABLE statement did not succeed. Scroll up in your command history and re-run the CREATE TABLE first.

If SQL Commands only executes one statement when you paste multiple, highlight all three INSERT lines, then click Run. Alternatively, run each INSERT individually by placing your cursor on a single statement and pressing Ctrl+Enter (Windows) or Cmd+Enter (macOS).

help me fix this SQL error in APEX SQL Commands

Now create the PRODUCTS table. This table has more columns and includes constraints that enforce data quality.

  • Clear the editor, paste the following SQL, and click Run:
-- Create PRODUCTS table with constraints
CREATE TABLE products (
    product_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR2(200) NOT NULL,
    unit_price   NUMBER(10,2) NOT NULL,
    category_id  NUMBER NOT NULL,
    created_date DATE DEFAULT SYSDATE,
    CONSTRAINT fk_product_category FOREIGN KEY (category_id)
        REFERENCES categories (category_id),
    CONSTRAINT chk_price_positive CHECK (unit_price > 0)
);

What do these constraints do?

  • fk_product_category is a foreign key that links each product to an existing category. You cannot insert a product with a category_id that does not exist in the CATEGORIES table.
  • chk_price_positive is a CHECK constraint that rejects any price of zero or below. The database enforces this rule automatically.
  • created_date DATE DEFAULT SYSDATE timestamps each product with the current date if you do not supply one.

You should see "Table created" in the Results pane.

  • Insert six sample products and commit the data by clearing the editor, pasting the following, and clicking Run:
-- Insert sample products across all three categories
INSERT INTO products (product_name, unit_price, category_id) VALUES ('Wireless Mouse', 29.99, 1);
INSERT INTO products (product_name, unit_price, category_id) VALUES ('USB-C Cable', 12.50, 1);
INSERT INTO products (product_name, unit_price, category_id) VALUES ('Cotton T-Shirt', 19.99, 2);
INSERT INTO products (product_name, unit_price, category_id) VALUES ('Running Shoes', 89.00, 2);
INSERT INTO products (product_name, unit_price, category_id) VALUES ('Organic Coffee Beans', 15.75, 3);
INSERT INTO products (product_name, unit_price, category_id) VALUES ('Dark Chocolate Bar', 4.99, 3);

COMMIT;

Why COMMIT?

Oracle databases use transactions. Your INSERT statements are not permanently saved until you issue a COMMIT. Without it, your data would disappear if the session ended.

  • Confirm the tables exist by clicking SQL Workshop, then Object Browser.
  • Click PRODUCTS in the object list on the left, then click the Data tab.

You should see 6 rows listed with product names, prices, category IDs, and dates.

Don't see PRODUCTS in Object Browser?

Click the refresh icon in Object Browser to reload the object list. If the table still does not appear, return to SQL Commands and confirm the CREATE TABLE statement ran without errors by checking your command history.

If you see 0 rows in the Data tab, you likely forgot the COMMIT statement. Return to SQL Commands, run COMMIT;, and refresh Object Browser.

help me find my tables in APEX Object Browser

Verify the relationship with a JOIN query

Object Browser shows the raw table data, but you need to prove the foreign key relationship actually works. A JOIN query pulls data from both tables at once, matching each product with its category name.

  • Switch back to SQL Commands by clicking SQL Workshop, then SQL Commands.
  • Run this JOIN query to see products alongside their category names:
-- Verify the foreign key relationship works
SELECT p.product_name, p.unit_price, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id;

Before you click Run, how many rows do you expect to see returned?

  • Click Run to execute the query.

You should see 6 rows, each showing a product name, its price, and the category name it belongs to (not just the category ID number).

What does this prove?

The JOIN successfully matched every product row to its parent category using the category_id foreign key. If a product had an invalid category_id, the foreign key constraint would have blocked the INSERT entirely.

Seeing fewer than 6 rows?

If you see 0 rows, the INSERT statements did not commit. Run COMMIT; and try the JOIN again.

If you see 3 rows, some INSERTs for the PRODUCTS table may have failed. Check that you inserted all 6 products by running SELECT COUNT(*) FROM products; and verifying the result is 6.

help me debug my JOIN query results

Create a reusable view for the application

Your app will need the product-plus-category-name combination on multiple pages. Instead of writing the same JOIN query everywhere, you can create a view that encapsulates it. The app queries the view as if it were a table.

  • Create the PRODUCTS_V view by pasting the following into SQL Commands and clicking Run:
-- Create view joining products with categories
CREATE OR REPLACE VIEW products_v AS
SELECT
    p.product_id,
    p.product_name,
    p.unit_price,
    p.category_id,
    c.category_name,
    p.created_date
FROM products p
JOIN categories c ON p.category_id = c.category_id;

Why use a view?

A view is a saved query. When the app reads from PRODUCTS_V, Oracle runs the JOIN behind the scenes and returns the combined result. If you ever add columns or change the join logic, you update the view in one place rather than hunting through every page.

You should see "View created" in the Results pane.

  • Verify the view returns the same 6 rows by running:
SELECT * FROM products_v;

You should see 6 rows with all columns: product_id, product_name, unit_price, category_id, category_name, and created_date.

View created but no data returned?

If SELECT * FROM products_v returns 0 rows, your underlying tables are empty. Run SELECT COUNT(*) FROM products; to confirm data exists. If not, re-run the INSERT statements and COMMIT.

If you see "ORA-00942: table or view does not exist", check the spelling. The view is named products_v (lowercase with an underscore before the v).

help me fix my PRODUCTS_V view

✔️ Awesome, I've got everything!

Great. Double check you have the CATEGORIES table (3 rows), the PRODUCTS table (6 rows), and the PRODUCTS_V view all confirmed in Object Browser.

ⓧ I'd like to double check the full code

Scroll up and confirm you have run each of these SQL blocks in order:

  • CREATE TABLE categories (with the IDENTITY primary key and NOT NULL constraint).
  • Three INSERT INTO categories statements (Electronics, Clothing, Food).
  • CREATE TABLE products (with the IDENTITY primary key, foreign key, and CHECK constraint).
  • Six INSERT INTO products statements (Wireless Mouse through Dark Chocolate Bar) followed by COMMIT.
  • CREATE OR REPLACE VIEW products_v (joining products with categories on category_id).

To confirm everything is in place, run SELECT * FROM products_v; and verify you see all 6 products with their category names.

Your database is built and verified. Next up, you will turn these tables into a working application with a searchable report and data entry form.

Build the APEX Application with an Interactive Report

You have a solid relational schema with categories, products, and a view that joins them together. But right now the only way to see your data is by writing SQL queries in the command editor.

In this step, you will build an actual application on top of your tables. An Interactive Report gives you a full-featured data browser with built-in search, sort, filter, and export capabilities. You get all of that with almost no configuration.

In this step, get ready to:
  • Create a new APEX application using the Create Application wizard.
  • Run the application and verify sorting, filtering, and searching work.
  • Add a Form page so users can create and edit products.
Create a Product Catalog application

The Create Application wizard builds a complete multi-page application in one pass. You point it at your data source and APEX generates the pages, navigation, and authentication for you.

  • On the Workspace home page, click App Builder.
  • Click Create to launch the Create Application wizard.
  • In the Name field, type Product Catalog.

Now you need to add a page to the application. The wizard starts you with a Home page by default. You will add an Interactive Report page that pulls data from your view.

  • Click Add Page.
  • Select Interactive Report as the page type.
  • Set the Page Name to Products.
  • In the Table or View field, select PRODUCTS_V.
  • Click Add Page to confirm.

You should see "Products" listed as a page in the wizard's page list. Now create the entire application.

  • Click Create Application at the bottom of the wizard.

APEX generates your application. You land on the Application home page in App Builder, showing all the pages that were created.

What just happened?

The Create Application wizard built a full application with authentication, a navigation menu, a Home page, and your Products Interactive Report page. All of this was generated from the choices you made in the wizard.

The Interactive Report is pointed at your PRODUCTS_V view, so it displays product names alongside their category names without you writing any extra join logic in the page.

Run the application and test the Interactive Report

Before you run it, what do you expect to see? You pointed the report at a view with 6 product rows. Think about what the page should display.

  • Click Run Application (the play icon in the toolbar).
  • A sign-in page appears. Enter your workspace username and password (the same credentials you use to sign into the APEX workspace).
  • Click Sign In.

Your browser now shows the running application. Click Products in the navigation menu on the left to open the Interactive Report page.

You should see all 6 product rows displayed in a table with columns for product name, unit price, category name, and created date.

  • Click any column header (such as Unit Price) to sort the data.
  • Type Coffee in the search bar at the top of the report and press Enter. The report filters to show only the Organic Coffee Beans row.
  • Click the X on the filter tag to clear the search and see all rows again.

What is an Interactive Report?

An Interactive Report is an APEX page component that gives end users full control over how they view data. Sorting, filtering, searching, column reordering, and CSV export all come built in.

You did not write any JavaScript or build any UI components. APEX generated the entire interface from your view definition.

Don't see your 6 products?

Make sure you selected PRODUCTS_V (the view) as the data source, not the PRODUCTS table directly. The view includes category names. If you used the table, you will see category_id numbers instead of names.

If the report shows zero rows, return to SQL Workshop and run SELECT * FROM products_v to confirm your data and view still exist.

help me debug why my Interactive Report shows no data

Add a form page for creating and editing products

The report lets you browse products, but there is no way to add or edit them from the application. A Form page solves this by giving users a data entry screen linked directly to the PRODUCTS table.

  • Return to the App Builder. If you still have the running application open, click the developer toolbar at the bottom of the page and select Application xxxxx (where xxxxx is your application number) to go back to the editor.
  • Click Create Page.
  • Select Form as the page type.
  • Set the Page Name to Product Form.
  • Set the Table or View to PRODUCTS.

Why PRODUCTS and not PRODUCTS_V?

The form writes data (INSERT, UPDATE, DELETE), so it must point at a real table. The view is read-only and does not support direct DML operations. Reports read from the view. Forms write to the table.

  • In the Navigation section, look for an option to link this form to your existing Interactive Report page. Select your Products report page as the source so users can click a row to open the form.
  • Click Create Page to finish.

Now verify the form works. Run the application again and navigate to the Products report.

  • Click Run Application (the play icon).
  • Navigate to the Products page.
  • Click the edit icon (pencil) next to any product row. The form page opens showing that product's details.
  • Click Cancel to return to the report without making changes.

You now have a working application with a browsable report and a data entry form. The report reads from your view, and the form writes directly to your PRODUCTS table.

No edit icon on the report rows?

The link column only appears when the form page is properly associated with the Interactive Report. Return to App Builder, edit the Products report page, and check that the report's Link Column attribute points to your form page.

help me link my Interactive Report to the Form page

Your Product Catalog app is live and functional. You can browse, sort, filter, and export products. You can also click into any row to edit it. Next up, you will add server-side validation so the form rejects bad data before it reaches the database.

Add PL/SQL Server-Side Validation

Your Product Catalog app is running with a full Interactive Report and a data entry form. Users can add new products through the form and see them appear in the report.

There is a problem. The form currently accepts any submission attempt, and when the database constraint catches bad data, the user sees a cryptic Oracle error instead of a helpful message. Server-side PL/SQL validation ensures data integrity and gives users clear feedback regardless of how data enters the system.

In this step, get ready to:
  • Discover the problem with raw database constraint errors.
  • Add PL/SQL validation expressions to the form page.
  • Test that both validations reject bad data with friendly messages.
Try submitting bad data without validation

Before adding any validation, you need to see what happens when a user enters invalid data. Your PRODUCTS table has a CHECK constraint that rejects prices of zero or below. But what does the user actually experience when they hit that constraint?

  • Run your Product Catalog application by clicking Run Application from the App Builder home page (the green play button).
  • Sign in with your workspace credentials if prompted.
  • Navigate to the Products page (the Interactive Report).
  • Click the edit icon (pencil) on any row to open the form, or click Create to add a new product.
  • Enter Test Bad Product as the product name.
  • Enter -5 as the unit price.
  • Select any category from the dropdown.

Before you click the save button, what do you think will happen? Will the record save successfully, or will you see an error?

  • Click Create (or Apply Changes) to submit the form.

You see an error. The database CHECK constraint blocked the insert, but the message is a raw Oracle error referencing constraint names and table internals. This is confusing and unhelpful for a real user.

Why is this a problem?

Database constraints are your last line of defense. They protect data integrity, but they were never designed to communicate with end users. A production application needs server-side validation that catches bad data BEFORE it reaches the database and returns a clear, human-readable message.

PL/SQL expressions in APEX run on the server when the form is submitted. They evaluate to TRUE (valid) or FALSE (invalid). If FALSE, APEX shows your custom error message and prevents the save.

Add the price validation

Now you will add a PL/SQL Expression validation that checks the price before the data ever reaches the database.

  • Return to the App Builder tab in your browser.
  • Click on your Product Catalog application.
  • Click on the form page (the page linked to the PRODUCTS table) to open it in Page Designer.

How do I find my form page?

In App Builder, your application pages are listed with their names and page numbers. The form page is the one you created in the previous step that is linked to the PRODUCTS table. It might be named something like "Product" or "Products" with a page type of "Form".

  • In Page Designer, click the Processing tab in the left panel. This is where validations, computations, and processes live.
  • Right-click Validating (under the Processing tab) and select Create Validation.

The Property Editor on the right side now shows attributes for your new validation.

  • Under the Validation group in the Property Editor, set Type to PL/SQL Expression.
  • In the PL/SQL Expression field that appears, enter the following expression:
:P_UNIT_PRICE > 0
  • Under the Error group, set Error Message to:
Price must be greater than zero

What about the page item name?

In APEX, page items are named with a prefix that includes the page number. Your actual item might be called P3_UNIT_PRICE or P4_UNIT_PRICE depending on which page number your form received.

Look at the items listed under your form region in the Rendering tab to find the exact name. Replace the P prefix in the expression with your actual page prefix (for example, :P3_UNIT_PRICE > 0).

  • Click Save in the top right corner of Page Designer.

Your first validation is saved. Now add the second one for the product name.

  • Right-click Validating again and select Create Validation.
  • Set Type to PL/SQL Expression.
  • In the PL/SQL Expression field, enter:
:P_PRODUCT_NAME IS NOT NULL
  • Under the Error group, set Error Message to:
Product name is required
  • Click Save in the top right corner.

What do these expressions do?

Each expression is evaluated when the form is submitted. If the expression returns TRUE, the validation passes and the form processes normally. If it returns FALSE, APEX stops the submission and displays your error message inline on the page.

The expression :P_UNIT_PRICE > 0 checks that the price is a positive number. The expression :P_PRODUCT_NAME IS NOT NULL checks that the product name is not left blank. Both use the colon prefix (:) to reference APEX page item values.

Don't see the Validating node?

Make sure you are on the Processing tab in the left panel of Page Designer, not the Rendering or Dynamic Actions tab. The Processing tab shows validations, processes, and branches that execute when the page is submitted.

help me find the Processing tab and Validating node in APEX Page Designer

Test both validations

With both validations saved, it is time to confirm they work. You will submit the form with bad data and verify that the friendly error messages appear instead of cryptic database errors.

  • Click Run (the green play button) in Page Designer to launch the form page.
  • On the form, enter Test Negative Price as the product name.
  • Enter -5 as the unit price.
  • Select any category.

What do you expect to see this time when you submit? Think about whether the database error or your custom message will appear.

  • Click Create to submit the form.

You see the message "Price must be greater than zero" displayed inline on the page. The record is NOT saved. Your PL/SQL validation caught the bad data before it reached the database.

  • Now test the product name validation. Clear the product name field so it is blank.
  • Enter 10 as the unit price (a valid value this time).
  • Click Create to submit.

You see the message "Product name is required" and the record does NOT save.

Still seeing a database error instead of your message?

Double-check that you used the correct page item name in your expression. Switch to the Rendering tab in Page Designer and expand your form region to see the exact item names (they include the page number, like P3_UNIT_PRICE). Your validation expression must use the same name with the colon prefix.

Also confirm you selected PL/SQL Expression as the validation type, not a different type like "SQL Expression" or "Function Body".

help me debug why my PL/SQL validation is not showing a custom error message in Oracle APEX

Your form now rejects bad data with clear, human-readable messages. Next up, you will expose your product data as a REST API endpoint so external systems can access it over HTTP as JSON.

Expose a REST API for Your Product Data

Your APEX application looks great for anyone who can sign in and use the browser interface. But external systems (mobile apps, dashboards, other services) cannot sign into your APEX app to get product data.

A REST API endpoint solves this by letting any system fetch your product data over HTTP as JSON. In this step, you will register your schema with ORDS (Oracle REST Data Services), create a REST module, and expose your products as a public JSON endpoint.

In this step, get ready to:
  • Register your workspace schema with ORDS for RESTful access.
  • Create a REST module, resource template, and GET handler.
  • Test the live endpoint in your browser and see JSON product data returned.
Register your schema with ORDS

Before you can create REST endpoints, your workspace schema needs to be registered with ORDS. This one-time registration tells Oracle that your schema is allowed to serve data over HTTP.

  • On the Workspace home page, click SQL Workshop.
  • Click RESTful Services.
  • Click ORDS Based RESTful Services.
  • You will see a prompt saying your schema is not registered with ORDS. Click Register Schema with ORDS.
  • Set Enable RESTful Access to On.
  • Enter products in the Schema Alias field.
  • Set Install Sample Service to Off.
  • Click Save Schema Attributes.

The page reloads and you now see the RESTful Services management interface with no modules listed yet.

What is ORDS?

ORDS (Oracle REST Data Services) is the layer that sits between your Oracle database and the outside world. It translates HTTP requests into SQL queries and returns the results as JSON.

The Schema Alias you entered (products) becomes part of your REST endpoint URL. Setting Install Sample Service to Off keeps your workspace clean by skipping pre-built demo endpoints you do not need.

Don't see the registration prompt?

If the RESTful Services page loads directly without asking you to register, your schema may already be registered. You can proceed to creating the module in the next substep.

help me figure out why my ORDS registration page looks different

Create the REST module and resource template

ORDS organizes REST endpoints in a three-level hierarchy: Module, Template, Handler. You will create the first two levels now.

How does the ORDS hierarchy work?

A Resource Module is the top-level container that defines a base path (like /catalog/). A Resource Template sits inside the module and defines a URI pattern (like products).

A Resource Handler sits inside the template and implements one HTTP method (GET, POST, PUT, or DELETE). Together they form the full endpoint path: base path + URI pattern.

  • Click Create Module on the right pane.
  • Set Module Name to product_catalog.
  • Set Base Path to /catalog/.
  • Set Is Published to On.
  • Set Pagination Size to 25.
  • Click Create Module.

The module page loads showing your new product_catalog module with its base path displayed.

  • Under Resource Templates on the left panel, click Create Template.
  • Set the URI Template to products.
  • Click Create Template.

You now see the products template listed under your module.

Why these specific values?

The Module Name product_catalog is an internal label for organizing your endpoints. The Base Path /catalog/ combined with the URI Template products creates the URL path /catalog/products that callers will hit.

Pagination Size 25 means the endpoint returns results in pages of 25 items. This prevents accidentally dumping thousands of rows in a single response.

Create the GET handler and test your endpoint

The handler is where you define what actually happens when someone hits your endpoint. You will write a SQL query that returns product data from your view.

  • Under the products template, click Create Handler.
  • Set Method to GET.
  • Set Source Type to Collection Query.
  • In the SQL query area, enter the following query:
SELECT product_id, product_name, unit_price, category_name
FROM products_v
ORDER BY product_name
  • Click Create Handler.

What does this query do?

This query pulls from products_v (the view you created in Step 2) which already joins products with their category names. The Collection Query source type automatically formats the SQL results as paginated JSON with no extra code needed.

After creating the handler, the page displays a Full URL for your endpoint. This is the live, public URL that returns your data as JSON.

  • Copy the full REST URL displayed on the handler page.
  • Open a new browser tab and paste the URL into the address bar.

Before you press Enter, what do you think the browser will show?

  • Press Enter to load the URL.

You see your product data returned as JSON directly in the browser. The response includes product names, prices, and category names for all 6 products, ordered alphabetically.

Bookmark your endpoint URL

Save this URL somewhere handy. You can share it with anyone, and they can fetch your product data without needing an APEX account.

Seeing a 404 or empty response?

Make sure Is Published is set to On for your module. If it is Off, the endpoint exists but rejects all requests.

Double-check that the SQL query references products_v (the view) and not products (the table). The view includes the category_name column that the query selects.

help me troubleshoot my ORDS REST endpoint not returning data

✔️ I see JSON data in my browser

Your REST API is live and returning product data. Any system can now fetch your products by hitting this URL.

ⓧ I'd like to double check my setup

Confirm your ORDS configuration matches these values:

  • Module Name: product_catalog.
  • Base Path: /catalog/.
  • Is Published: On.
  • Pagination Size: 25.
  • URI Template: products.
  • Handler Method: GET.
  • Handler Source Type: Collection Query.

The handler SQL should be:

SELECT product_id, product_name, unit_price, category_name
FROM products_v
ORDER BY product_name

Your product data is now accessible to the entire internet through a simple URL. Next up, you will take on the Secret Mission and push your database to its limits with 100,000 rows and performance optimization.

Secret mission

Performance Tuning with 100K Rows

Your report handles 6 rows beautifully. But real databases have millions. In this mission, you will flood your PRODUCTS table with 100,000 rows, watch the Interactive Report slow to a crawl, and then fix it using indexing and pagination tuning.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely on the free Oracle APEX workspace, so there are no ongoing costs.

Resources you used:

  • Product Catalog APEX application (App Builder).
  • PRODUCTS table (100,006 rows, two indexes).
  • CATEGORIES table (3 rows).
  • PRODUCTS_V view.
  • ORDS REST module (product_catalog).
  • Oracle APEX free workspace on oracleapex.com.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the app soon.

  • Your workspace, application, tables, and REST endpoint remain available at no cost.
  • Workspaces without any activity are automatically purged after 60 days. Sign in at least once every 60 days to keep it alive.

Pause - I'll come back to this later

Free up workspace storage while keeping your application structure intact. Remove the 100,000 test rows generated during the Secret Mission so the workspace stays lean.

  • Go to SQL Workshop, then SQL Commands.
  • Run the following statement to remove the generated test data while keeping your original 6 products:
DELETE FROM products WHERE product_id > 6;
COMMIT;
  • Your application, tables, view, indexes, and REST module remain in place for when you return.
  • Remember to sign in at least once every 60 days to prevent the workspace from being automatically purged.

Delete - I don't want to use this again

Remove all project resources and optionally terminate the workspace entirely.

  • Go to App Builder. Click your Product Catalog application.
  • Click the application menu (three dots or gear icon), then select Delete this Application.
  • Confirm the deletion when prompted.

Next, drop the database objects. Go to SQL Workshop, then SQL Commands, and run each statement below:

DROP VIEW products_v;
DROP TABLE products;
DROP TABLE categories;
  • Delete the REST module by going to SQL Workshop, then RESTful Services. Click the product_catalog module, then click Delete Module.

To terminate the workspace entirely and remove all accounts and data:

  • Click the Administration menu in the APEX navigation bar.
  • Select Manage Service, then Make a Service Request.
  • On the Request Service page, select Request Termination and follow the on-screen prompts.
  • This permanently removes all accounts, applications, and data from your workspace.

Nice Work!

Nice Work!

Nice work! You just built a full-stack Product Catalog application in Oracle APEX, complete with relational database design, server-side validation, and a live REST API.

You've learned how to:

  • Built a relational database with properly linked CATEGORIES and PRODUCTS tables, foreign keys, constraints, and a reusable view for clean data access.
  • Created an Interactive Report and data entry form with PL/SQL server-side validation that rejects invalid data with user-friendly error messages.
  • Exposed your product data as a live REST API endpoint returning JSON via ORDS, accessible from any browser.
  • Secret Mission: Generated 100,000 rows of test data, identified performance bottlenecks, and applied indexing and pagination optimization to measurably improve query performance.

Ready to quiz yourself?