Why does exception through me out of cursor loop?

Question

I have a PL/SQL oracle procedure, I run over list of sql_id to tune them. I have an exception , but it through me out and finish. Tried it with or without continue, but it's the same.. ANY IDEAS?

CREATE OR REPLACE Procedure sql_perf_for_sql_id
IS
   v_sql_id VARCHAR2(13);
    stmt_task VARCHAR2(64 CHAR);
    retcode VARCHAR2(64 CHAR);
    
   cursor c1 is
           SELECT SQL_ID 
            FROM sql_for_tune
            WHERE tuned='NO';
           
    

BEGIN
    IF c1%ISOPEN THEN
     CLOSE c1 ;
    END IF;
   
   open c1;
   LOOP
   --for  r_c1 in c1 loop  
   BEGIN
       fetch c1 into v_sql_id;
       --v_sql_id := r_c1.sql_id;
       exit when c1%notfound;
        
        update sql_for_tune set remarks='RUNNING' where sql_id=v_sql_id;
        commit;
        stmt_task := NULL;
        DBMS_OUTPUT.PUT_LINE (v_sql_id);
    
        -- create a tuning task tune the statement
        stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => v_sql_id);
        DBMS_OUTPUT.PUT_LINE( NVL(stmt_task,'Error CREATE_TUNING_TASK() Return Value'));

        -- execute the resulting task
        retcode := DBMS_SQLTUNE.EXECUTE_TUNING_TASK(stmt_task);
        DBMS_OUTPUT.PUT_LINE( NVL(retcode,'Error EXECUTE_TUNING_TASK() Return Value'));

        -- accept the resulting task
        retcode := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(stmt_task);
        DBMS_OUTPUT.PUT_LINE( NVL(retcode,'Error ACCEPT_SQL_PROFILE() Return Value'));

        -- Optional SELECT DBMS_SQLTUNE.report_tuning_task( stmt_task ) AS recommendations FROM dual;

               update sql_for_tune set tuned='YES', 
                task_name = stmt_task ,
                index_benefit=(SELECT max( r.benefit/100)
                                        FROM dba_advisor_actions a,
                                         dba_advisor_recommendations r
                                          WHERE a.task_name=stmt_task
                                         AND a.task_id = r.task_id
                                         AND a.rec_id = r.rec_id
                                         and a.command='CREATE INDEX'),
             profile_benefit=(SELECT max( r.benefit/100)
                                      FROM dba_advisor_actions a,
                                     dba_advisor_recommendations r
                                      WHERE a.task_name=stmt_task
                                     AND a.task_id = r.task_id
                                     AND a.rec_id = r.rec_id
                                     and a.command='ACCEPT SQL PROFILE')
               where sql_id=v_sql_id;
               
               commit;
               update sql_for_tune set remarks='END' where sql_id=v_sql_id;
        commit;
 
     EXCEPTION                
            WHEN OTHERS THEN
                update sql_for_tune set tuned='CHECK',      task_name = stmt_task     where sql_id=v_sql_id;
                commit;
                raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
                
                --continue;
        END;
    END LOOP;
    close c1;
END;

---

SQL> execute sql_perf_for_sql_id;
BEGIN sql_perf_for_sql_id; END;

*
ERROR at line 1:
ORA-20001: An error was encountered - -13786 -ERROR- ORA-13786: missing SQL
text of statement object "1" for tuning task "TASK_464272"
ORA-06512: at "SQL_PERF.SQL_PERF_FOR_SQL_ID", line 73
ORA-06512: at line 1


SQL>

Thank you

Answer 1

It exits because of

raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

If you want the loop to continue, don't raise the error - log it using some other way (for example, dbms_output.put_line being the simplest; store it into a table by using a call of the autonomous transaction procedure).

---

Also, remove all those COMMITs out of the loop. Commit once, at the end of the procedure.