Skip to content

🔍 SEO & DISCOVERABILITY (Metadata)

title: "PL/SQL Data Types | Scalar, Anchored, & %TYPE" description: "Understand PL/SQL data types. Learn about scalar types, Boolean, and the powerful %TYPE anchored declaration for dynamic column matching." keywords: [plsql data types, %type oracle, anchored declaration, plsql boolean, oracle variable types, vishnu damwala] tags: [oracle, plsql, foundations]

🎓 EDUCATIONAL CONTEXT

difficulty: Beginner estimated_time: 20 minutes prerequisites: - PL/SQL Variables

PL/SQL Data Types 🏗️

Mentor's Note: In standard SQL, we have columns like VARCHAR2 or NUMBER. PL/SQL supports all of those, plus some special types like BOOLEAN and Anchored Types. Anchored types are the "Secret Sauce" of Oracle—they make your code future-proof! 💡

🌟 The Scenario: The Custom Mold 🧱

Imagine you are a baker. - Fixed Size: You have a 10cm cake tin. (VARCHAR2(10)). - Anchored Size: You tell your assistant: "Make the tin the exact same size as the customer's order." If the customer changes the order size, your tin changes automatically. ✅

💻 1. Scalar Types (The Basics)

Scalar types hold a single value. - VARCHAR2(n): Text. - NUMBER(p, s): Numeric. - DATE: Date and time. - BOOLEAN: TRUE, FALSE, or NULL. (Only in PL/SQL, not in SQL columns!).

DECLARE
   v_is_active BOOLEAN := TRUE;
   v_score     NUMBER(3) := 95;
BEGIN
   IF v_is_active THEN
      DBMS_OUTPUT.PUT_LINE('User is online! ✅');
   END IF;
END;

💻 2. Anchored Types (%TYPE) ⚓

This is the most professional way to declare variables. Instead of guessing the size of a column, you "anchor" your variable to the database column.

DECLARE
   -- "Make v_emp_name the same type as the 'first_name' column in 'employees'"
   v_emp_name employees.first_name%TYPE;
   v_salary   employees.salary%TYPE;
BEGIN
   SELECT first_name, salary INTO v_emp_name, v_salary
   FROM employees WHERE employee_id = 101;
END;

🛡️ 3. Why use %TYPE? (Architect's Note)

  1. Maintenance: If the DBA changes first_name from 50 chars to 100 chars, your PL/SQL code will not break. It updates automatically! ⚡
  2. Safety: You avoid "Value too large" errors because your variable will always be large enough to hold the table data.
  3. Accuracy: You never have to worry about data type mismatches (e.g., trying to put a Number into a String).
graph LR
    A[(Table Column: VARCHAR2 100)] -- %TYPE --> B[Variable: v_name]
    A -- "DBA changes to 200" --> C[(Table Column: VARCHAR2 200)]
    C -- "Auto Update" --> D[Variable: v_name 200]

📈 Learning Path