i have this code..,. i dont see what's wrong why its only working on the first record of LOOP of X
set serveroutput on size 30000;
DECLARE
VTEMP NUMBER;
VPARENT NUMBER;
BEGIN
FOR X IN (
SELECT TEMPLATE_ID, TYPE_ID, TYPE_KIND, RECORD_ID FROM RT_RECORDS
WHERE TEMPLATE_ID = 106
)LOOP
DBMS_OUTPUT.PUT_LINE('TYPE_ID X :' || X.TYPE_ID );
FOR Y IN (
SELECT OBJECT_ID, OBJECT_TYPE CHILD_TYPE, LEVEL,
PARENT_ID, RPA_N.GETTYPEID(PARENT_ID)PARENT_TYPE
FROM NODES N
CONNECT BY PRIOR N.OBJECT_ID = N.PARENT_ID
START WITH N.OBJECT_ID = 18464078 ) LOOP
DBMS_OUTPUT.PUT_LINE('TYPE_ID :' || X.TYPE_ID || ' PARENT_ID: ' || Y.PARENT_TYPE );
IF X.TYPE_ID = Y.PARENT_TYPE THEN
-- CHILD
SELECT RECORD_ID INTO VTEMP FROM RT_RECORDS WHERE TEMPLATE_ID = 106
AND TYPE_ID = Y.CHILD_TYPE;
-- PARENT
SELECT RECORD_ID INTO VPARENT FROM RT_RECORDS WHERE TEMPLATE_ID = 106
AND TYPE_ID = Y.PARENT_TYPE;
DBMS_OUTPUT.PUT_LINE('RECORD_ID :' || VTEMP || ' PARENT_ID: ' || VPARENT );
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;upon running this will be the output
TYPE_ID X :2
TYPE_ID :2 PARENT_ID: 281
TYPE_ID :2 PARENT_ID: 2
RECORD_ID :29098 PARENT_ID: 29097
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2LOOP X has many records in it.. but it only loops on the first record
