It groups one or more column to form a new data type These columns will have its own name and data type A Record type can accept the data

As a single record that consists of many columns OR It can accept the value for one particular column of a record

Record type simply means a new data type. Once the record type is created, it will be stored as a new data type in the database and the same shall be used to declare a variable in programs. It will use the keyword ‘TYPE’ to instruct the compiler that it is creating the new data type. It can be created at “database level” which can be stored as database objects, used all-over the database or it can be created at the “subprogram levels”, which is visible only inside the subprograms. The database level record type can also be declared for the table columns so that single column can hold the complex data. The data in these data type can be accessed by referring to their variable_name followed by period operator (.) followed by column_name i.e. ‘<record_type_variable_name>.<column_name>’

Syntax for declaration at the database level:

In the first syntax, we can see the keyword ‘CREATE TYPE’ this instructs the compiler to create the record type named “type_name_db” with the specified column as a database object. This is given as an individual statement and not inside any block. Syntax for declaration at subprogram level:

In the syntax, we are creating the record type named “type_name” only inside the subprogram. In both declaration method, the way of defining the column and data type is similar.
Example 1: RECORD Type as Database Object In this program, we are going to see how to create “Record type” as a database object. We are going to create record type ’emp_det’ with four columns. The columns and their data type are as follows:

EMP_NO (NUMBER) EMP_NAME (VARCHAR2 (150)) MANAGER (NUMBER) SALARY (NUMBER)

Output: Code Explanation:

The above code will create type emp_det as a database object. It will have 4 column emp_no, emp_name, manager and salary as defined. Now ’emp_det’ is a similar to other data type (like NUMBER, VARCHAR@, etc.) And it is visible in the entire database. Hence this can be used in the entire database to declare the variable of this type.

Output: Created the type ’emp_det’ as record type at the database level. Example 2: Record Type at Subprogram level- Column level access In this example, we are going to see how to create a record type at subprogram level and how to populate and fetch the values from it by column level. We are going to create ’emp_det’ record_type at subprogram level, and we are going to use the same to populate and to display data from it.

Output: Code Explanation:

Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER. Code line 9: guru99_emp_rec variable is declared as ’emp_det’ data type. Now this variable can hold the value that contains all the above 4 fields/columns. Code line 11: Populating the ’emp_no’ field of ‘guru99_emp_rec’ with value 1001. Code line 12: Populating the ’emp_name’ field of ‘guru99_emp_rec’ with value XXX. Code line 13: Populating the ‘manager’ field of ‘guru99_emp_rec’ with value 1000. Code line 14: Populating the ‘salary’ field of ‘guru99_emp_rec’ with value 10000. Code line 15-19: Displaying the value of the ‘guru99_emp_rec’ in output.

Example 3: Record Type at Subprogram level-Row level access In this example, we are going to see how to create a record type at subprogram level and how to populate it as a row level. We are going to create ’emp_det’ record_type at subprogram level, and we are going to use the same to populate and to display data from it.

Code Explanation:

Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER. Code line 9: guru99_emp_rec variable is declared as ’emp_det’ data type. Now this variable can hold the value that contains all the above 4 fields/columns. Code line 11: Populating the table emp with data 1002 as emp_no, YYY as emp_name, 15000 as salary and 1000 as manager number. Code line 12: Committing the above insert transaction. Code line 13: Populating the ‘guru99_emp_rec’ variable as a row level data from the select query for employee number 1002. Code line 15-19: Displaying the value of the ‘guru99_emp_rec’ in output.

Output: Note: The record type can be accessed only in column level while redirecting its value to any output mode.