---
description: A Record type is a complex data type which allows the programmer to create a new data type with the desired column structure.
title: Oracle PL/SQL Records Type with Examples
image: https://www.guru99.com/images/PL-SQL/110215_0744_ComplexData1.png
---

 

[Skip to content](#main) 

##  What is Record Type?

A Record type is a complex data type which allows the programmer to create a new data type with the desired column structure.

* 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:**

[![Oracle PL/SQL Records Type](https://www.guru99.com/images/PL-SQL/110215_0744_ComplexData1.png)](https://www.guru99.com/images/PL-SQL/110215%5F0744%5FComplexData1.png)

CREATE TYPE <type_name_db> IS RECORD
(
<column 1> <datatype>,
);

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:**

[![Oracle PL/SQL Records Type](https://www.guru99.com/images/PL-SQL/110215_0744_ComplexData2.png)](https://www.guru99.com/images/PL-SQL/110215%5F0744%5FComplexData2.png)

DECLARE
TYPE <type_name> IS RECORD
(
<columnl> <datatype>,
);
BEGIN
<execution_section>;
END;

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)

CREATE TYPE emp_det IS OBJECT
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER 
);
/

**Output:**

Type created

**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](https://www.guru99.com/complex-data-types-pl-sql.html) (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.

[![Oracle PL/SQL Records Type](https://www.guru99.com/images/PL-SQL/110215_0744_ComplexData4.png)](https://www.guru99.com/images/PL-SQL/110215%5F0744%5FComplexData4.png)

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER, 
EMP_NAME VARCHAR2(150),
MANAGER NUMBER, 
SALARY NUMBER
);
guru99_emp_rec emp_det;
BEGIN
guru99_emp_rec.emp_no:= 1001;	
guru99_emp_rec.emp_name:=:'XXX';	
guru99_emp_rec.manager:= 1000;	
guru99_emp_rec.salary:=10000;	
dbms_output.put.line('Employee Detail');
dbms_output.put_line ('Employee Number: '||guru99_emp_rec.emp_no); 
dbms_output.put_line ('Employee Name: '||guru99_emp_rec.emp_name); 
dbms_output.put_line ('Employee Salary: ' ||guru99_emp_rec.salary); 
dbms_output.put_line ('Employee Manager Number: '||guru99_emp_rec.manager);
END;
/

**Output:**

Employee Detail
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000 
Employee Manager Number: 1000

**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](https://www.guru99.com/pl-sql-identifiers.html) 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.

[![Oracle PL/SQL Records Type](https://www.guru99.com/images/PL-SQL/110215_0744_ComplexData6.png)](https://www.guru99.com/images/PL-SQL/110215%5F0744%5FComplexData6.png)

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME YARCHAR2( 150),
MANAGER NUMBER,
SALARY NUMBER
);
guru99_emp_rec emp_det;
BEGIN
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
COMMIT;
SELECT emp_no, emp_name, salary, manager INTO guru99_emp_rec FROM emp WHERE emp_no=1002;
dbms_output.put_line (‘Employee Detail’);
dbms_output.put_line (‘Employee Number: '||guru99_emp_rec.emp_no); 
dbms_output.put_line (‘Employee Name: '||guru99_emp_rec.emp_name); 
dbms_output.put_line (‘Employee Salary: '||guru99_emp_rec. salary); 
dbms_output.put_line (‘Employee Manager Number: '||guru99_emp_rec.manager);
END;
/

**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:**

Employee Detail 
Employee Number: 1002 
Employee Name: YYY 
Employee Salary: 1000 
Employee Manager Number: 15000

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

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter](https://www.guru99.com/images/footer-email-avatar-imges-1.png) Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/pl-sql-record-type.png","url":"https://www.guru99.com/images/pl-sql-record-type.png","width":"542","height":"162","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/pl-sql-record-type.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/pl-sql","name":"PL-SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/pl-sql-record-type.html","name":"Oracle PL/SQL Records Type with Examples"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/pl-sql-record-type.html#webpage","url":"https://www.guru99.com/pl-sql-record-type.html","name":"Oracle PL/SQL Records Type with Examples","dateModified":"2024-06-28T17:19:32+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/pl-sql-record-type.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/pl-sql-record-type.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown","description":"I'm Fiona brown, a Full Stack Developer with over a decade of experience, sharing practical guides on robust and scalable application development.","url":"https://www.guru99.com/author/fiona","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/fiona-brown-author.png","url":"https://www.guru99.com/images/fiona-brown-author.png","caption":"Fiona Brown","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"@type":"NewsArticle","headline":"Oracle PL/SQL Records Type with Examples","keywords":"pl-sql","dateModified":"2024-06-28T17:19:32+05:30","articleSection":"PL-SQL","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"publisher":{"@id":"https://www.guru99.com/#organization"},"description":"A Record type is a complex data type which allows the programmer to create a new data type with the desired column structure.","copyrightYear":"2024","copyrightHolder":{"@id":"https://www.guru99.com/#organization"},"name":"Oracle PL/SQL Records Type with Examples","@id":"https://www.guru99.com/pl-sql-record-type.html#richSnippet","isPartOf":{"@id":"https://www.guru99.com/pl-sql-record-type.html#webpage"},"image":{"@id":"https://www.guru99.com/images/pl-sql-record-type.png"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/pl-sql-record-type.html#webpage"}}]}
```