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.
A record is not a collection, so your explanation is slightly confused. From the docs (which you've probably already looked at:
So looking at what you said:
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 theid
column from thetblName
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 avarchar2
column is increased, for instance).Correct - a variable of the newly created type is being created.
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:
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.