Theory
Bundling mismatched details into a single variable
Imagine your manager at TicketDesk wants an automated report that grabs a ticket's text title, its numerical priority level, and the text name of the assigned agent. If you create individual scalar variables for each piece, your declaration section quickly gets cluttered. When you pass these separate variables into a procedural function, your parameters multiply out of control. How can you group these distinct datatypes into a single custom package that moves together through your application logic?
Theory
The Customized Combo Meal
Think of scalar variables like buying items separately: a burger, some fries, and a cold drink. You have to carry each item individually in your hands, increasing the chance of dropping one. A user defined record is like ordering a combo meal packed inside a single customized cardboard tray. You create the tray shape to fit exactly one burger, one fry pack, and one drink. Now, you can pass the entire tray across the counter in a single movement.
Theory
Defining User-Defined Records Formally
In the Oracle dialect, a user defined record is a composite data structure that groups multiple related fields of different datatypes under a single name. Unlike %ROWTYPE, which binds strictly to an entire table's predefined row structure, a user-defined record is completely customized by the programmer. You declare the blueprint using the TYPE ... IS RECORD syntax, and then instantiate record variables from that blueprint to store mixed data types in database memory.
At a glance
Comparison between Oracle %ROWTYPE records and custom user defined records
| Feature | %ROWTYPE Record | User Defined Record |
|---|---|---|
| Structure Source | Automatically copied from an existing table or view | Explicitly structured by the programmer in code |
| Field Flexibility | Must include every column from the table in exact order | Can select specific columns or mix fields from multiple tables |
| Declaration Step | One step: use variable name table_name%ROWTYPE | Two steps: define the TYPE blueprint, then declare the variable |
| Custom Fields | Cannot include non table variables or calculated fields | Can include independent counters or extra flags alongside table data |
Practical
Declaring and Using a Custom Record Block
-- Enable console display in Oracle SQL*Plus
SET SERVEROUTPUT ON;
DECLARE
-- Step 1: Define the custom record blueprint
TYPE t_ticket_summary IS RECORD (
ticket_title tickets.title%TYPE,
urgency_status VARCHAR2(20),
days_open NUMBER := 0
);
-- Step 2: Declare a variable of the new custom type
v_my_record t_ticket_summary;
BEGIN
-- Step 3: Populate fields using a query or direct assignment
SELECT title, status INTO v_my_record.ticket_title, v_my_record.urgency_status
FROM tickets
WHERE id = 101;
v_my_record.days_open := 5;
-- Step 4: Access fields using dot notation
DBMS_OUTPUT.PUT_LINE('Ticket: ' || v_my_record.ticket_title);
DBMS_OUTPUT.PUT_LINE('SLA Days: ' || v_my_record.days_open);
END;
/Quiz
What is the correct dot notation syntax to modify the 'days_open' field inside a record variable named 'v_info'?
- v_info->days_open := 10;
- v_info.days_open := 10;
- t_ticket_summary.days_open := 10;
- v_info(days_open) := 10;
Show the answer
v_info.days_open := 10;
PL/SQL uses dot notation to access individual fields within a record variable. You write the variable name, followed by a period, and then the field name. Option 0 uses pointer notation from languages like C++ which is wrong. Option 2 references the blueprint type name rather than the specific variable instance.
Think first
Mental Challenge: Combining Tables
Can you include a field from the tickets table and a field from the agents table inside a single user defined record blueprint? Think about the field declarations mentally before tapping to reveal.
Show the answer
Yes! That is one of the main advantages of user defined records. You can define a single record type where field one is tickets.title%TYPE and field two is agents.name%TYPE. This allows you to fetch joined data into a single consolidated record variable seamlessly.
Watch out
The Two-Step Declaration Blunder
A classic mistake in university lab exams is trying to use a user-defined record directly without declaring a variable first. Writing t_ticket_summary.ticket_title := 'Crash'; will trigger an immediate compilation error. Remember that TYPE is just an abstract blueprint, a shape in your mind. It does not occupy memory space. You must always create a concrete variable instance from that type before reading or writing data values.
Theory
Production API Pipelines and Future Semesters
In enterprise software, records are used to build clean programming interfaces. Instead of writing database functions with 20 separate input variables, you pass a single record variable, keeping code modular and readable. In your Sem 3 mobile databases curriculum using SQLite, you will group row data into structural model classes that act identically to these PL/SQL custom records.
Summary
Key takeaways
- A user defined record groups multiple fields of different datatypes into a single compound unit.
- It provides complete architectural flexibility compared to table locked rowtype variables.
- Creating a record requires a two step process: defining the TYPE structure, then declaring a variable instance.
- Dot notation provides direct operational access to read and modify specific fields within the variable.
- Record instances simplify argument passing in complex database routines by clustering raw values.
- Memory hook: Design the custom tray blueprint, create the box, and access with a dot!