SQL & PL/SQL

Hi All,

        Time i ran this query with only one row, it works fine but with multiple rows, it shows error arguement '0' is out of range.

        What i understand that may be array location is missing some value, what it is expected.

        Please help. Also it would be helpful if you redirect me to some similar threads. Thanks

DECLARE
L_STR VARCHAR2(100);
L_NUM NUMBER;
N NUMBER := 1;
K NUMBER;
L_EMP VARCHAR2(50);
BEGIN
    FOR J IN (WITH TEMP AS (  SELECT 'FXDQE04|Reuters_Code|4^FXDQE05|Thomson_Code|5^FXDQE06|Jameson_Code|6' EXCEPTION_RECORD FROM DUAL
                                               UNION
                                               SELECT 'FXDQE07|Reuters_Code|7^FXDQE08|Thomson_Code|8^FXDQE09|Jameson_Code|9' EXCEPTION_RECORD FROM DUAL)
                    SELECT EXCEPTION_RECORD FROM TEMP)
        LOOP
           L_STR:=J.EXCEPTION_RECORD;
              BEGIN
                    SELECT LENGTH(L_STR)-LENGTH(REPLACE(L_STR,'^')) INTO L_NUM FROM DUAL;
                    DBMS_OUTPUT.PUT_LINE(L_NUM);
                    DBMS_OUTPUT.PUT_LINE(L_STR);
                      IF L_NUM=0 THEN
                         DBMS_OUTPUT.PUT_LINE(L_STR);
                      ELSE
                        FOR I IN 1..L_NUM+1
                            LOOP
                            SELECT SUBSTR(L_STR,N,DECODE(I-L_NUM,1,LENGTH(L_STR),
                                           DECODE(N,1,INSTR(L_STR,'^',1,I),INSTR(L_STR,'^',1,I) - INSTR(L_STR,'^',1,I-1))))
                            INTO L_EMP FROM DUAL;

                             DBMS_OUTPUT.PUT_LINE(L_EMP);
                            N := N+LENGTH(L_EMP);
                            END LOOP;
                       END IF;
                END;
            END LOOP;
END;