Working with Local Data

Enterprise applications thrive on data, and Oracle APEX makes working with any data simple and efficient. While it's also easy to integrate remote data, most APEX applications work primarily with data in local tables.

• Database Concepts Primer
  The Oracle APEX engine and App Builder run inside the Oracle database.
• Working with Sample Data
  While you are learning Oracle APEX, if you don't already have an existing set of database tables to experiment with, you can work initially with built-in sample data.
• Installing a Sample Dataset
  In addition to working with built-in sample data, you can also install additional sample datasets that include multiple tables related to a functional area.
• Importing Data from a File
  The APEX Builder's Data Workshop lets you easily import data from Excel spreadsheets or files in JSON, XML, or Comma-Separated Values (CSV) format.
• Creating Data Models with Quick SQL
  Create tables and relationships quickly by describing your data model in Quick SQL shorthand.
• Creating a Data Model Using Natural Language
  You can create a data model by using natural language to describe what data your application needs to manage.

The Oracle APEX engine and App Builder run inside the Oracle database.

When your applications work with data in the same database, your development activities and applications benefit from zero-latency access. This performance advantage is unique to local database access, since using remote data from another Oracle database or from REST APIs requires network requests.

You create and maintain all the local database objects your application needs using the Object Browser in App Builder. In addition to creating tables for application data storage, you can also create views to easily reuse specific queries by name, define triggers to enforce business logic, organize custom code into packages, and more. The figure shows the Object Browser landing page, with the names of tables, views, a package, and a trigger visible in the navigator on the left.

• Database Tables
  You organize an application's data into tables, each representing a business entity like an employee or a department. Each column in a table corresponds to a property of that entity, like an employee's hire date or a department's location. Every column has a data type that defines the kind of information it holds, like text, numbers, or dates.
• Structured Query Language (SQL)
  While building your Oracle APEX application, you use the industry-standard Structured Query Language (SQL) to specify the data you want to work with in a simple, declarative syntax.
• Database Views, Triggers, and Packages
  While tables store your application data, it's important to understand how views, triggers, and packages complement them.

While you are learning Oracle APEX, if you don't already have an existing set of database tables to experiment with, you can work initially with built-in sample data.

When creating new pages in App Builder, just select Sample Data for the Data Source as shown below.

The built-in sample data defaults to using Products. As shown below, you can use the Property Editor in Page Designer to change to use Employees, Tasks, or Projects data instead.

In addition to working with built-in sample data, you can also install additional sample datasets that include multiple tables related to a functional area.

Each sample dataset contains one or more tables with sample data related to the following use cases:

All sample data is in English. However, when installing the EMP / DEPT dataset, you can also choose Arabic, Chinese, Czech, French, German, Italian, Japanese, Korean, Polish, Russian, or Spanish. Wherever possible, the examples in this guide use the simple EMP and DEPT tables from the Employees and Departments sample dataset.

The APEX Builder's Data Workshop lets you easily import data from Excel spreadsheets or files in JSON, XML, or Comma-Separated Values (CSV) format.

Many enterprise applications start small by importing existing data from an Excel spreadsheet. Migrating the data from a file to a central database ensures a single source of truth that all users can see simultaneously and modify where allowed. You can load data from a file on your computer, or one that resides in the cloud using an object bucket in Oracle Cloud Infrastructure (OCI).

Data Workshop parses the file to be loaded and automatically infers appropriate data types for the columns it identifies. You can load selected columns into an existing table, or into a new table Data Workshop creates. The figure shows loading a spreadsheet with data about New York City public high schools into a new table. The Load Data wizard previews the file's data and lets you configure which columns to load.

Create tables and relationships quickly by describing your data model in Quick SQL shorthand.

An application's data model is the set of tables and relationships that store the information it manages. When you are ready to create a new application, APEX Builder's Quick SQL editor is a productive way to iteratively design its tables. Using a shorthand syntax, you type in the business entities in your data model one per line, and indent the attributes and data types under each entity. You relate tables by identifying the foreign keys.

For example, imagine you need to build an application to manage a conference. The Quick SQL syntax below defines tables for ROOMS, PRESENTERS, SESSIONS, ATTENDEES, and AGENDAS. Quick SQL automatically creates a primary key named ID for each table, so you don't have to list it explicitly. The NAME and TITLE columns store variable-length character data with a maximum length of 100 characters (vc100), while ROOMS has two number columns to store its map coordinates. Each session has a STARTS date (with time) and a number DURATION in minutes. Notice how the /fk or /references annotation identifies a column as a foreign key along with the name of the table it references. In this example, each row in the SESSIONS table has a ROOM_ID for the room in which the session occurs, and a PRESENTER_ID for the person presenting the session. Similarly, each row in the AGENDAS table has a SESSION_ID for the session and a ATTENDEE_ID for the conference-goer who wants to attend it.

