PL SQL - Declare an array of objects and loop insert statements

While gathering informations about how an array with objects can be declared in PlSQL and the processed by aa for loop, I ended up with very different results. Some people created temporary tables and other defined complex new types. It was difficult to see through. By prioritizing simpler and more common approaches, I ended up with a suitable solution.

Before we have a look at the the PlSQL script, let me introduce you to the scenario I had to resolve.

In my case there is a profile table (T_PROFILE) with an id (ID) and name (NAME) attribute. This table has an n:m relationship with a profile table (T_PROFILE) containing the same attributes. Roles are assigned to profiles in an authorization (T_AUTHORIZATION) table. A test data set for the authorization table had to be defined and its entries had to be inserted into the authorization table.

My approach to resolve this scenario was simple.

First I declared the required types, then populated the array structure, looped the array and finally inserted the authorization entries.

DECLARE

    index INT;
	role_id INT;
	profile_id INT;

    -- Create an object type
	TYPE t_authorization IS RECORD(
        role_name VARCHAR2(50),
        profile_name VARCHAR2(50)
	);
    -- Create an array type consisting of the authorization type
	TYPE t_authorizations IS TABLE OF t_authorization INDEX BY pls_integer;

    -- Declare the array
	authorizations t_authorizations;

BEGIN

	-- Add new authorization entries
	index := 1;
	authorizations(index).role_name := 'Administrator';
	authorizations(index).profile_name := 'Janik';

	-- Every entry is process by the loop statement
	FOR i IN authorizations.FIRST .. authorizations.LAST
	LOOP

        -- Look up the id for profile and role
		SELECT ID into role_id FROM T_ROLE WHERE NAME = authorizations(i).role_name;
		SELECT ID into profile_id FROM T_PROFILE WHERE NAME = authorizations(i).profile_name;

        -- Add a new authorization
		INSERT INTO T_AUTHORIZATION (id,ctl_cre_dat,ctl_mod_dat,profile_id,role_id)
			VALUES (S_AUTHORIZATION.NEXTVAL,SYSDATE,SYSDATE,profile_id,role_id);

	END LOOP;
END;

Actually super simple.