1. What are composite data types in Oracle?
Composite data types are types that can hold multiple values or different kinds of values in a single variable. They go beyond simple types like NUMBER or VARCHAR2.
In Oracle, the main composite types are:
- Records – like a “row” with named fields.
- Tables / Collections – like arrays or lists.
- Objects – user-defined types with attributes and methods.
2. What is a RECORD in Oracle?
A RECORD is a variable that can hold multiple fields of different data types. Think of it as a row in a table stored in PL/SQL. Each field is called a component or attribute.
Example:
DECLARE
TYPE student_rec IS RECORD (
id NUMBER,
name VARCHAR2(50),
marks NUMBER
);
s student_rec;
BEGIN
s.id := 101;
s.name := 'Alice';
s.marks := 95;
DBMS_OUTPUT.PUT_LINE('Student: ' || s.name || ' - Marks: ' || s.marks);
END;
/
3. What is an OBJECT type in Oracle?
An OBJECT type is a user-defined type that can store multiple attributes and even methods (procedures/functions).
Example:
CREATE TYPE student_obj AS OBJECT (
id NUMBER,
name VARCHAR2(50),
marks NUMBER,
MEMBER FUNCTION grade RETURN VARCHAR2
);
- Can be used as a column in a table or a PL/SQL variable.
- Objects can have methods for behavior.
4. What are collections in Oracle?
Collections are groups of elements of the same type. Oracle supports:
- VARRAY (Variable-size array) – fixed max size, maintains order.
- Nested Table – unordered, can grow dynamically.
- Associative Array (Index-by table) – key-value pairs, like a dictionary.
Example of a nested table:
CREATE TYPE num_list AS TABLE OF NUMBER;
DECLARE
numbers num_list := num_list(10, 20, 30);
BEGIN
DBMS_OUTPUT.PUT_LINE(numbers(1)); -- prints 10
END;
/
5. What is the difference between RECORDs and OBJECTs?
|
Feature |
RECORD |
OBJECT TYPE |
|
Scope |
PL/SQL only |
SQL & PL/SQL |
|
Behavior |
Data only |
Data + Methods (functions/procedures) |
|
Use in table |
No |
Yes |
|
Reusability |
Local to PL/SQL block |
Can be reused anywhere in DB |
6. How do I use composite types in tables?
Objects as columns:
CREATE TABLE students (
student_info student_obj
);
Nested tables as columns:
CREATE TYPE num_list AS TABLE OF NUMBER;
CREATE TABLE student_scores (
id NUMBER,
scores num_list
) NESTED TABLE scores STORE AS scores_tab;
7. Can composite types contain other composite types?
Yes, Oracle allows nested composite types. For example:
- A record can have a nested record
- An object can have a nested object or collection
- A collection can hold objects
8. What are the advantages of using composite types?
- Group related data in a single variable.
- Simplifies PL/SQL programming.
- Can model real-world entities like students, employees, or orders.
- Supports modular design (especially with object types and methods).
9. Any limitations of composite types?
- RECORDs are PL/SQL-only, cannot be directly stored in tables.
- Nested composites can get complex and harder to query.
- Methods in OBJECTs add overhead, so not ideal for very large datasets.
10. Can I loop through composite types?
- Records: No loop, access fields directly by name.
- Collections: Yes, you can loop using FOR i IN 1..collection.COUNT for VARRAYs or nested tables, or FOR key IN collection.FIRST..collection.LAST for associative arrays.
Example (looping nested table):
DECLARE
TYPE num_list IS TABLE OF NUMBER;
numbers num_list := num_list(5, 10, 15);
BEGIN
FOR i IN 1..numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || numbers(i));
END LOOP;
END;
/