可以使用UNPVIVOT和LISTAGG。

SELECT ID,   
    '['|| 
        LISTAGG ('{' || col || ':' || VALUE || '}', ',')
        WITHIN GROUP (ORDER BY ROWNUM)
    || ']'  key_value_pair
FROM (SELECT id,Name, TO_CHAR (age) age, Country FROM yourtable) 
UNPIVOT (VALUE FOR col IN (Name, Age, Country)) 
GROUP BY id;

输出

| ID |                      KEY_VALUE_PAIR |
|----|-------------------------------------|
|  1 | [{NAME:AAAA},{AGE:12},{COUNTRY:AU}] |
|  2 | [{NAME:BBBB},{AGE:13},{COUNTRY:CC}] |

演示

你说你有一个TYPE KEYVALUEPAIR<VARCHAR,VARCHAR>。你想用这种东西吗？

DECLARE
TYPE KEYVALUEPAIR
IS
  RECORD
  (
    KEY_   VARCHAR2(12),
    VALUE_ VARCHAR2(12) );
TYPE RECTYPE IS VARRAY(10) OF KEYVALUEPAIR;
RECID1 RECTYPE;
BEGIN
  SELECT col,
    VALUE BULK COLLECT
  INTO RECID1
  FROM
    (SELECT id, Name, TO_CHAR (age) age, Country FROM yourtable WHERE ID = 1
    ) UNPIVOT (VALUE FOR col IN (Name, Age, Country)) ;
END;
/

我不确定你的主要要求是使用VARARY集合还是使用你提到的格式的数据。
如果你需要使用VARRAY集合，这里有一个例子：

declare
    type t_1 is varray(10) of test_1%ROWTYPE;
    v_1 t_1:=t_1();
    j number;
begin
    v_1.extend(2);
    j := 1;
    for i in (select * from test_1)
    loop
        v_1(j).id       := i.id;
        v_1(j).name     := i.name;
        v_1(j).age      := i.age;
        v_1(j).country  := i.country;
        j := j + 1;
    end loop;

    for j in v_1.first .. v_1.last
    loop
        dbms_output.put_line ('v_1(' || j || ').id = ' || v_1(j).id);
        dbms_output.put_line ('v_1(' || j || ').name = ' || v_1(j).name);
        dbms_output.put_line ('v_1(' || j || ').age = ' || v_1(j).age);
        dbms_output.put_line ('v_1(' || j || ').country = ' || v_1(j).country);
        dbms_output.put_line ('---------------------------------');        
    end loop;
end;

select id, 
       json_array( json_object('name' value name)
                 , json_object('age' value age)
                 , json_object('country' value country)) key_values
  from 
  ( select 1 id, 'AAAA' name, 12 age, 'AU' country from dual 
     union all
    select 2 id, 'BBBB' name, 13 age, 'CC' country from dual ) a