Skip to content
Tutorials Center
My learning

Topics

Lesson 1 of 38 ยท Getting Started

What Is Oracle Database?

Meet Oracle Database 23ai: what it is, how an instance and a database fit together, what SQL and PL/SQL are, and how the examples in this course run.

Oracle Database is one of the most widely used relational database systems in the world. Banks, airlines, hospitals, telecom companies and governments run their most important data on it. It stores data in tables, you query it with SQL, and you can put real program logic inside the database itself with PL/SQL, Oracle's procedural language.

This course covers Oracle Database 23ai, the current long-term-support release. You will learn Oracle's flavor of SQL (with its own quirks such as DUAL, ROWNUM and CONNECT BY) and then PL/SQL: blocks, cursors, procedures, packages, triggers and exceptions. If you already know basic SQL from our SQL tutorial, you will move quickly; if you do not, the first chapters teach everything from scratch.

What is real and what is a stand-in

Oracle Database cannot run in your browser, and it cannot run on this site's code runner either. So this course is honest about what each example is:

  • Portable SQL (SELECT, WHERE, JOIN, GROUP BY, CASE, COALESCE, window functions) runs for real in a <TryIt> editor. Those editors use SQLite, which understands the same standard SQL. Where Oracle and SQLite differ only in spelling, the lesson says so.
  • Oracle-specific syntax (DUAL, ROWNUM, SYSDATE, sequences, CONNECT BY, NVL, DECODE, every PL/SQL block) appears in read-only code blocks. We show the output only when we are certain of it.
  • Stand-ins are runnable examples that give the same result in another way: a recursive CTE for CONNECT BY, a window function for ROWNUM paging, coalesce for NVL. They are labelled "Stand-in" every time.

To run the Oracle-only code yourself, you can use a free tool such as Oracle Database Free in a container, or a free cloud account. The next lesson shows the options.

SQL and PL/SQL

Two languages work together in Oracle:

Language What it does Example
SQL Describes what data you want (declarative) SELECT name FROM employees WHERE salary > 5000
PL/SQL Adds variables, loops, IF, error handling and stored code A procedure that gives every employee in a department a raise

SQL says what; PL/SQL says how, step by step. PL/SQL blocks contain SQL statements, so the two are used together all the time. Chapter 5 starts PL/SQL; chapters 1 to 4 are SQL.

SQL
-- Oracle SQL: runs in SQL*Plus, SQL Developer, and so on
SELECT 'Hello, Oracle' AS greeting FROM dual;
PLSQL
-- PL/SQL: an anonymous block
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL');
END;
/

The first statement prints Hello, Oracle. The second prints Hello from PL/SQL once server output is switched on (SET SERVEROUTPUT ON in SQL*Plus).

Instance, database and schema

Beginners mix up three words. Keep them apart now and the rest of Oracle gets easier.

flowchart LR
  C["Client<br/>SQL Developer, app"] --> I
  subgraph S["Server"]
    I["Instance<br/>memory + background processes"] --> D["Database<br/>files on disk"]
  end
  D --> T["Tablespaces"]
  T --> U["Schemas (users)<br/>tables, views, code"]

A client talks to an instance; the instance reads and writes the database files.

  • The database is the set of files on disk: data files, redo logs, control files.
  • The instance is the running software: memory structures (the SGA and PGA) and background processes that read and write those files.
  • A schema is the collection of objects (tables, views, procedures) owned by one database user. The user HR owns the schema HR. When you write SELECT * FROM hr.employees, hr is the schema name.

Since Oracle 12c, a multitenant database holds one container database (CDB) and many pluggable databases (PDBs). Your applications almost always connect to a PDB, such as FREEPDB1 in Oracle Database Free.

What is new in 23ai

Oracle Database 23ai (the "ai" is for AI vector search) added many features that make SQL friendlier. We use several of them in later lessons:

  • A native BOOLEAN data type in SQL.
  • SELECT 1 + 1 without FROM dual.
  • IF [NOT] EXISTS in CREATE and DROP statements.
  • Multi-row INSERT ... VALUES (...), (...).
  • GROUP BY and HAVING that accept column aliases.
  • Vector data type and similarity search for AI applications.

If your server is an older release such as 19c, most of the course still works; each lesson flags the 23ai-only parts.

A first runnable query

SQL's building blocks are the same everywhere. This query runs for real in the editor, against a small bookstore database:

SQL Your first query ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

In Oracle the statement is identical. Oracle ends statements with a semicolon in tools like SQL*Plus, but the semicolon is not part of the statement itself.

Here is one place the dialects differ. Oracle has no LIMIT; it uses FETCH FIRST. Stand-in: SQLite's LIMIT returns the same three rows.

SQL Stand-in for FETCH FIRST 3 ROWS ONLY ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

How the course is organized

Ten chapters, one path

Getting started Tools, data types, first queries
SQL and functions Query, change and transform data
Joins and hierarchies Combine tables, walk trees
PL/SQL Blocks, cursors, packages, triggers

Chapters 6 to 10 cover stored code, errors, tuning and projects.

1 / 2

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement describes an Oracle instance?

    The instance is the running software (memory plus processes). The database is the files on disk, and a schema is one user's objects.
  2. 2. What is PL/SQL?

    PL/SQL adds variables, control flow, error handling and stored code to SQL. SQL*Plus is a command-line client tool.
  3. 3. In this course, why are PL/SQL blocks shown in read-only code blocks?

    Portable SQL runs in SQLite-based editors. Oracle-specific syntax and PL/SQL are shown read-only, with clearly labelled stand-ins where possible.
  4. 4. What does a schema contain?

    A schema is the set of objects owned by a user. Its name matches the user's name.

Summary

  • Oracle Database stores data in tables and is queried with SQL; PL/SQL adds procedural code inside the database.
  • An instance (memory plus processes) works on a database (files); a schema is one user's objects.
  • Modern Oracle is multitenant: you connect to a pluggable database.
  • 23ai adds friendlier SQL, a BOOLEAN type and vector search.
  • In this course, portable SQL runs live, Oracle-only syntax is read-only, and stand-ins are labelled.

Next: Oracle Tools and Setup