So far we have seen how to create Table based and cursor based record datatype variables. The one that is left is the user define record datatype which we are going to cover in today’s tutorial.

As the name suggests, user define records are the record variables whose structure is defined by the user, which is unlike the table based or cursor based records whose structures are derived from their respective tables or cursor. This means that with user define records you can have complete control over the structure of your record variable.

The creation process of user define record variable is divided into two parts. Before defining the record we first need to define the TYPE for the record variable. This TYPE will become the base of the User Define Record variable and will help in driving its structure. Once the TYPE is successfully declared then we can use it for creating our user define record variable.

Syntax of User Define Records in Oracle Database

Below is the syntax for creating the TYPE for User Defined Record Datatype Variable.

TYPE type_name IS RECORD (
field_name1 datatype 1,
field_name2 datatype 2,
...
field_nameN datatype N 
);

Once we have our TYPE declared we are all set to create our Record Variable. This variable will then acquire all the properties of the type using which it is created. And here is the syntax for creating the user define record datatype variable.

record_name TYPE_NAME;

Did you notice that unlike the Table based or Cursor Based Record Variable we do not have to use %ROWTYPE attribute here for declaring the record variable?

You can watch the Video Tutorial on my YouTube channel for a detailed explanation of the above syntax.

Example: How To Create User Defined Record Datatype Variable.

Step 1: Declare Type for the User Defined Record Variable

SET SERVEROUTPUT ON;
DECLARE
  TYPE rv_dept IS RECORD(
    f_name  VARCHAR2(20),
    d_name  DEPARTMENTS.department_name%TYPE 
  );

Step 2: Declare User Define Record Variable

After creating the TYPE you are all set to create your User Defined Record Variable.

var1 rv_dept;

This above PL/SQL statement will create a record variable with the name VAR1.

Step 3: Initialize the User Defined Record Variable.

User defined record variable can be initialized in multiple ways. For instance you can initialize the record variable directly by assigning value to it using assignment variable or you can fetch the values stored into the column of a table using SELECT-INTO statement. So let’s move ahead with our example and learn how to initialize a user defined record variable using SELECT-INTO statement.

Next I will write the execution section. In the execution section we will have a SELECT statement which will be joining employees table and departments table and returning the first name and department name of the specific employee.

BEGIN
  SELECT first_name , department_name 
  INTO var1.f_name, var1.d_name
  FROM employees  join departments
  Using (department_id) WHERE employee_id = 100; 
  DBMS_OUTPUT.PUT_LINE(var1.f_name||' '||var1.d_name);
END;

The select statement which we have here will return the first name and the department name of the employee whose employee id is 100. The data for both the columns are coming from different tables thus we used a JOIN here.  As there are two different tables involved in the query thus in such a situation use of Table Based Record Variable is not possible therefore the viable solution is the user define record variable.

Let’s join all the above chunks of codes together into a single anonymous PL/SQL block.

SET SERVEROUTPUT ON;
DECLARE
  TYPE rv_dept IS RECORD(
    f_name  VARCHAR2(20),
    d_name  departments.department_name%type 
  );
  var1 rv_dept;
BEGIN
  SELECT first_name , department_name 
  INTO var1.f_name, var1.d_name
  FROM employees  join departments
  Using (department_id) WHERE employee_id = 100;
  
  DBMS_OUTPUT.PUT_LINE(var1.f_name||' '||var1.d_name);
END;
/

That is how we create a User Defined Record Datatype Variable in Oracle Database. Hope you enjoyed reading and learnt something new. Also I am giving away RebellionRider’s merchandise to one randomly selected winner every month so do make sure to share this blog with your friends on your social.  

That’s all folks thanks & have a great day!