PL-SQL lab

What This Provides

A lightweight, reproducible Oracle 23c Free environment for SQL + PL/SQL practice.

Features:

• Docker-based Oracle DB with persistent volume
• Automatic schema + seed initialization on first run
• Make commands for common workflows
• Ability to run custom SQL scripts
• Easy full reset / rollback
• Seed data included for immediate experimentation

📦 Directory layout

plsql-lab/
├─ docker-compose.yml
├─ .env                       # secrets & config (not checked in)
├─ db/
│  ├─ init/                   # runs ONLY on first database creation
│  │   ├─ 01_schema.sql
│  │   └─ 02_seed_data.sql
│  └─ startup/                # runs at EVERY container start
│      └─ 01_after_start.sql
├─ scripts/
│  ├─ seed.sh                 # apply any .sql file(s) on demand
│  └─ reset.sh                # stop & wipe volumes & re-create
└─ Makefile

Make scripts executable

chmod +x scripts/*.sh

---

🐳 Quickstart (Docker Only)

Start the environment:

docker compose up -d

Check logs:

docker compose logs -f oracle

Stop:

docker compose down

Full reset (recreates DB + reruns init scripts):

docker compose down -v && docker compose up -d

---

🧰 Quickstart (Using Make)

Start DB:

make up

Stop DB:

make down

Logs:

make logs

Open shell:

make sh

Run any SQL file as APP_USER:

make seed FILE=path/to/script.sql

Full reset (fresh DB + auto-seed):

make reset

---

Clone repo

git clone git@github.com:Kotmin/PL-SQL-Lab.git

🧠 Connect to IDE

Test default state with

SHOW USER;

SELECT table_name FROM user_tables ORDER BY table_name;

-- Which schema am I?
SHOW USER;  


SELECT owner, table_name
FROM   all_tables
WHERE  table_name IN ('STUDENCI','EGZAMINY','EGZAMINATORZY','OSRODKI','PRZEDMIOTY')
ORDER  BY owner, table_name;

Working with VSC

To install connect and use lab within Visual Studio Code

Details

Enable display to console(in that preset it should be already enabled)

SET SERVEROUTPUT ON;

BEGIN
  DBMS_OUTPUT.PUT_LINE('5');
END;
/

Working with DataGrip

To install connect and use lab with DataGrip

Details

oracle dialect

Driver

assign shortcut for run code alt + enter

Enable DBMS_OUTPUT

Enable display to console(in that preset it should be already enabled)

SET SERVEROUTPUT ON;

BEGIN
  DBMS_OUTPUT.PUT_LINE('5');
END;
/

🌱 Seed & Initialization

🔧 Automatic Initialization

Files in:

db/init/

run once on first DB creation:

• 01_schema.sql — creates tables
• 02_seed_data.sql — loads example data

Re-run by performing a reset:

make reset

🔁 Startup Scripts

Files in:

db/startup/

run every time the container starts (useful for views, helpers, maintenance).

---

📊 Testing the Environment

Test that PL/SQL + DBMS_OUTPUT works:

SET SERVEROUTPUT ON;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Oracle Lab OK');
END;
/

Test your sample data:

SELECT COUNT(*) FROM studenci;
SELECT COUNT(*) FROM egzaminy;

🧹 Troubleshooting

❗ “Table does not exist” / ORA-00942

Database schema didn’t initialize.
Fix:

make reset

❗ Seed script not running

Use:

make seed FILE=yourfile.sql

❗ DBMS_OUTPUT not showing

Enable in your client:

SET SERVEROUTPUT ON;

❗ “Database already initialized”

Remove volume + restart:

make reset

---

📘 Notes

• Oracle version: 23c Free
• Main schema: APP_USER
• Service name for connections: FREEPDB1
• Data persists until you reset the volume
• Safe sandbox — reset anytime for a clean environment