rooms
    name vc100
    latitude num
    longitude num

presenters
    name vc100

sessions
    title vc100
    starts date
    duration num
    room_id /fk rooms
    presenter_id /fk presenters

attendees
    name vc100

agendas
    session_id /references sessions
    attendee_id /references attendees

Whenever you pause your typing in the Quick SQL editor, as shown below, the Diagram tab immediately updates its visual representation of the tables, columns, and relationships. At any time you can peek at the SQL tab to see the statements needed to create the new tables. When you are happy with the data model, just click Review and Run to save the SQL script and create your tables.

You can create a data model by using natural language to describe what data your application needs to manage.

As shown below, APEX AI Assistant responds with a proposed data model in Quick SQL notation with suggested tables, columns, data types, and relationships. You can carry on the conversation requesting adjustments if necessary, then click Review Quick SQL to inspect the data model in the Quick SQL diagram.

Based on your prompt, APEX AI Assistant creates a data model like the one shown below. If you notice anything you forgot, you can edit the Quick SQL until you're ready to click Review and Run to save the script and create the tables.

You organize an application's data into tables, each representing a business entity like an employee or a department. Each column in a table corresponds to a property of that entity, like an employee's hire date or a department's location. Every column has a data type that defines the kind of information it holds, like text, numbers, or dates.

A table's primary key column uniquely identifies each row. This key lets you establish relationships between tables. By using it, you can link a row of data in one table to a related row in another. A foreign key is a column in one table that references the primary key value of another, ensuring data consistency and enforcing data integrity.

The figure below shows two tables representing the data for employees and the departments in which they work. The EMPNO column is the EMP table's primary key. The DEPTNO column plays that role in the DEPT table. Both display below with a key icon. The EMP table's DEPTNO column is a foreign key. Its value references the primary key of the DEPT table, indicating the department to which each employee belongs.

The APEX Builder's Quick SQL editor and Create Data Model Using AI wizard simplify creating an initial set of tables for your application, while the Object Browser makes it easy to evolve those tables over time.

While building your Oracle APEX application, you use the industry-standard Structured Query Language (SQL) to specify the data you want to work with in a simple, declarative syntax.

You use SQL to indicate what data to retrieve or modify, without regard for how that will happen. While some spell out the acronym, most pronounce "SQL" like a movie "sequel".

• Basic SQL Statements
  You can use SQL productively for Oracle APEX app development by learning a small set of basic operations.
• SQL Mentor at Your Side
  APEX AI Assistant is your SQL mentor. After registering the generative AI service APEX should use, this AI‑powered design-time companion is available in every code editor in App Builder. It can help you write, learn, and debug SQL on demand.

While tables store your application data, it's important to understand how views, triggers, and packages complement them.

While you define a view using SQL, you write triggers, procedures, functions, and packages in PL/SQL, the procedural language extension to SQL. If your database is Oracle 26ai, you can also use JavaScript.

The Object Browser in App Builder lets you easily create and maintain any tables, views, triggers, and packages that your application requires.

• Database Views
  A database view is a named SQL query you can reuse to simplify data access across your APEX applications.
• Database Triggers
  Database triggers are a useful way to validate data and compute column values in one place, no matter which application access it.
• Database Packages
  Database packages contain a specification of available procedures and functions. Oracle recommends you use them to organize custom business logic.

You can use SQL productively for Oracle APEX app development by learning a small set of basic operations.

You use SQL's SELECT statement to retrieve data to display in a page or to reference in custom business logic. Its FROM clause names the desired table. When a table has a foreign key column, the JOIN clause lets you also retrieve data from related tables at the same time. Since a SELECT command is a request for information from your database, it is also known as a query. It produces results that answer some question about your application data like, "Who are all the employees that meet this criteria?"

Using other SQL statements, you can INSERT new rows of data into a table, UPDATE existing rows as needed, or DELETE rows no longer needed. When a logical business transaction requires multiple changes, you either save them all as a unit with COMMIT or abandon them all with ROLLBACK.

Your SQL commands can specify conditions on which rows in a table to select, update, or delete. Using a WHERE clause, you identify the participating rows using filter expressions. You sort the results with an ORDER BY clause. For example, the SELECT statement below retrieves the employee name, salary, and location of the department in which they work from related EMP and DEPT tables. It returns only employees whose salary is between 800 and 2450 and whose job is either "CLERK" or "MANAGER". The JOIN … ON clause asks to relate departments to employees using their respective DEPTNO value. The results are sorted by salary in descending order, that is, highest salary first.

