By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (id1, name1, id2, name2, id3, name3, id4, name4) AS
SELECT 1, 'N1', 2, 'N2', 3, 'N3', 4, 'N4' FROM DUAL;
1 rows affected
DECLARE
data TABLE_NAME%ROWTYPE;
BEGIN
SELECT * INTO data FROM table_name FETCH FIRST ROW ONLY;
DBMS_OUTPUT.PUT_LINE('1: ' || data.id1 || ', ' || data.name1);
DBMS_OUTPUT.PUT_LINE('2: ' || data.id2 || ', ' || data.name2);
DBMS_OUTPUT.PUT_LINE('3: ' || data.id3 || ', ' || data.name3);
DBMS_OUTPUT.PUT_LINE('4: ' || data.id4 || ', ' || data.name4);
END;
/
1 rows affected
dbms_output:
1: 1, N1
2: 2, N2
3: 3, N3
4: 4, N4
DECLARE
CURSOR cur IS
SELECT id, name
FROM (SELECT * FROM table_name FETCH FIRST ROW ONLY)
UNPIVOT (
(id, name)
FOR idx IN (
(id1, name1) AS 1,
(id2, name2) AS 2,
(id3, name3) AS 3,
(id4, name4) AS 4
)
);
TYPE cur_row_arr IS TABLE OF cur%ROWTYPE;
rw cur%ROWTYPE;
arr cur_row_arr := cur_row_arr();
BEGIN
OPEN cur;
LOOP
FETCH cur INTO rw;
EXIT WHEN cur%NOTFOUND;
arr.EXTEND;
arr(arr.COUNT) := rw;
END LOOP;
CLOSE cur;
FOR i IN 1 .. arr.COUNT LOOP
DBMS_OUTPUT.PUT_LINE( i || ': ' || arr(i).id || ', ' || arr(i).name );
END LOOP;
END;
/
1 rows affected
dbms_output:
1: 1, N1
2: 2, N2
3: 3, N3
4: 4, N4