RECORD Type FAQS

1. What is a RECORD type in Oracle?

A RECORD is a composite data type in PL/SQL that allows you to group multiple fields (columns) into a single variable.

• Each field can have a different data type.
• Useful for storing a row of data or related variables together.

2. How do you declare a RECORD type?

There are two ways to declare a record:

1️⃣ Explicit TYPE declaration

DECLARE

   TYPE emp_rec_type IS RECORD (

      emp_id     employees.employee_id%TYPE,

      emp_name   employees.first_name%TYPE,

      emp_salary employees.salary%TYPE

   );

   emp_rec emp_rec_type;

BEGIN

   NULL;

END;

2️⃣ %ROWTYPE declaration (based on an existing table or cursor)

DECLARE

   emp_rec employees%ROWTYPE;

BEGIN

   NULL;

END;

%ROWTYPE automatically matches the table structure.

3. How do you assign values to a RECORD?

• Use dot notation: record_name.field_name

Example:

DECLARE

   emp_rec employees%ROWTYPE;

BEGIN

   emp_rec.employee_id := 101;

   emp_rec.first_name := 'Alice';

   emp_rec.salary := 5000;

 

   DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' earns ' || emp_rec.salary);

END;

4. How do you fetch data into a RECORD?

• Records are commonly used with explicit cursors:

DECLARE

   CURSOR emp_cursor IS

      SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10;

   emp_rec emp_cursor%ROWTYPE;

BEGIN

   OPEN emp_cursor;

   FETCH emp_cursor INTO emp_rec;

   DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' - ' || emp_rec.salary);

   CLOSE emp_cursor;

END;

• %ROWTYPE ensures record structure matches the cursor or table columns.*

5. Can RECORDs be used in procedures and functions?

Yes. RECORDs can be passed as parameters to PL/SQL subprograms:

CREATE OR REPLACE PROCEDURE print_emp(p_emp IN employees%ROWTYPE) IS

BEGIN

   DBMS_OUTPUT.PUT_LINE(p_emp.first_name || ' earns ' || p_emp.salary);

END;

Call with a populated RECORD variable.

6. Can a RECORD contain another RECORD?

Yes, you can nest RECORD types:

DECLARE

   TYPE address_rec IS RECORD (

      street VARCHAR2(50),

      city   VARCHAR2(30)

   );

 

   TYPE emp_rec_type IS RECORD (

      emp_id     NUMBER,

      emp_name   VARCHAR2(20),

      emp_address address_rec

   );

 

   emp_rec emp_rec_type;

BEGIN

   emp_rec.emp_id := 101;

   emp_rec.emp_name := 'John';

   emp_rec.emp_address.street := '123 Main St';

   emp_rec.emp_address.city := 'New York';

 

   DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name || ' lives in ' || emp_rec.emp_address.city);

END;

7. What are the advantages of using RECORD types?

• Group related data into one variable
• Avoid declaring multiple separate variables
• Work naturally with tables and cursors using %ROWTYPE
• Simplify procedure/function parameters
• Improve code readability and maintainability

8. Common mistakes when using RECORDs

• Forgetting to match data types when using explicit fields
• Using uninitialized RECORDs in operations
• Confusing RECORD fields with PL/SQL scalar variables
• Not using %ROWTYPE when table/column structure may change

9. Best practices

• Use %ROWTYPE when working with tables or cursors for easier maintenance
• Use dot notation for clarity (record.field)
• Avoid hardcoding field types; prefer dynamic structure matching with %ROWTYPE
• Use RECORDs in bulk processing or procedure parameters for modular code

 

Composite Data Types FAQS

1. What are composite data types in Oracle?

Composite data types are data structures that can store multiple values in a single variable. They allow grouping related data together for easier manipulation in PL/SQL.

Oracle supports these main composite types:

• Records – group different data types under one variable
• Collections – group multiple elements of the same type
• Associative Arrays (Index-by tables)
• Nested Tables
• VARRAYs (Variable-size arrays)

2. What is a RECORD type in Oracle?

