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