SQL> declare
  2    a number;
  3    myexcpt exception;
  4    pragma exception_init (myexcpt, -1476);
  5  begin
  6    a := 1/0;
  7  exception
  8    when myexcpt then dbms_output.put_line('exception division by 0');
  9  end;
 10  /
exception division by 0

PL/SQL procedure successfully completed.

begin  
    
  sql_stmt := 'SELECT 
*
FROM TAB';  
exception ORA-08103
WHen excpetion
then dbms_lock(sleep10)
  execute immediate sql_stmt; 
end; 

begin  
    
  sql_stmt := 'SELECT 
*
FROM TAB';  



  execute immediate sql_stmt; 
end; 

SQL> begin
  2
  3    sql_stmt := 'SELECT
  4  *
  5  FROM TAB';
  6
  7
  8
  9    execute immediate sql_stmt;
 10  end;
 11  /
  sql_stmt := 'SELECT
  *
ERROR at line 3:
ORA-06550: line 3, column 3:
PLS-00201: identifier 'SQL_STMT' must be declared
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored
ORA-06550: line 9, column 21:
PLS-00201: identifier 'SQL_STMT' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

DECLARE
    Cnt         NUMBER;
    No_object   EXCEPTION;
    PRAGMA EXCEPTION_INIT (No_object, -8103);
    My_col      VARCHAR2 (100);
BEGIN
    Cnt := 1;

    WHILE Cnt > 0
    LOOP
        BEGIN
            SELECT Col1 INTO My_col FROM My_table;

            Cnt := 0;
        EXCEPTION
            WHEN No_object
            THEN
                IF Cnt = 10
                THEN
                    RAISE No_object;
                END IF;

                -- sleep for 5 seconds. The user running the dbms_lock 
                -- procedure must be given permission by the dba to
                -- access the package.
                DBMS_LOCK.Sleep (5);
                Cnt := Cnt + 1;
        END;
    END LOOP;
END;
/

SELECT Col1 INTO My_col FROM My_table;