PL/SQL Record and Table Variables inchanged

Question

I am new to Oracle PL/SQL and have been given some code I am being asked to modify. I have been looking through the oracle documentation and think I know what it is doing but would like to get a verification as to if my understanding is correct or not.

Here is the code:

TYPE array_rec_type IS RECORD (gla_sub tblName.id%type);

v_closes_sc   array_rec_type;

TYPE v_sc_type is TABLE OF v_closes_sc%TYPE INDEX BY BINARY_INTEGER;

sc_array  v_sc_type;

Here is what I believe is being declared here:

• TYPE array_rec_type IS RECORD (gla_sub tblName.id%type);
  • It looks like a collection is being declared using the column type from the id column from the tblName table. Which could be a number or UUID, etc.
• v_closes_sc array_rec_type;
  • A variable of the newly created type is being created
• TYPE v_sc_type is TABLE OF v_closes_sc%TYPE INDEX BY BINARY_INTEGER;
  • A new type is being created using the collection (Record) previously created a template for the table. The table will have a single column of ID.
• sc_array v_sc_type;
  • A new variable is being created to hold the new table variable.

Answer 1

A record is not a collection, so your explanation is slightly confused. From the docs (which you've probably already looked at:

In a collection, the internal components always have the same data type, and are called elements. ... 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 ... To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.

So looking at what you said:

Here is what I believe is being declared here:

TYPE array_rec_type IS RECORD (gla_sub tblName.id%type);

This is a record, not a collection. In this case it has a single field called gla_sub which is indeed being declared using the data type of the id column from the tblName table. The use of %type means that, to some extent, you don't need to know what the data type actually is, and you may not have to change your code if that changes (if the size of a varchar2 column is increased, for instance).

v_closes_sc array_rec_type;

Correct - a variable of the newly created type is being created.

TYPE v_sc_type is TABLE OF v_closes_sc%TYPE INDEX BY BINARY_INTEGER;

This is a collection, specifically an associative array. It's a sparse array whose members are instance of the record type declared above, with a numeric index. That could also be declared using the type directly, as:

TYPE v_sc_type is TABLE OF array_rec_type INDEX BY BINARY_INTEGER;

sc_array v_sc_type;

Correct - a new variable is being created of that just-declared table type.

---

Having a record type with a single field seems a bit pointless, as you could have a collection based on the table column data type. Your existing code will be expecting that structure though, so changing it isn't quite trivial.