How to Use Record Types as Return Values in PL/SQL

Oracle PL/SQL provides robust data structures to manage complex data. One such structure is the record type, which can also be used as a function return type. This allows developers to encapsulate multiple columns into a single return value. In this guide, we explore how to define and use record types as return values in PL/SQL with real examples.

Defining Record Types for Return Values in PL/SQL

You can only use explicitly defined record types when returning records from a function in PL/SQL. The %ROWTYPE attribute is not allowed as a function return type.

Here’s how to do it:

DECLARE
  -- Define an explicit record type
  TYPE individual_record IS RECORD (
    individual_id   INTEGER,
    first_name      VARCHAR2(30 CHAR),
    middle_initial  individuals.middle_initial%TYPE,
    last_name       VARCHAR2(30 CHAR)
  );

  -- Define a variable of the above record type
  individual individual_record;

  -- Define a function that returns the record type
  FUNCTION get_row(individual_id_in INTEGER)
  RETURN individual_record IS

    -- Define a cursor to retrieve data
    CURSOR c(individual_id_cursor INTEGER) IS
      SELECT *
      FROM individuals
      WHERE individual_id = individual_id_cursor;

  BEGIN
    -- Use a cursor FOR loop to fetch data
    FOR i IN c(individual_id_in) LOOP
      RETURN i;
    END LOOP;
  END get_row;

BEGIN
  -- Assign the function return value to the variable
  individual := get_row(1);

  -- Output the result
  dbms_output.put_line(CHR(10));
  dbms_output.put_line('INDIVIDUAL_ID  : ' || individual.individual_id);
  dbms_output.put_line('FIRST_NAME     : ' || individual.first_name);
  dbms_output.put_line('MIDDLE_INITIAL : ' || individual.middle_initial);
  dbms_output.put_line('LAST_NAME      : ' || individual.last_name);
END;
/

Output Example

If the record exists in the individuals table, the console will show:

INDIVIDUAL_ID  : 1  
FIRST_NAME     : John  
MIDDLE_INITIAL : D  
LAST_NAME      : Rockefeller

Explanation

This example does the following:

Step	Description
1️⃣	Defines an explicit record type `individual_record`.
2️⃣	Declares a function `get_row` returning this record type.
3️⃣	Uses a cursor FOR loop to retrieve a matching row.
4️⃣	Returns the row and assigns it to a variable of the same type.
5️⃣	Uses `DBMS_OUTPUT` to print values.

Benefits of Using Record Types as Return Values in PL/SQL

Encapsulation: Groups multiple values into a single return.
Clarity: Makes code more readable and structured.
Reusability: Record types can be reused in multiple subprograms.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.