# 📘 Developer Notes: Packages ## What is Oracle APEX? ### 💡 The Idea in Plain English Oracle APEX (Application Express) is a tool that lets you build **web applications** that run directly on top of the **Oracle Database**. Think of it like this: * You already have a powerful database that stores all your data. * APEX is the **front door and control panel** that helps you interact with that database through apps. 👉 Without writing thousands of lines of code, you can create pages, forms, charts, and reports. ### 🏠 Analogy: A House with Furniture * The **database** is the house (it stores everything). * **SQL & PL/SQL** are the rules and tools for managing that house. * **APEX** is the **furniture + decorations** that make the house liveable and easy to use. So instead of raw bricks (data), APEX gives you a comfy living space (apps). ### 🔹 A Real Example Imagine you’re managing **students** at a university. * The raw data (names, emails, majors) lives in the **STUDENTS** table inside the Oracle Database. * With APEX, you can: * Build a form to **add new students**. * Build a report page to **list all students**. * Add charts to show “Students per Major”. All this is powered by the database — but APEX makes it interactive and pretty. ### 📝 Mini Practice 1. Log in to Oracle APEX (or if you don’t have it, just imagine you did). 2. Go to **SQL Workshop → SQL Commands**. 3. Run your first SQL command: ```sql SELECT SYSDATE FROM DUAL; ``` 👉 This simply asks: “Hey database, what’s today’s date and time?” 4. APEX will return something like: ```sql 30-SEP-25 10:32:15 ``` 🎉 Boom! You just ran your first command in Oracle APEX. ✅ **Key Takeaway:** * APEX = web app builder for Oracle Database. * Lets you turn raw data into usable apps (forms, reports, charts). * You’ll use it together with SQL and PL/SQL as you learn packages. --- ## SQL vs PL/SQL ### 💡 The Idea in Plain English * **SQL (Structured Query Language)** is how you **talk** to the database. You ask questions like: * “Show me all students.” * “Insert this new student.” * **PL/SQL (Procedural Language / SQL)** is how you **teach** the database to do a series of tasks with logic. * “For each student, if their email is missing, set it to unknown.” * “Insert a student, then log the action, then send a message.” 👉 Think of SQL as **a single instruction** and PL/SQL as **a recipe with multiple steps**. ### 🏠 Analogy: Restaurant * **SQL** = You tell the **waiter** what you want: “Bring me a pizza.” * **PL/SQL** = You go into the **kitchen** and give the **chef** a recipe: * Step 1: Preheat oven. * Step 2: Make dough. * Step 3: Add toppings. * Step 4: Bake and serve. SQL = quick request. PL/SQL = full recipe with flow and logic. ### 🔹 SQL Example Ask the database for data: ```sql SELECT student_name, student_email FROM students WHERE student_major_fk = 1; ``` 👉 This says: *“Show me all students whose major ID = 1.”* ### 🔹 PL/SQL Example Now let’s teach the database a task: ```sql BEGIN DBMS_OUTPUT.PUT_LINE('Hello, Database!'); END; ``` 👉 This block **doesn’t just query**, it **executes logic**. Here, it prints a message. And you can go further, like inserting multiple students in a loop, handling errors, etc. ### 📊 Quick Comparison | Feature | SQL | PL/SQL | | --- | --- | --- | | Purpose | Talk to the database | Teach the database tasks | | Nature | Single statements | Blocks of code (multi-step) | | Logic | No IF/LOOP, just queries | Full programming (IF, LOOP, EXCEPTION) | | Example | `SELECT * FROM students;` | `BEGIN ... END;` | --- ### 📝 Mini Practice 1. In APEX SQL Commands, run this SQL: ```sql SELECT COUNT(*) FROM students; ``` 👉 This tells you how many students exist. 2. Now run this PL/SQL block: ```sql BEGIN DBMS_OUTPUT.PUT_LINE('Number of students = ' || (SELECT COUNT(*) FROM students)); END; ``` 👉 Notice how it **mixes SQL inside PL/SQL** and adds logic (printing the result). ✅ **Key Takeaway:** * SQL = one-time requests. * PL/SQL = programming logic with SQL inside. * Together, they’re the building blocks for packages. --- ## What is a Package? ### 💡 The Idea in Plain English When you start writing more and more PL/SQL code, it can get messy. * Imagine you have 20 procedures scattered everywhere. * Some insert data, some update data, some delete data. * Hard to manage, right? 👉 That’s where a **Package** comes in. A package is a **toolbox** where you group related procedures and functions together in one place. ### 🏠 Analogy: Toolbox * A **toolbox** holds different tools. * Each tool has a job: hammer (nail), screwdriver (screw), tape (measure). * Instead of leaving them scattered, you keep them **organized in one box**. 👉 In Oracle: * A package is the **toolbox**. * A **procedure** is a tool that *does something*. * A **function** is a tool that *does something and gives you a result*. ### 🔹 Package Structure: The Two Parts 1. **Package Specification (Spec)** = *Menu* * Shows what tools (procedures/functions) are available. * Doesn’t show how they work. 2. **Package Body** = *Kitchen* * Contains the real code (recipes). * This is where the actual logic lives. 👉 Together, they form a complete package. * Menu tells you “what you can order”. * Kitchen decides “how it’s cooked”. ### 🔹 Example: Student Package **Package Spec (menu):** ```sql CREATE OR REPLACE PACKAGE student_pkg IS PROCEDURE add_student(p_name VARCHAR2, p_email VARCHAR2); FUNCTION get_student_email(p_id NUMBER) RETURN VARCHAR2; END student_pkg; ``` 👉 This says: “Inside this toolbox, I have two tools: 1. A procedure to add a student. 2. A function to get a student’s email.” **Package Body (kitchen):** ```sql CREATE OR REPLACE PACKAGE BODY student_pkg IS PROCEDURE add_student(p_name VARCHAR2, p_email VARCHAR2) IS BEGIN INSERT INTO students (student_name, student_email) VALUES (p_name, p_email); END add_student; FUNCTION get_student_email(p_id NUMBER) RETURN VARCHAR2 IS v_email VARCHAR2(320); BEGIN SELECT student_email INTO v_email FROM students WHERE student_id = p_id; RETURN v_email; END get_student_email; END student_pkg; ``` 👉 Now the “menu” is backed up by a “kitchen” that really does the work. ### 📝 Mini Practice 1. Look at the spec above. Can you identify: * Which one is a **procedure**? * Which one is a **function**? 2. Imagine you’re building a “Book Toolbox”. * What procedures might you add? (e.g., add\_book, delete\_book). * What functions might you add? (e.g., get\_book\_title, count\_books). ✅ **Key Takeaway:** * A package is a toolbox for your PL/SQL code. * Spec = menu (what’s available). * Body = kitchen (how it works). * Packages keep your database logic clean, organized, and professional. --- ## Procedures Inside a Package ### 💡 What is a Procedure? A **procedure** is a stored program that **does something** in the database but doesn’t directly give back a value. * Think of it like pressing a **light switch**: it turns the light on, but you don’t “get” anything back in your hand. * Procedures are great for tasks like inserting, updating, or deleting data. ### 🔹 Example: `add_student` Procedure **Step 1 – Package Spec (menu):** ```sql CREATE OR REPLACE PACKAGE student_pkg IS PROCEDURE add_student(p_name VARCHAR2, p_email VARCHAR2); END student_pkg; ``` 👉 This tells us: “In my toolbox, there’s a tool called `add_student` that takes a name and an email.” **Step 2 – Package Body (kitchen):** ```sql CREATE OR REPLACE PACKAGE BODY student_pkg IS PROCEDURE add_student(p_name VARCHAR2, p_email VARCHAR2) IS BEGIN INSERT INTO students (student_name, student_email) VALUES (p_name, p_email); END add_student; END student_pkg; ``` 👉 This is the recipe: when you use the tool, it inserts a new row into the `students` table. ### 🔹 Step 3 – Calling the Procedure Now, let’s use our tool: ```sql BEGIN student_pkg.add_student('Alice', 'alice@example.com'); END; ``` 👉 Result: Alice is added to the `STUDENTS` table. ### 📝 Mini Practice 1. Try calling the procedure again with your own details: ```sql BEGIN student_pkg.add_student('Bob', 'bob@example.com'); END; ``` Then run: ```sql SELECT * FROM students; ``` to check if Bob is there. 2. Think of **two more tasks** you could build as procedures in the `student_pkg`. (Hint: maybe `update_student`, `delete_student`). ✅ **Key Takeaway:** * A **procedure** = action without return. * Perfect for tasks like insert, update, delete. * Inside a package, it becomes part of a neat toolbox you can reuse anywhere. --- ## Functions Inside a Package ### 💡 What is a Function? A **function** is a stored program that not only does something but also **returns a value** back to you. * Think of it like a **vending machine**: you put in a coin, press a button, and it gives you a snack (a result). * Functions are great for tasks like calculations, lookups, or anything where you expect an answer. 👉 **Difference from a procedure**: * Procedure = does an action (insert, update, delete). * Function = does an action **and gives back a result**. ### 🔹 Example: `get_student_email` Function **Step 1 – Package Spec (menu):** ```sql CREATE OR REPLACE PACKAGE student_pkg IS FUNCTION get_student_email(p_id NUMBER) RETURN VARCHAR2; END student_pkg; ``` 👉 This says: “In my toolbox, there’s a tool called `get_student_email` that takes a student ID and returns their email address.” **Step 2 – Package Body (kitchen):** ```sql CREATE OR REPLACE PACKAGE BODY student_pkg IS FUNCTION get_student_email(p_id NUMBER) RETURN VARCHAR2 IS v_email VARCHAR2(320); BEGIN SELECT student_email INTO v_email FROM students WHERE student_id = p_id; RETURN v_email; END get_student_email; END student_pkg; ``` 👉 This recipe finds the student’s email by ID and hands it back to you. ### 🔹 Step 3 – Calling the Function There are two ways to use it: 1. **In SQL (like a column):** ```sql SELECT student_pkg.get_student_email(1) AS email FROM dual; ``` 👉 This fetches the email of the student with ID = 1. 2. **In PL/SQL (like a variable):** ```sql DECLARE v_result VARCHAR2(320); BEGIN v_result := student_pkg.get_student_email(1); DBMS_OUTPUT.PUT_LINE('Email is: ' || v_result); END; ``` ### 📝 Mini Practice 1. Add a new student using the procedure from Section 4: ```sql BEGIN student_pkg.add_student('Charlie', 'charlie@example.com'); END; ``` 2. Now call the function: ```sql SELECT student_pkg.get_student_email(3) AS email FROM dual; ``` 👉 You should get Charlie’s email back. 3. Challenge yourself: * What if the function was `get_student_name`? * How would you write it to return the student’s name by ID? ✅ **Key Takeaway:** * A **function** = action **with return**. * Can be used in SQL queries or PL/SQL blocks. * Great for calculations, lookups, or reusable logic. --- ## Combining Procedures and Functions in One Package ### 💡 Why Combine Them? In real projects, you’ll rarely see a package with just one procedure or one function. Instead, you’ll group related tools together — like a toolbox for **student management** or **orders management**. 👉 Think of it like a **smartphone app**: * The app has different features (camera, music, messaging). * All features are in the same app, not scattered around. Packages work the same way — related procedures and functions belong in the same package. ### 🔹 Example: Student Package with Both **Package Spec (menu):** ```sql CREATE OR REPLACE PACKAGE student_pkg IS PROCEDURE add_student(p_name VARCHAR2, p_email VARCHAR2); FUNCTION get_student_email(p_id NUMBER) RETURN VARCHAR2; END student_pkg; ``` 👉 Our menu now lists **two tools**: 1. A procedure to add a student. 2. A function to fetch a student’s email. **Package Body (kitchen):** ```sql CREATE OR REPLACE PACKAGE BODY student_pkg IS PROCEDURE add_student(p_name VARCHAR2, p_email VARCHAR2) IS BEGIN INSERT INTO students (student_name, student_email) VALUES (p_name, p_email); END add_student; FUNCTION get_student_email(p_id NUMBER) RETURN VARCHAR2 IS v_email VARCHAR2(320); BEGIN SELECT student_email INTO v_email FROM students WHERE student_id = p_id; RETURN v_email; END get_student_email; END student_pkg; ``` 👉 Now our toolbox has both a **do-something tool** and a **give-back tool**. ### 🔹 Using Them Together 1. **Add a student (procedure):** ```sql BEGIN student_pkg.add_student('David', 'david@example.com'); END; ``` 2. **Get the email back (function):** ```sql SELECT student_pkg.get_student_email(4) AS email FROM dual; ``` 👉 Flow: first **insert** → then **retrieve**. ### 📝 Mini Practice 1. Add two new students of your choice using `add_student`. 2. Use `get_student_email` to fetch each student’s email by ID. 3. Bonus challenge: * Write down one more idea for a **procedure** (e.g., `delete_student`). * And one more idea for a **function** (e.g., `get_student_name`). ✅ **Key Takeaway:** * Packages are powerful because they combine **procedures (actions)** and **functions (results)** in one organized place. * You can chain them together for real workflows (insert → check → calculate → return). * This is the foundation of how real enterprise systems are built. --- ## Why Packages Matter in Real Projects ### 💡 The Real-World Problem Imagine a big company with: * 20 different APEX apps. * 200+ pages across those apps. * 50 developers working together. 👉 If every developer wrote their own random procedures and functions, the code would be: * Messy 😵 * Duplicated 📑 * Hard to maintain 🔧 * Insecure 🔒 ### 💎 Why Packages Solve This 1. **Centralized Logic** * All business rules (like how to calculate a student’s GPA) live in one package. * If the rule changes, update it *once* → every app/page gets the new logic. 2. **Reusability** * A single package can serve multiple APEX apps. * No need to copy/paste code everywhere. 3. **Security** * You can give users permission to **run a package**, without exposing raw tables. * Safer for sensitive data (like salaries, medical records, student grades). 4. **Performance** * Packages are compiled and stored in the database. * Code runs faster than sending raw SQL each time. ### 🏠 Analogy: Recipe Book Without packages: * Every cook in the kitchen keeps their own sticky notes of recipes. * Chaos: dishes taste different, mistakes happen. With packages: * One **official recipe book** in the kitchen. * Every cook follows the same book → consistent, professional results. ### 🔹 Example in APEX Let’s say you have a form in APEX where a user adds a student. * Instead of writing `INSERT INTO students ...` in every app, * You just call: ```sql student_pkg.add_student(:P1_NAME, :P1_EMAIL); ``` If later the insert logic changes (e.g., you need to also record the creator), 👉 You only update the package. 👉 All apps instantly use the new rule. ### 📝 Mini Practice 1. Think of a daily task in your life you repeat often (making tea, sending an email, logging in). * Would you rather: * Write down steps on sticky notes each time? * Or keep one reusable recipe card in a box? 2. Now imagine your **student\_pkg** is that recipe card box. * Every time you need “add student” or “get email,” you don’t rewrite it. * You just call it. ✅ **Key Takeaway:** * Packages = **professional way** to handle database logic. * They bring order, security, speed, and consistency. * In the real world, every serious Oracle project uses them. --- ## Final Practice Lab ### 💡 Why a Practice Lab? Learning sticks best when you **build something yourself**. So here’s your chance to extend the `student_pkg` with **two new tools**: 1. A procedure to delete a student. 2. A function to get a student’s name. ### 🔹 Step 1 – Update the Package Spec (menu) ```sql CREATE OR REPLACE PACKAGE student_pkg IS PROCEDURE add_student(p_name VARCHAR2, p_email VARCHAR2); FUNCTION get_student_email(p_id NUMBER) RETURN VARCHAR2; PROCEDURE delete_student(p_id NUMBER); FUNCTION get_student_name(p_id NUMBER) RETURN VARCHAR2; END student_pkg; ``` 👉 Now your toolbox has **four tools**. ### 🔹 Step 2 – Update the Package Body (kitchen) ```sql CREATE OR REPLACE PACKAGE BODY student_pkg IS PROCEDURE add_student(p_name VARCHAR2, p_email VARCHAR2) IS BEGIN INSERT INTO students (student_name, student_email) VALUES (p_name, p_email); END add_student; FUNCTION get_student_email(p_id NUMBER) RETURN VARCHAR2 IS v_email VARCHAR2(320); BEGIN SELECT student_email INTO v_email FROM students WHERE student_id = p_id; RETURN v_email; END get_student_email; PROCEDURE delete_student(p_id NUMBER) IS BEGIN DELETE FROM students WHERE student_id = p_id; END delete_student; FUNCTION get_student_name(p_id NUMBER) RETURN VARCHAR2 IS v_name VARCHAR2(200); BEGIN SELECT student_name INTO v_name FROM students WHERE student_id = p_id; RETURN v_name; END get_student_name; END student_pkg; ``` 👉 Your kitchen is now fully stocked: insert, retrieve, delete, fetch name. ### 🔹 Step 3 – Try Them Out 1. **Add a new student:** ```sql BEGIN student_pkg.add_student('Emma', 'emma@example.com'); END; ``` 2. **Get her email:** ```sql SELECT student_pkg.get_student_email(5) AS email FROM dual; ``` 3. **Get her name:** ```sql SELECT student_pkg.get_student_name(5) AS name FROM dual; ``` 4. **Delete her:** ```sql BEGIN student_pkg.delete_student(5); END; ``` 5. **Check if she’s gone:** ```sql SELECT * FROM students WHERE student_id = 5; ``` ### 📝 Mini Practice 1. Add two new students of your own choice. 2. Use `get_student_name` and `get_student_email` to fetch their details. 3. Delete one student and confirm they’re gone. 4. Bonus challenge: * Add a new **procedure** called `update_student_email(p_id, p_new_email)`. * Try updating a student’s email with it. ✅ **Key Takeaway:** * Now you’ve built a **real mini-package** with both procedures and functions. * You know how to add, fetch, and delete students in an organized way. * This is exactly how developers build professional packages in real projects. **That’s it! You’ve taken your first steps into Oracle APEX development. 🎉** You started from zero: * Learning what APEX is and how it connects to the database * Seeing the difference between SQL (simple commands) and PL/SQL (programming logic) * Understanding packages as toolboxes that organize your code * Building your own procedures and functions, then combining them into a working package What you built may look small, but these are the same building blocks used in large systems with thousands of users. From here you can: * Add more tools to your package like updates or reports * Connect your package to APEX pages and buttons * Try advanced features like package variables or APEX APIs Every big system you’ve heard of started with these same basics. Keep practicing, keep experimenting, and keep building. > Don’t just memorize. Understand, apply, and think of packages as toolboxes. Once you get that, everything else becomes easier.