Great!. As usual.
I was just thinking about the same method using varray.
But was stuck with this requirement.Quote:

If the KEYWORD is found, then the next value determines how many value pairs will follow, and therefore how many rows should be created in the affected DB table.

Had no idea how to do it with native means.
The sample data is clean. For key value 4, there are exactly 4 pairs.
If the key value was 3, it should load only 3 out of 4 pairs.
Probably just load all data into staging table and use sql ?.

You can use a count of the varrays instead of delimited by whitespace, as demonstrated below. In the example below, I changed the 4 in the data line with 4 pairs to 3. I also added a when clause to test for the keyword and included a line in the data that does not have the keyword.