A record is a composite type that allows storing a row of data with multiple fields. Each field can have a different data type.

Example:

DECLARE

   TYPE emp_rec_type IS RECORD (

      emp_id     employees.employee_id%TYPE,

      emp_name   employees.first_name%TYPE,

      emp_salary employees.salary%TYPE

   );

   emp_rec emp_rec_type;

BEGIN

   emp_rec.emp_id := 101;

   emp_rec.emp_name := 'John';

   emp_rec.emp_salary := 5000;

   DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name || ' earns ' || emp_rec.emp_salary);

END;

Use case: Combine related fields and pass them around as a single variable.

3. What are collections in Oracle?

Collections are composite types that store multiple elements of the same type. Oracle supports three collection types:

Type	Description
Associative Array	Key-value pair table (index-by table)
Nested Table	Unordered set of elements that can be stored in database columns
VARRAY	Ordered, bounded array of elements; maintains order

4. What is an associative array (index-by table)?

• Uses unique keys to reference elements
• Can have integer or string keys
• Fully in-memory, used for fast lookups

Example:

DECLARE

   TYPE emp_table IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;

   emp_names emp_table;

BEGIN

   emp_names(1) := 'Alice';

   emp_names(2) := 'Bob';

   DBMS_OUTPUT.PUT_LINE(emp_names(2)); -- Outputs 'Bob'

END;

5. What is a nested table?

• A nested table is unordered and unbounded
• Can be stored in database columns
• Can be manipulated with SQL and PL/SQL

Example:

DECLARE

   TYPE emp_nt_type IS TABLE OF employees.first_name%TYPE;

   emp_nt emp_nt_type := emp_nt_type();

BEGIN

   emp_nt.EXTEND;

   emp_nt(1) := 'Alice';

   emp_nt.EXTEND;

   emp_nt(2) := 'Bob';

   DBMS_OUTPUT.PUT_LINE(emp_nt(2)); -- Outputs 'Bob'

END;

6. What is a VARRAY?

• A variable-size array with a maximum size limit
• Maintains order
• Can be stored in database tables

Example:

DECLARE

   TYPE emp_varray_type IS VARRAY(5) OF employees.first_name%TYPE;

   emp_varray emp_varray_type := emp_varray_type('Alice', 'Bob');

BEGIN

   DBMS_OUTPUT.PUT_LINE(emp_varray(1)); -- Outputs 'Alice'

END;

7. How do you access elements in composite types?

• Records: Use record_name.field_name
• Collections: Use collection_name(index)

Example with record and collection:

DECLARE

   TYPE emp_rec_type IS RECORD (id NUMBER, name VARCHAR2(20));

   TYPE emp_nt_type IS TABLE OF emp_rec_type;

   emp_list emp_nt_type := emp_nt_type();

BEGIN

   emp_list.EXTEND;

   emp_list(1).id := 101;

   emp_list(1).name := 'John';

   DBMS_OUTPUT.PUT_LINE(emp_list(1).name);

END;

8. Can composite types be used in procedures/functions?

Yes. You can pass records or collections as parameters:

CREATE OR REPLACE PROCEDURE print_emp_names(p_emps IN emp_nt_type) IS

BEGIN

   FOR i IN 1..p_emps.COUNT LOOP

      DBMS_OUTPUT.PUT_LINE(p_emps(i));

   END LOOP;

END;

Great for passing multiple values without creating multiple parameters.

9. Advantages of composite data types

• Reduce code complexity by grouping related data
• Allow row-wise and set-wise operations in PL/SQL
• Support modular and reusable code
• Can interface with SQL (nested tables and VARRAYs)

10. Common mistakes with composite types

• Forgetting to initialize collections before use (EXTEND or constructor)
• Exceeding VARRAY size limits
• Misusing associative array indexes
• Using uninitialized records

11. Best practices

• Use records for row-like structures
• Use associative arrays for in-memory lookups
• Use nested tables for sets that need SQL interaction
• Use VARRAYs for small, fixed-size, ordered lists
• Always initialize collections before adding elements