---
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/oracle-plsql-records-type.png
---

 

[Skip to content](#main) 

**⚡ Smart Summary**

PL/SQL Record Type is a complex data type that groups several columns, each with its own name and data type, into one new type. A record can be defined at the database level as a stored object or inside a subprogram, and its fields are reached with the dot operator.

* 🧱 **Definition:** A record type groups one or more columns into a single new data type.
* 🔑 **Keyword:** The TYPE keyword tells the compiler a new data type is being created.
* 🏛️ **Two Levels:** A database-level record is a stored object; a subprogram-level record is visible only inside that subprogram.
* 🔗 **Field Access:** Fields are reached as record\_variable.column\_name using the dot operator.
* 📥 **Column-level:** Values can be assigned to one field at a time.
* 📦 **Row-level:** A SELECT INTO can populate a whole record from a table row.
* 🧩 **Record Kinds:** Table-based %ROWTYPE, cursor-based %ROWTYPE, and programmer-defined records.

[ Read More ](javascript:void%280%29;) 

![Oracle PL/SQL Records Type](https://www.guru99.com/images/oracle-plsql-records-type.png)

## What is a Record Type?

A record type is a complex data type that allows the programmer to create a new data type with the desired column structure. Its main characteristics are:

* It groups one or more columns to form a new data type.
* Each of these columns has its own name and data type.
* A record type can accept the data as a single record consisting of many columns, or it can accept the value for one particular column of a record.
* A record type is simply a new data type. Once created, it is stored as a new data type and can be used to declare a variable in programs.
* It uses the keyword **‘TYPE’** to instruct the compiler that a new data type is being created.
* It can be created at the **database level**, stored as a database object and used across the database, or at the **subprogram level**, visible only inside the subprogram.
* A database-level record type can also be declared for table columns, so that a single column can hold complex data.
* The data is accessed by referring to the variable name, then a period operator (.), then the column name, as ‘<record\_type\_variable\_name>.<column\_name>’.

## Syntax to Declare a Record Type

**Syntax for declaration at the database level:**

[](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, the keyword ‘CREATE TYPE’ instructs the compiler to create the record type named “type\_name\_db” with the specified columns as a database object. This is given as an individual statement, not inside any block.

**Syntax for declaration at the subprogram level:**

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

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

In this syntax, we create the record type named “type\_name” only inside the subprogram. In both methods, the way of defining the column and data type is similar.

## Example 1: Record Type as a Database Object

In this program, we see how to create a record type as a database object. We create the record type ’emp\_det’ with four columns. The columns and their data types are:

* 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 creates the type emp\_det as a database object.
* It has 4 columns: emp\_no, emp\_name, manager, and salary, as defined.
* Now ’emp\_det’ is similar to any other [data type](https://www.guru99.com/complex-data-types-pl-sql.html) (like NUMBER or VARCHAR2) and is visible across the entire database. Hence it can be used anywhere in the database to declare a variable of this type.

### RELATED ARTICLES

* [Oracle PL/SQL IF THEN ELSE Statement: ELSIF, NESTED-IF ](https://www.guru99.com/pl-sql-decision-making-statements.html "Oracle PL/SQL IF THEN ELSE Statement: ELSIF, NESTED-IF")
* [Oracle PL/SQL LOOP with Example ](https://www.guru99.com/loops-pl-sql.html "Oracle PL/SQL LOOP with Example")
* [Oracle PL/SQL Stored Procedure & Functions with Examples ](https://www.guru99.com/subprograms-procedures-functions-pl-sql.html "Oracle PL/SQL Stored Procedure & Functions with Examples")
* [PL/SQL Tutorial for Beginners ](https://www.guru99.com/pl-sql-tutorials.html "PL/SQL Tutorial for Beginners")

## Example 2: Subprogram Level, Column-level Access

In this example, we see how to create a record type at the subprogram level and how to populate and fetch values from it by column. We create the ’emp\_det’ record type at the subprogram level and use it to populate and display data.

[](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, manager, and salary of data type NUMBER, VARCHAR2, NUMBER, and NUMBER.
* **Code line 9:** The guru99\_emp\_rec variable is declared as ’emp\_det’ data type. This [variable](https://www.guru99.com/pl-sql-identifiers.html) can hold a value that contains all four fields.
* **Code line 11:** Populating the ’emp\_no’ field of ‘guru99\_emp\_rec’ with value 1001.
* **Code line 12:** Populating the ’emp\_name’ field with value XXX.
* **Code line 13:** Populating the ‘manager’ field with value 1000.
* **Code line 14:** Populating the ‘salary’ field with value 10000.
* **Code line 15-19:** Displaying the value of ‘guru99\_emp\_rec’ in the output.

## Example 3: Subprogram Level, Row-level Access

In this example, we see how to create a record type at the subprogram level and populate it at the row level. We create the ’emp\_det’ record type at the subprogram level and use it to populate and display data.

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

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(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, manager, and salary of data type NUMBER, VARCHAR2, NUMBER, and NUMBER.
* **Code line 9:** The guru99\_emp\_rec variable is declared as ’emp\_det’ data type and can hold all four fields.
* **Code line 11:** Populating the table emp with 1002 as emp\_no, YYY as emp\_name, 15000 as salary, and 1000 as manager number.
* **Code line 12:** Committing the insert transaction.
* **Code line 13:** Populating the ‘guru99\_emp\_rec’ variable at the row level from the select query for employee number 1002.
* **Code line 15-19:** Displaying the value of ‘guru99\_emp\_rec’ in the output.

**Output:**

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

**Note:** A record type can be accessed only at the column level when redirecting its value to any output mode. Notice that in this example the SELECT list order does not match the record field order, which is why the salary and manager values appear swapped in the output.

## Types of PL/SQL Records

Beyond the programmer-defined record shown above, Oracle offers two anchored record kinds that copy their structure automatically. Using them avoids re-declaring columns and keeps the record in step with the table or cursor it is based on.

| Record kind               | Declared with        | Structure comes from     |
| ------------------------- | -------------------- | ------------------------ |
| Table-based record        | table\_name%ROWTYPE  | All columns of a table   |
| Cursor-based record       | cursor\_name%ROWTYPE | The cursor’s select list |
| Programmer-defined record | TYPE … IS RECORD     | Columns you list by hand |

A %ROWTYPE record is the safest choice when the shape should follow a table or a [cursor](https://www.guru99.com/pl-sql-cursor.html), because a column change is picked up automatically on the next compile.

## FAQs

🧱 What is the difference between a record and a collection?

A record groups different columns of possibly different types into one row-like structure. A collection holds many elements of the same type indexed by a subscript. They are often combined, a collection of records.

🔗 What is %ROWTYPE and when should it be used?

%ROWTYPE declares a record whose fields match a table or cursor automatically. Use it when the record should mirror that source, so a column change is reflected without editing the declaration.

📥 Can a whole record be assigned in one statement?

Yes, if both records are of the same type. SELECT INTO can also fill an entire record from a table row, provided the select list order matches the field order of the record.

🤖 Can AI generate a record type from a table?

Yes. AI can read a table definition and produce a matching TYPE … IS RECORD block, or recommend a %ROWTYPE where a live link to the table is preferable. Verify data types before use.

🏛️ What is the difference between a database-level and subprogram-level record?

A database-level record is created with CREATE TYPE and stored as an object visible across the database. A subprogram-level record is declared inside a block and exists only within that subprogram.

#### 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]() 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/oracle-plsql-records-type.png","url":"https://www.guru99.com/images/oracle-plsql-records-type.png","width":"700","height":"250","caption":"Oracle PL/SQL Records Type","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":"2026-07-22T16:26:52+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/oracle-plsql-records-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"}},{"articleSection":"PL-SQL","headline":"Oracle PL/SQL Records Type with Examples","description":"A Record type is a complex data type which allows the programmer to create a new data type with the desired column structure.","keywords":"pl-sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"dateModified":"2026-07-22T16:26:52+05:30","image":{"@id":"https://www.guru99.com/images/oracle-plsql-records-type.png"},"copyrightYear":"2026","name":"Oracle PL/SQL Records Type with Examples","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between a record and a collection?","acceptedAnswer":{"@type":"Answer","text":"A record groups different columns of possibly different types into one row-like structure. A collection holds many elements of the same type indexed by a subscript. They are often combined, a collection of records."}},{"@type":"Question","name":"What is %ROWTYPE and when should it be used?","acceptedAnswer":{"@type":"Answer","text":"%ROWTYPE declares a record whose fields match a table or cursor automatically. Use it when the record should mirror that source, so a column change is reflected without editing the declaration."}},{"@type":"Question","name":"Can a whole record be assigned in one statement?","acceptedAnswer":{"@type":"Answer","text":"Yes, if both records are of the same type. SELECT INTO can also fill an entire record from a table row, provided the select list order matches the field order of the record."}},{"@type":"Question","name":"Can AI generate a record type from a table?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI can read a table definition and produce a matching TYPE ... IS RECORD block, or recommend a %ROWTYPE where a live link to the table is preferable. Verify data types before use."}},{"@type":"Question","name":"What is the difference between a database-level and subprogram-level record?","acceptedAnswer":{"@type":"Answer","text":"A database-level record is created with CREATE TYPE and stored as an object visible across the database. A subprogram-level record is declared inside a block and exists only within that subprogram."}}]}],"@id":"https://www.guru99.com/pl-sql-record-type.html#schema-1148901","isPartOf":{"@id":"https://www.guru99.com/pl-sql-record-type.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/pl-sql-record-type.html#webpage"}}]}
```