multiple IF inside cursor loop

Question

after this question, I'm unable to evaluate with different IFs statements inside one single loop, my execution ends with ORA-01403: no data found

What I have:

Declaration:

CREATE OR REPLACE PACKAGE MYSCHEMA.MYPKG AS

FUNCTION MYFUNCTION ( description OUT VARCHAR2 ) RETURN INTEGER;

END MYPKG;

Body:

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS

    FUNCTION MYFUNCTION ( description OUT VARCHAR2 ) RETURN INTEGER AS

CURSOR CUR_MYDATA IS
        SELECT
            o.name,
            o.last_name,
            o.id,
            o.socnum
        FROM
            origin o
        WHERE
            1=1
            AND o.name like upper ('a%');

        TYPE t_name IS TABLE OF origin.name%TYPE;
        TYPE t_lastname IS TABLE OF origin.last_name%TYPE;
        TYPE t_id IS TABLE OF origin.id%TYPE;
        TYPE t_socnum IS TABLE OF origin.socnum%TYPE;

        l_name t_name;
        l_lastname t_lastname;
        l_id t_id;
        l_socnum t_socnum;

    retcode INTEGER := 0;

    BEGIN
        description := 'OK';

        OPEN CUR_MYDATA;
        LOOP
        FETCH CUR_MYDATA BULK COLLECT INTO l_name,l_lastname,l_id,l_socnum;
        EXIT WHEN l_name.count = 0;
            for name in l_name.first .. l_name.last loop
                IF l_socnum(name) IS NULL THEN 
                    (select oo.socnum from other_origin where oo.id=l_id(name)) 
                END IF;

                IF length(l_lastname(name)) < 2 THEN
                    l_lastname(name) := 'default lastname';
                END IF;
            end loop;

            forall i IN l_name.first .. l_name.last    
                INSERT INTO destiny (
                    d_name,
                    d_lastname,
                    d_id,
                    d_socnum) 
                VALUES (
                    l_name(i),
                    l_lastname(i),
                    l_id(i),
                    l_socnum(i));

            END LOOP;
            COMMIT;
            RETURN retcode;
     EXCEPTION
     WHEN OTHERS THEN
        description := SQLERRM;
        retcode := SQLCODE;
        CLOSE CUR_MYDATA;
        RETURN retcode;
    END MYFUNCTION;

END MYPKG;

But I got ORA-01403: no data found

I also tried:

for name in l_name.first .. l_name.last loop
    IF l_socnum(name) IS NULL THEN 
        (select oo.socnum 
        into l_socnum(name)
        from other_origin where oo.id=l_id(name))

        for ln in l_name.first .. l_name.last loop
            IF length(l_lastname(ln)) < 2 THEN
                l_lastname(ln) := 'default lastname';
            END IF;
        end loop;
    END IF;

end loop;

But error is the same.

So, what do you think that I'm missing when I loop more than once through my cursor evaluating differents columns?

Try replacing " EXIT WHEN l_name.count = 0; with "exit when v_cursor%notfound;" — OldProgrammer, May 28 '19 at 22:58
@OldProgrammer somewhere I did read that `EXIT WHEN l_name.count = 0` is better than `exit when v_cursor%notfound` because second one might miss some records. — downtheroad, May 28 '19 at 23:35
Avoid all loops and cursors. Simply use an `insert into select` as in the end of [APC's answer](https://stackoverflow.com/a/56336935/7998591) to your earlier question — Kaushik Nayak, May 29 '19 at 03:19
Add DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to your exception handling. This would help you in identifying the source of failure. It looks like you have an issue with the data selected by one of the Select statements. — Krishna, May 29 '19 at 06:21

multiple IF inside cursor loop

0 Answers0