SELECT emp.ename, emp.sal, dept.loc
  FROM emp
  JOIN dept ON emp.deptno = dept.deptno
 WHERE sal BETWEEN 800 AND 2450
   AND job IN ('CLERK','MANAGER')
 ORDER BY sal DESC

The query produces the following result rows:

ENAME         SAL LOC
--------- ------- -----------
CLARK        2450 NEW YORK
MILLER       1300 NEW YORK
ADAMS        1100 DALLAS
JAMES         950 CHICAGO
SMITH         800 DALLAS

APEX's native components can formulate SQL statements for you, given just a table name and the set of columns a page references. However, when writing custom business logic or fine-tuning the data for a particular part of a page, knowing SQL basics will be your superpower in daily development. In either case, APEX automatically augments SQL statements for you to add filtering, ordering, pagination, and aggregation to support end-user requirements. SQL Workshop's SQL Commands editor shown below is a simple sandbox for your SQL experiments.

APEX AI Assistant is your SQL mentor. After registering the generative AI service APEX should use, this AI‑powered design-time companion is available in every code editor in App Builder. It can help you write, learn, and debug SQL on demand.

To write SQL, explain in English what data you need to work with, and APEX AI Assistant can help generate the statement to get the job done. To improve your understanding of SQL, APEX AI Assistant can help answer any questions or doubts you have. As you develop, it can also help suggest corrections to SQL when you encounter errors.

A database view is a named SQL query you can reuse to simplify data access across your APEX applications.

CREATE OR REPLACE VIEW mid_level_clerks_and_mgrs AS
SELECT emp.ename, emp.sal, dept.loc
  FROM emp
  JOIN dept ON emp.deptno = dept.deptno
 WHERE sal BETWEEN 800 AND 2450
   AND job IN ('CLERK','MANAGER')

SELECT ename, sal, loc
  FROM mid_level_clerks_and_mgrs
 WHERE loc LIKE '%A%'
ORDER BY sal

ENAME             SAL LOC
---------- ---------- ----------
SMITH             800 DALLAS
JAMES             950 CHICAGO
ADAMS            1100 DALLAS

Database triggers are a useful way to validate data and compute column values in one place, no matter which application access it.

CREATE OR REPLACE TRIGGER emp_bef_ins_or_upd
BEFORE INSERT OR UPDATE ON emp FOR EACH ROW
BEGIN
    -- If incoming SAL value exceeds 9000, then raise error
    IF :NEW.sal > 9000 THEN
        raise_application_error(-20001,'Maximum salary is 9000');
    END IF;
    -- Round to nearest multiple of ten. The -1 means
    -- 1 digit to the *left* of the decimal point
    :NEW.sal := ROUND(:NEW.sal, -1);
END;

UPDATE emp
   SET sal = 9954
 WHERE empno = 7900 /* JAMES */

ORA-20001: Maximum salary is 9000

Database packages contain a specification of available procedures and functions. Oracle recommends you use them to organize custom business logic.

CREATE OR REPLACE PACKAGE hr_utils AS
    ---------------------------------------
    PROCEDURE handle_employer_contribution(
        p_empno                  IN  NUMBER,
        p_additional_withholding IN  NUMBER DEFAULT 0,
        p_total_contribution     OUT NUMBER);
    ---------------------------------------
    FUNCTION years_employed(
        p_hiredate IN DATE)
        RETURN        NUMBER;
END hr_utils;

CREATE OR REPLACE PACKAGE BODY hr_utils AS
    ---------------------------------------
    FUNCTION years_ago(
        p_date IN DATE)
    RETURN NUMBER
    IS
    BEGIN
        RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, p_date) / 12);
    END years_ago;
    ---------------------------------------
    PROCEDURE handle_employer_contribution(
        p_empno                  IN NUMBER,
        p_additional_withholding IN NUMBER DEFAULT 0,
        p_total_contribution     OUT NUMBER)
    IS
    BEGIN
        apex_debug.enter('handle_employer_contribution');
        -- Interesting contribution code here, eventually
        -- assigning a value to p_total_contribution
    END handle_employer_contribution;
    ---------------------------------------
    FUNCTION years_employed(
        p_hiredate IN DATE)
        RETURN        number
    IS
    BEGIN
        RETURN years_ago(p_hiredate);
    END years_employed;
END hr_utils;

-- Handle the employee's pension contribution
hr_utils.handle_employer_contribution(
    p_additional_withholding => 1500,
    p_empno                  => :P71_EMPLOYEE_ID,
    p_total_contribution     => :P71_TOTAL);