How to create TYPE data type in PLSQL

Here is how to create the %TYPE data type in Oracle PL/SQL.

Data types for Table columns we usually define during the Table creation.

However, in PL/SQL, suppose you need an internal variable of the same as table column data type, then TYPE statement you can use it.

Let us say you have created a Table called X. It has three columns such as emp_id, emp_name, and emp_slaray.

Table X columns data-types

  • emp_id number(6)
  • emp_name varchar2 (20)
  • emp_salary number(6)

Logic to create an internal variable

DECLARE
V_SALARY X.emp_salary%TYPE
BEGIN
NULL;
END;

Now, the V_SALARY data type and emp_salary data type are the same.

With %TYPE you can create an Array

Here is the logic to create an internal array.

DECLARE 
TYPE EmpSSNarray
IS TABLE OF X.emp_id%TYPE
INDEX BY SIMPLE_INTEGER;

I have defined an array using the syntax of TYPE array_name.

Secondly, the X.emp_id%TYPE will store values same data type of emp_id of Table X.

Related posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.