Oracle – scalar variable and composite variable in PL/SQL oracle


I am new to Oracle and have just read that the scalar variable has no internal component whereas composite variable has an internal component.

Could you please explain what is this internal component?
How does it work? What is its purpose ?

Best Solution

You need to read the documentation about PL/SQL Records and Collections:

A composite variable's internal components are simply the structure that makes up the variable itself.


In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE.

In a record, the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with this syntax: variable_name.field_name. To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.

For example, if I create a record type:

TYPE person_rectype IS RECORD (
     forename VARCHAR2(30),
     surname  VARCHAR2(30),
     sex      VARCHAR2(1),
     dob      DATE

then declare a variable of that type:

applicant_rec person_rectype;

The variable applicant_rec has the internal components forename, surname, sex and dob which are VARCHAR2 and DATE data types.

Hope it helps...