title: Looping Procedure to Truncate a table SQL Developer
body: <p>I am trying to create a database procedure to truncate a table, but if that truncate fails wait momentarily and re-run the truncate. I cannot lock the table or duplicate the table then drop the older table, so this more difficult method is my only option.</p>
  
  <p>I am getting a PLS-00382 ERROR for my following code and stumped on the issue:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE TRUNCATE_ROUTING_CACHE AS
  runcount   NUMBER := 0;
  status      varchar2(10);
  
  
  BEGIN
  
  EXECUTE IMMEDIATE 'truncate table routing_cache';
  
  
  IF VALUE_ERROR THEN
  status := 'FAIL';
  
      WHILE status = 'FAIL' AND runcount &lt; 3
          LOOP
          sys.dbms_lock.sleep(120);
          EXECUTE IMMEDIATE 'truncate table routing_cache';
            IF VALUE_ERROR THEN
               runcount := runcount + 1;
  
            ELSIF status = 'FAIL' THEN
               status := 'SUCCESS';
  
            END IF;
  
          END LOOP;
  
  ELSIF runcount = 0 THEN
      status := 'SUCCESS';
      runcount := runcount + 1;
  END IF;
  
  END;
  </code></pre>