title: Create Stored Procedure in Oracle 11g pl/sql using IF and THEN
body: <p>I was having an issue creating this stored procedure and had some errors when executing. Errors consisted of SQL statement ignored and SQL command not properly ended. I thought all the code was pretty clean. </p>
  
  <ul>
  <li><p>Returns the total records and inserts it into the RECORD_COUNT variable from TABLE1 table.</p></li>
  <li><p>Condition to see if RECORD_COUNT is greater than zero and dumps data to clear out TABLE1 table.</p></li>
  <li><p>Condition to see if RECORD_COUNT equals zero in order to insert into TABLE1 table from the EXTERNAL_TABLE table.</p></li>
  </ul>
  
  <p>Please assist.</p>
  
  <pre><code>    CREATE OR REPLACE PROCEDURE sp_INSERT
      (RECORD_COUNT OUT NUMBER)
      IS
      BEGIN   
  
           SELECT COUNT(*) 
               INTO RECORD_COUNT
               FROM TABLE1;   
  
           IF RECORD_COUNT &gt; 0 THEN 
               EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE1'
           END IF;     
  
          IF RECORD_COUNT = 0 THEN 
          INSERT INTO TABLE1
              (
                  JOB_ID,                         
                  NUM_SP1,              
                  NUM_SP2,              
                  NUM_SP3,              
                  NUM_SP4,                         
              )
              (SELECT JOB_ID,               
                  NUM_SP1,              
                  NUM_SP2,              
                  NUM_SP3,              
                  NUM_SP4,              
              FROM EXTERNAL_TABLE)
          COMMIT;
          END IF;
      END;
  </code></pre>